AUTO_UPDATE_STATISTICS_ASYNC is the setting that cleared a new wait type, WAIT_ON_SYNC_STATISTICS_REFRESH, for my client. With auto update statistics on and stale statistics, the query halts while the statistics update. Switching the update to asynchronous removed the wait.
Session Wait Stats: Find the Query Behind a Wait Type
Session wait stats tell you which session produced a wait type, and the input buffer tells you which query that session ran. This post shows how to see who waits right now, how to read the wait history of each session, and how to join them to the query text. It also explains the THREADPOOL wait.
RSFXDEVICEOPS Wait: Unneeded WAITFOR DELAY Slowing Queries
The RSFXDEVICEOPS wait, listed as PREEMPTIVE_OS_RSFXDEVICEOPS, was high at a client that did not use FileStream. Their queries carried a WAITFOR DELAY nobody remembered adding. When the clause came out, the wait dropped and the queries got fast again.
RESOURCE_SEMAPHORE_QUERY_COMPILE Wait: Check Lock Pages in Memory
RESOURCE_SEMAPHORE_QUERY_COMPILE was the first wait type in a client’s wait statistics. Readers asked how I knew to look at Lock Pages in Memory. Here is the script I used, and the setting that resolved the problem.
Query Runs Slow Occasionally: Five Checks to Find Why
When a query runs slow occasionally, the cause sits around the query: a lock, a job, a file growth, tempdb or a shortage of worker threads. This post gives one tested T-SQL check for each and a small demo for blocking and file growth. It also says what to do when all five checks find nothing.
Signal Waits: Detect CPU Pressure With Wait Statistics
Signal waits are the part of every wait that a task spends in the queue for a free CPU. This post ranks the wait types by signal time, shows why timer waits ruin the total share, and samples the signal share into a small table so a whole working day can be read interval by interval.
THREADPOOL Waits: When Raising Max Worker Count Helps
THREADPOOL waits mean tasks waited for a worker thread. Five read-only queries separate real thread starvation from blocking and busy CPUs. The post lists five signs and shows how to raise the cap in small steps.