Press "Enter" to skip to content

Category: Wait Stats

Hash Spill Wait Stats

Published 2021-09-10 by Kevin Feasel

Erik Darling continues a series on interesting wait stats:

Hash spills are, as we’ll see, sometimes identified by a different wait than sort spills. In small quantities, spills are often not worth bothering with. But when they pile up, they can really cause some severe performance issues.

In this post, I want to show that both Hash Aggregate and Joins can cause the same wait type to show, along with some evidence that strings make things worse.

Click through for a good explanation of the wait stat you’re liable to see the most.

Comments closed

Wait Stats on Sort Spills

Published 2021-09-09 by Kevin Feasel

Erik Darling starts a new series on wait stats, starting with one particular topic:

The point is not that spills are the sole things that cause these waits, it’s just to give you some things to potentially watch out for if you see these waits piling up and can’t pin down where they’re coming from.

In all the queries, I’m going to be using the MAX_GRANT_PERCENT hint to set the memory grant ridiculously low to make the waits I care about stick out.

Click through for the first of several demonstrations.

Comments closed

Wait Stats Not in Query Store

Published 2021-06-09 by Kevin Feasel

Erik Darling says wait, wait, don’t tell me:

There are some oddities in the documentation for query store wait stats.

One is that RESOURCE_SEMAPHORE_QUERY_COMPILE is listed as a collected wait, but with an asterisk that says it’s not actually collected. I’ve tested workloads that generate lots of that wait, and just like the docs say, it doesn’t end up there.

Of course, since I added wait stats recently to sp_QuickieStore, I wanted to make sure other waits that I care about actually show up in there.

Read on to see which wait stats you can find in Query Store and which you’ll have to get from someplace else.

Comments closed

Fixing Those Pesky Wait Stats

Published 2021-02-22 by Kevin Feasel

David Alcock keeps us from having to think:

CXPACKET

Attempt to pronounce parallellellellellism correctly then set MAXDOP to 1.

PAGELATCH

Mention TempDB and contention in the same sentence. Delete TempDB

BACKUP_

Delete any long running backup jobs. If wait persists then delete all backup jobs.

Click through for plenty of excellent nuggets of advice which definitely won’t land you on the unemployment line.

Comments closed

Threadpool Waits

Published 2021-01-28 by Kevin Feasel

Chad Callihan recommends not messing with worker threads:

Our story begins on a test server hosting a couple hundred databases. Over time, the server kept getting slower and slower when trying to navigate in SSMS. Query windows would take too much time to load, logging in would lag a bit, etc. When investigating with sp_Blitz, I found that the server had been experiencing Threadpool waits.

There are some cases where increasing thread count is important, especially when you’re working with database mirroring or availability groups. I worked with a customer with thousands of mirrored databases per server. None of the databases were particularly large or heavily-used, so it was on properly-sized hardware. As a result, to prevent the server from falling over due to threadpool waits, we had to scale thread counts to scary-high levels.

Comments closed

Collecting Wait Stats Over Time

Published 2021-01-11 by Kevin Feasel

Michael J. Swart has a repo for us:

I find wait stats so useful that I’ve got Paul Randal’s SQL Server Wait Statistics (or please tell me where it hurts…) bookmarked and I visit it frequently.

But that gives the total amount of waits for each wait type accumulated since the server was started. And that isn’t ideal when I’m troubleshooting trouble that started recently. No worries, Paul also has another fantastic post Capturing wait statistics for a period of time.

You can also get this from various monitoring tools, as Michael mentions, but if you don’t have such a tool in place, here’s how you can roll your own.

Comments closed

SSMS and Ignoring Certain Waits

Published 2020-10-27 by Kevin Feasel

Erik Darling has a plea to the SQL Server Management Studio team:

Lock waits are particularly annoying. Imagine (I know, this might be difficult) that you have a friend who is puzzled by why a query is sometimes slow.

They send you an actual plan for when it’s fast, and an actual plan for when it’s slow. You compare them in every which way, and everything except duration is identical.

It’d be a whole lot easier to answer them if LCK waits were collected, but hey. Let’s just make them jump through another hoop to figure out what’s going on.

CXCONSUMER has a similar problem — and here’s the thing — if people are going through the trouble of collecting this information, give’em what they ask for. Don’t just give them what you think is a good idea.

Click through to see the issue and what you can do to work around this limitation.

Comments closed

Unusual Threadpool Waits

Published 2020-08-28 by Kevin Feasel

Josh Darnell explains why you might get threadpool waits even when you think you shouldn’t:

I occasionally see (usually brief) THREADPOOL waits on systems that are really not all that heavily loaded. This is my investigation into why. Some might say I have too much time on my hands.

Before getting into these unusual THREADPOOL cases, let’s cover the normal ones.

Read the whole thing. It’s example #9068 of how a particular wait is not always a bad thing.

Comments closed

Understanding the RESOURCE_GOVERNOR_IDLE Wait Type in Azure

Published 2020-06-16 by Kevin Feasel

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.

Comments closed

Per-Query Wait Stats with Extended Events

Published 2020-03-31 by Kevin Feasel

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