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 HADR_FILESTREAM_IOMGR_IOCOMPLETION is recorded by the FILESTREAM I/O manager for Availability Groups while it sleeps between checks for completed FILESTREAM sends to replicas. The detail that surprises people: it ticks every half second for half a…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability HADR_DATABASE_FLOW_CONTROL is an Availability Group wait recorded on the primary when log capture has outrun the network send. Each database in an AG has a quota of unacknowledged messages allowed in flight to a secondary;…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability HADR_CLUSAPI_CALL is recorded when a SQL Server thread switches to preemptive mode to invoke Windows Server Failover Clustering (WSFC) APIs. Availability Groups lean on the cluster constantly, health reporting, lease maintenance, metadata reads, failover coordination,…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: HADR_SYNC_COMMIT Wait TypeRelated pillar: High Availability HADR_AG_MUTEX is recorded when a thread waits for the critical section guarding an Availability Group’s configuration. AG DDL statements (ALTER AVAILABILITY GROUP, adding or removing databases and replicas) and Windows Server Failover Clustering commands need…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting Two internal full-text search waits share this page: FT_IFTSHC_MUTEX, a thread in a full-text operation synchronising with other full-text workers around fdhost control operations (fdhost.exe is the filter daemon host process that does the…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting FT_IFTS_SCHEDULER_IDLE_WAIT is recorded by the full-text search scheduler while it waits for work to appear on the full-text background task queue. If the Full-Text Engine is installed, this scheduler exists and mostly idles, whether…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting Two waits belong to the FileTable subsystem (the FFT prefix), and this page covers both: FFT_NSO_DB_LIST, a thread waiting for access to the list of databases the FileTable subsystem maintains, and FFT_RECOVERY, a thread…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: IO_COMPLETION Wait TypeRelated pillar: High Availability These two waits synchronise I/O against database snapshot sparse files, and this page covers both: FCB_REPLICA_READ for reads through a snapshot’s context (queries against the snapshot, or DBCC’s internal snapshot reading its own copied pages)…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting EXECSYNC is a parallelism wait recorded when the threads of a parallel plan wait for a shared construct to be built by a single thread. Unlike CXPACKET, which covers the exchange iterators…

⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: CXPACKET and CXCONSUMER Wait TypesRelated pillar: Performance & Troubleshooting EXCHANGE is a wait that occurs during synchronisation inside the query processor’s exchange iterator, the operator that moves rows between threads in a parallel plan. Exchange operators (you see them as Distribute…