Press "Enter" to skip to content

Category: Wait Stats

A Primer on Wait Stats in SQL Server

Published 2025-04-18 by Kevin Feasel

Kevin Hill wants to know where your server hurts:

Everything in SQL Server is waiting for something…this is by design. And most people never think about it…

Until your application is waiting to complete its request, sell the product or provide “the report” to the CEO!

Most SQL Server performance issues come down to one thing: waiting. Every time a query slows down, SQL Server records what it was waiting on. These wait stats are one of the best diagnostic tools for DBAs, sysadmins, and developers – but only if you know how to use them.

Click through for the explanation. Kevin does a great job of proving the 100-level explanation of wait stats to new DBAs, giving them an understanding of what wait stats are, why they are important, and how we can use them to troubleshoot a performance issue. After really locking in the 100-level explanation, it’s a good idea to come back later and understand the difference between signal and non-signal waits (and when that’s relevant), how to track wait stats as part of a query execution plan, and how to correlate wait stats with other measures to find the root cause sooner.

Leave a Comment

Dealing with AG Secondaries Falling Behind

Published 2024-07-18 by Kevin Feasel

David Fowler troubleshoots a wait type:

Are you struggling with a laggy redo and a build up in the redo queue on your readonly secondaries? Are you suffering with high PARALLEL_REDO_TRAN_TURN waits? Then this magic remedy could cure your ailments.

Read on to learn more about the parallel redo process and one thing you can do if it’s falling too far behind. I wouldn’t permanently switch back to serial redo, but maybe make that switch if there’s some consistent pattern to when the process falls behind.

Comments closed

Actual Execution Plans and Lock Waits

Published 2024-05-16 by Kevin Feasel

Erik Darling notices me in a leg cast staring through his window with my telescope:

A long time ago, I complained that wait stats logged by actual execution plans don’t show lock waits. That seemed like a pretty big deal, because if you’re running a query and wondering why sometimes it’s fast and sometimes it’s slow, that could be a pretty huge hint.

Click through for the full story. Getting actual waits is indeed a big deal, and way easier than any of the alternatives like spinning up a special extended events session or yelling at everyone not to use the server for a few minutes while you ran your query.

Comments closed

Preventing ASYNC_NETWORK_IO Waits in SQL Server

Published 2024-01-23 by Kevin Feasel

Vlad Drumea troubleshoots a pernicious wait type:

In this post I’ll go over what the ASYNC_NETWORK_IO wait type is, when it occurs, and how you can tell if the application is causing it.

Lately I’ve had to troubleshoot a few situations where the issue seemed like a poor performing query, but the apparent slowdown was due to applications causing excessive ASYNC_NETWORK_IO waits in SQL Server.

This particular wait doesn’t always happen because of slow apps, though that’s often the case. Vlad breaks out several reasons why you might see the wait and explains what you can do to fix the problem.

Comments closed

Preemptive vs Non-Preemptive Waits in SQL Server

Published 2023-12-01 by Kevin Feasel

Chad Callihan interrupts this broadcast for an important message:

Have you ever been reviewing SQL Server waits and spotted any that had PREEMPTIVE in the name? There about 200 variations of PREEMPTIVE wait types out there, so I’m guessing you’ve seen at least one at some point in your career. They don’t always get the same type of attention as a CXPACKET wait or a THREADPOOL wait, so perhaps you never took the time to find out what they mean. Let’s discuss PREEMPTIVE waits as well as what NON-PREEMPTIVE means.

Read on for the answer.

Comments closed

A Primer on Latch Waits

Published 2023-09-13 by Kevin Feasel

Kendra LIttle gives us a sneak peek:

I’ve long found it tricky to remember and explain the differences between three similar-sounding waits in SQL Server that all have “LATCH” in the name: PAGELATCH, LATCH, and PAGEIOLATCH waits.

Here’s an illustration that explains these waits, along with wait subtypes.

This is an excerpt from my new comic, “Wait Stats in SQL Server.”

Click through for the excerpt, as well as some more detail on these latch types.

Comments closed

Troubleshooting HADR_SYNC_COMMIT Waits

Published 2023-03-27 by Kevin Feasel

Jose Manuel Jurado Diaz reviews a wait stat:

Today, I worked on a service request that our customer faced a performance issue in their business critical service tier database. Their main suspect was that the syncronization with the secondary replica has a delay. 

During their troubleshooting process they found that they got many request with the wait type is HADR_SYNC_COMMIT.

Read on to learn more about this wait, including what can cause it. There’s also a bit of information about it in the SQLskills wait state compendium.

Comments closed

Diagnosing a Resource Semaphore Wait Issue

Published 2023-02-27 by Kevin Feasel

Jose Manuel Jurado Diaz finds excessive resource semaphore waits:

Today, we got a service request that our customer reported that they query are taking too much for their execution. The main wait stats found was RESOURCE_SEMAPHORE and I would like to share with you my lessons learned here. 

We executed this query to find out the queries and check the resource semaphore wait type. 

Click through for the queries and diagnosis.

Comments closed

Troubleshooting High CPU via PAGELATCH Waits

Published 2023-01-02 by Kevin Feasel

Ajay Dwiveldi does some digging:

In the above dashboards, I could clearly notice PAGELATCH_** wait at the top along with SOS_SCHEDULER_YIELD. The presence of the above 2 waits is indicative of high CPU issues due to contention on the access of data file pages. I validated and found that this PAGELATCH_** wait is present almost all the time on the server. So decided to check the data of dbo.WhoIsActive that stores captured data of sp_WhoIsActive in SQLMonitor tool.

Read on for the outcome.

Comments closed