Author: Peter Whyte

⏳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 DEADLOCK_ENUM_MUTEX is recorded when the background deadlock monitor and the sys.dm_os_waiting_tasks DMV synchronise so that SQL Server never runs multiple deadlock searches at once. Computing blocking_session_id for the DMV’s output walks…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting FT_MASTER_MERGE is recorded when a thread participating in a full-text master merge waits for the other participating threads to finish. A master merge happens when ALTER FULLTEXT CATALOG … REORGANIZE consolidates the many small…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting The cryptography-related preemptive waits track Windows crypto API calls, and this page covers the family: PREEMPTIVE_OS_BCRYPTIMPORTKEY, CRYPTACQUIRECONTEXT, CRYPTIMPORTKEY, CRYPTOPS, NCRYPTIMPORTKEY, VERIFYSIGNATURE, and VERIFYTRUST. Their customers are the engine’s encryption features: TDE key operations, backup…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting PWAIT_ALL_COMPONENTS_INITIALIZED appears to be a startup gate: background tasks (the SQLOS resource monitor among them, judging from its call stacks) waiting during SQL Server startup for the engine components they depend on to finish…

⏳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…