RESOURCE_SEMAPHORE vs THREADPOOL tells you whether a task waits for memory or for a worker thread. This post reads both counters and checks for live waits. A demo shows how an index shrinks a memory grant from 38,216 KB to zero.
Finding the Indexes Behind Lock Waits With Operational Stats
Rank indexes behind lock waits with operational stats, separate latches from locks, and test targeted access or row versioning fixes.
SQL SERVER – PREEMPTIVE Waits and Scheduler Context
Preemptive waits can measure work outside SQL Server cooperative scheduling. A customer asked about their increase during a health check.
Spinlock in SQL Server: What It Is and How to Read One
A spinlock in SQL Server is a very short lock on an internal structure, and a thread that waits for it keeps spinning on the CPU instead of sleeping. This post explains how a spinlock differs from a latch and a lock. It reads sys.dm_os_spinlock_stats, measures a ten second window and lists the Extended Events that report backoffs.
SQL SERVER – What is Latch?
What is Latch protection in SQL Server was a reader question after my scheduler video. A latch provides short-lived internal synchronization.
SQLOS Scheduler and Process States: Running, Runnable and Suspended
SQLOS scheduler and process states, in simple words: the processor, the ready queue and the waiting queue. After my SQL in Sixty Seconds video, people asked how to map them, so here is the answer.
Execution Plan Wait Stats: Read Them in SSMS and XML
Execution plan wait stats list what one execution of a query waited for, so you don’t have to guess from server-wide totals. This post shows where to find them in SSMS and in the plan XML, reproduces a lock wait with two query windows, and explains the common wait types. It ends with Query Store, which keeps wait totals for many queries.