Press "Enter" to skip to content

Category: Wait Stats

Actual Execution Plans and Lock Waits

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

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

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

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

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

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

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

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

Understanding the Oldest Page Wait

Tom Collins explains a database wait:

SQL Server Log truncation deletes inactive Virtual Log Files (VLF) from the SQL Server database transaction log . The Log truncation process frees  space in the logical log for reuse by the Physical transaction log. If no truncation occurs , eventually it will fill all the disk space allocated to physical log files.

SQL Server Log truncation can be delayed for a range of different reasons.A good starting point is to  query the sys.databases log_reuse_wait and log_reuse_wait_desc columns. This will supply different waits describing the reason for a delay 

Read on for more info about the OLDEST_PAGE wait.

Comments closed