Author: Peter Whyte

⏳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_WORKER_SYNC is recorded when the main parallel redo thread on an Availability Group replica waits for its redo worker threads to complete their assigned work. It is the general completion-synchronisation wait of the redo pipeline,…

⏳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_WORKER_WAIT_WORK is recorded on an Availability Group secondary when one of the parallel redo worker threads is waiting to be given log records to replay. Most of the time that is because the primary simply…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting PERFORMANCE_COUNTERS_RWLOCK is recorded when a thread waits on the reader-writer lock protecting the performance counter structures while a counter instance is added or removed. Many SQL Server counters are per-instance-of-something, per database, per Availability…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting The PREEMPTIVE_COM_* waits track threads calling methods on COM objects, in DBA practice almost always the OLE DB providers behind linked servers and OPENROWSET/OPENQUERY. This page covers the family: All are preemptive: the thread…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting Distributed transactions route through the Microsoft Distributed Transaction Coordinator (MSDTC), and SQL Server’s calls into it are preemptive, the thread leaves cooperative scheduling until MSDTC answers. This page covers the family: Together they meter…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability PREEMPTIVE_HADR_LEASE_MECHANISM is recorded when a thread switches to preemptive mode to renew the lease between the failover cluster and the SQL Server resource DLL, following a lease timeout. The lease is the AG’s dead-man switch:…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: ASYNC_NETWORK_IO Wait TypeRelated pillar: Performance & Troubleshooting PREEMPTIVE_OLEDBOPS is recorded when a thread switches to preemptive mode (outside SQL Server’s cooperative scheduler) to communicate with an OLE DB provider. It is the third member of the linked server wait family: OLEDB…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting A cluster of preemptive waits track Windows group membership and directory queries, and this page covers them together: PREEMPTIVE_OS_NETGROUPGETUSERS, NETLOCALGROUPGETMEMBERS, NETUSERGETGROUPS, NETUSERGETLOCALGROUPS, NETUSERMODALSGET, DOMAINSERVICESOPS, and DIRSVC_NETWORKOPS. They fire when SQL Server asks Windows who…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting PREEMPTIVE_OS_AUTHENTICATIONOPS is recorded when a thread calls Windows security functions for authentication and authorisation work: validating logins, checking group memberships, building security tokens. The thread runs preemptively (outside SQL Server’s scheduler, state showing RUNNING)…