Press "Enter" to skip to content

Category: Wait Stats

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

Wait Stat Variance with QDS Toolbox

Published 2022-02-09 by Kevin Feasel

Jared Poche looks for differences:

In my most recent blog post, I looked at the Query Variation report, which compares the recent performance of queries versus their historical performance to either highlight improvements or regressions in performance. The Waits Variation component does the same, but comparing the recent waits for a query to its historical waits.

One thing to keep in mind, is that if a given query is changed in any way (to change the filter, return additional columns, or include a hint), the changed query will have a different query_id in Query Store. In which case, both the Waits Variation and Query Variation procedures will not compare the historical performance of the old query to the recent performance of the new one.

Read on to see how.

Comments closed