Press "Enter" to skip to content

Category: Wait Stats

Understanding the RESOURCE_GOVERNOR_IDLE Wait Type in Azure

Josh Darnell does some sleuthing:

With a big gap between CPU and elapsed time, it’s often worthwhile to check wait statistics. If the query was running, but not using CPU, it seems reasonable that it was waiting on something. Normally, with on-prem SQL Server, you’d have to check sys.dm_os_wait_stats, and take a diff of the cumulative values before and after.

However, thanks to (relatively) recent enhancements to execution plans (which keep getting better and better!), we can see a subset of what resources the query waited on right in the plan.

Looking at the plan from my Azure query, here’s what I see:

<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="5733" WaitCount="323" />
<Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="5545" WaitCount="430" />

Notice that there were 5.5 seconds of RESOURCE_GOVERNOR_IDLE waits during this query. That explains the 5 second gap in CPU and elapsed time. But what does it mean?

Click through to learn more about this in the context of Azure SQL Database.

Leave a Comment

Per-Query Wait Stats with Extended Events

Grant Fritchey shows us how to see the waits associated with a specific query:

And that my friends is only the waits associated with the one query. TA-DA indeed!

Now, we could get into filtering this stuff too. Toss the ones that have little to no duration, ensure that I only capture for a specific query or procedure, all would be helpful. However, this is how you can easily identify just the waits associated with a single query, and only that query.

Click through to see how. The one thing I’d caution here is that the query which received waits isn’t necessarily the query in the wrong—it might be the fourth or fifth session in a blocking chain. But this is a great technique for getting additional per-query info when you can control the experiment.

Comments closed

ASYNC_NETWORK_IO and Execution Plans

Jonathan Kehayias dives into an interesting problem:

A few weeks ago, an interesting question was asked on the #SQLHelp hash tag on Twitter about the impact of execution plans on the ASYNC_NETWORK_IO wait type, and it generated some differing opinions and a lot of good discussion.

My immediate answer to this would be that someone is misinterpreting the cause and effect of this, since the ASYNC_NETWORK_IO wait type is encountered when the Engine has results to send over TDS to the client but there are no available TDS buffers on the connection to send them on. Generally speaking, this means that the client side is not consuming the results efficiently, but based on the ensuing discussion I became intrigued enough to do some testing of whether or not an execution plan would actually impact the ASYNC_NETWORK_IO waits significantly.

To summarize: Focusing on ASYNC_NETWORK_IO waits alone as a tuning metric is a mistake. The faster a query executes, the higher this wait type will likely accumulate, even if the client is consuming results as fast as possible. (Also see Greg’s recent post about focusing on waits alone in general.)

Click through for the things Jonathan tested.

Comments closed

Understanding SOS_SCHEDULER_YIELD

David Fowler takes us through the SOS_SCHEDULER_YIELD wait type:

I decided to write this off the back of a conversation I was having the other day around the SOS_SCHEDULER_YIELD wait type.

The conversation went something along the lines of “but David, I’m seeing SOS_SCHEDULER_YIELD, we must have CPU issues”.

Yes this particular customer had been CPU bound recently but was that really their problem now, what is SOS_SCHEDULER_YIELD really mean?

It’s a good write-up of when it is and is not a problem.

Comments closed

Troubleshooting RESOURCE_SEMAPHORE Waits

David Fowler takes a look at the RESOURCE_SEMAPHORE wait type:

These aren’t something that I ever want to see and if I do I am straight away going to go looking for the cause. Basically what’s that’s telling you is that you’ve got processes waiting on a memory allocation. This is going to be because SQL Server hasn’t got enough memory to dish out to that particular process.

What’s going on here is that every time a query runs, it’ll ask for a certain amount of memory. A happy SQL Server will serve up a tasty slice of memory for that query to run in. The problem comes when the query is asking for a bigger slice of the pie than SQL has available. In that case the query will need to wait until SQL has enough free to give the query what it wants, that’s when the RESOURCE_SEMAPHORE wait starts ticking up.

Read on for a second look at this, as well as what you can do to help. Sometimes the answer is “add more memory” but this isn’t necessarily the case.

Comments closed

Waiting on Stats Refreshes

Erik Darling looks at a new wait type:

I mean ever really wondered just how long a query of yours waited on stats to automatically update before running?

Have you ever been absolutely, positively sure that those gosh dang darn stats updates were putting your query on the trolley to Slowsville?

Your wishes will be 100% granted in SQL Server 2019.

This is a wait type that I’d consider useful but hopefully uncommon.

Comments closed

The Folly of Aggregation, Wait Stats Edition

Erik Darling explains why simply looking at wait stats isn’t enough:

I’m going to be honest with you, dear readers. I’ve fallen out of like with wait stats.

Unless there’s a massive resource or blocking problem, they’re a bit bland.

Tuning a single query, I don’t look at them at all.

Sure, they can be a good detail when you’re painting an overall picture of a server, but they’re misleading a lot.

Erik makes a sound point. It’s usually a sound point when it reminds me of the Hayek quotation, “Mr. Keynes’s aggregates conceal the most fundamental mechanisms of change.”

Comments closed

Tracking xp_cmdshell Executions

Jason Brimhall shows how you can see when someone calls xp_cmdshell, including the call details:

What was the wait_type? Well, the obscure wait_type was called PREEMPTIVE_OS_PIPEOPS. What causes this wait? As it turns out, this is a generic wait that is caused by SQL pipe related activities such as xp_cmdshell.

Knowing this much information however does not get us to the root cause of this particular problem for this client. How do we get there? This is another case for Extended Events (XEvents).

Read on for two ways to approach this, both using Extended Events.

Comments closed

Dealing with HADR_SYNC_COMMIT Waits

Dmitri Korokevitch walks us through the HADR_SYNC_COMMIT wait type:

The secondary nodes may be configured using asynchronous or synchronous commit. With asynchronous commit, transaction considered to be committed and all locks were released when COMMIT log record is hardened on the primary node. SQL Server sends COMMIT record to secondary node; however, it does not wait for the confirmation that the record had been hardened in the log there.

This behavior changes when you use synchronous commit as shown in Figure 1. In this mode, SQL Server does not consider transaction to be committed until it receives the confirmation that COMMIT log record is hardened in the log on the secondary node. The transaction on primary will remain active with all locks held in place until this confirmation is received. The session on primary is suspended with HADR_SYNC_COMMIT wait type.

Click through for the full story.

Comments closed

When Faster Disk Increases WRITELOG Waits

Paul Randal explains that WRITELOG waits can potentially increase as you get faster disk:

I was contacted last week by someone who was confused about the WRITELOG wait type. They were seeing lots of these waits, with an average wait time of 18ms. The log was stored on a Raid-1 array, using locally-attached spinning disks in the server. They figured that by moving the log to Raid-1 array of SSDs, they’d reduce the WRITELOGwait time and get better workload throughput.

They did so and got better performance, but were very surprised to now see WRITELOG as the most frequent wait type on the server, even though the average wait time was less than 1ms, and asked me to explain.

Read on for Paul’s explanation of why this is not a scary situation, or is it particularly weird. SQL Server performance is a complicated thing and trying to limit it to one measure or one query can lead you down the wrong path.

Comments closed