Category: Wait Types

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting PWAIT_EXTENSIBILITY_CLEANUP_TASK is recorded by a background task belonging to the extensibility framework behind SQL Server Machine Learning Services, the Launchpad-based machinery that runs external R and Python scripts. The task sleeps in 300-second stretches…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait TypesRelated pillar: Performance & Troubleshooting REQUEST_FOR_DEADLOCK_SEARCH is recorded by the background deadlock monitor while it waits between deadlock searches. The default interval is five seconds, and it adapts: when deadlocks are being found the monitor searches…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: RESOURCE_SEMAPHORERelated pillar: Performance & Troubleshooting RESOURCE_SEMAPHORE_QUERY_COMPILE means a query is waiting for memory needed at compile time. This is different from RESOURCE_SEMAPHORE, the runtime grant wait, where a plan is already compiled and queuing for execution memory. SQL Server throttles concurrent…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting WAIT_XTP_OFFLINE_CKPT_NEW_LOG is recorded by an In-Memory OLTP (XTP) offline checkpoint thread waiting for new log records to scan. In-Memory OLTP durability works by continuously scanning the transaction log and folding changes into checkpoint file…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting XE_TIMER_MUTEX is recorded when a thread waits for access to the data structure holding the Extended Events engine’s timers, such as those implementing each session’s MAX_DISPATCH_LATENCY. Timer registration and firing serialise briefly on this…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: WRITELOG Wait TypeRelated pillar: Performance & Troubleshooting XTP_PREEMPTIVE_TASK appears to be a generic wait for background worker threads involved in In-Memory OLTP processing, running preemptively, so the thread stays under Windows control (state RUNNING) until its work completes. Documentation labels it…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability PARALLEL_REDO_TRAN_TURN is recorded on an Availability Group readable secondary when a parallel redo thread must redo a log record, but a prerequisite log record has not yet been redone by another thread. Some operations demand…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability PARALLEL_REDO_TRAN_LIST is recorded when the main parallel redo thread on an Availability Group replica needs access to the shared list of transactions currently being redone. That list is mostly the working territory of the parallel…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability PARALLEL_REDO_LOG_CACHE is recorded infrequently on an Availability Group replica after the main redo thread has hit a bottleneck, that is, following episodes of PARALLEL_REDO_FLOW_CONTROL. It relates to the log cache the redo pipeline reads from,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability PARALLEL_REDO_FLOW_CONTROL is recorded on an Availability Group secondary when the main redo thread has dispatched as much log as it is allowed to the parallel redo worker threads and must wait for one of them…