What Are Poison Waits?

Most of the time, SQL Server performance tuning starts with your top wait stats. Run sp_BlitzFirst @SinceStartup = 1, look at your top couple of wait types, and that’s where to focus your efforts.

However, even small amounts of certain wait types can indicate big problems. When these waits strike, it can feel like the server is frozen or unresponsive.

Pechuga style Mezcal = made with raw chicken. Surprisingly, not poisonous.

It’s kinda like having cocaine in your employee blood test results – if the company sees any of it, they’re going to freak out. (Not us, obviously. We don’t drug test our employees. We understand that DBAs need some pharmaceutical help to survive the database tragedies we see on a regular basis.)

That’s why sp_Blitz, our free health check stored proc, flags these wait types (and a few others) as poison waits:

RESOURCE_SEMAPHORE_QUERY_COMPILE – this means a query came in, and SQL Server didn’t have an execution plan cached for it. In order to build an execution plan, SQL Server needs a little memory – not a lot, just a little – but that memory wasn’t available. SQL Server had to wait for memory to become available before it could even build an execution plan. For more details and a reproduction script, check out my Bad Idea Jeans: Dynamically Generating Ugly Queries post. In this scenario, cached query plans (and small ones) may be able to proceed just fine (depending on how much pressure the server is under), but the ugly ones will feel frozen.

RESOURCE_SEMAPHORE – this means we got past the compilation stage (or the query was cached), but now we need memory in order to run the query. Other queries are using a lot of memory, though, and our query can’t even get started executing because there’s not enough memory available for our query. In this case, like with the prior poison, small queries may be able to get through just fine, but large ones will just sit around waiting. For more details and a repro, performance subscribers can watch my training video on RESOURCE_SEMAPHORE waits.

THREADPOOL – while the first two poisons involved memory issues, this one is about CPU availability. At startup, SQL Server allocates a certain number of worker threads based on the number of logical processors in your server. As queries come in, they get assigned to worker threads – but there’s only a finite number available. If enough queries pile up – especially when queries get blocked and can’t make progress – you can run out of available worker threads. The first temptation might be to increase max worker threads, but then you might simply escalate the problem to a RESOURCE_SEMAPHORE issue.

When sp_Blitz sees a history of any of these poison waits, it includes an urgent alert because this is about to become a big deal. If you don’t get on top of it, things will only get worse.

So go download & run sp_Blitz today, and if you get that warning, sing along with me: that wait is poison.

Previous Post
Should You Use Always Encrypted?
Next Post
[Video] Office Hours 2017/08/23 (With Transcriptions)

5 Comments. Leave new

  • Great Post, Brent®

    I love stuff on wait stats, as they were one of the first things I started learning about when I wanted to stop writing rubbish SQL. I knew about the two semaphore waits, but THREADPOOL is new to me so now I have some reading to do over the weekend.

    One thing of note; as a (fellow) Chicagoan, you should be ashamed for posting a picture of poison alcohol and not using Malört.

    Reply
  • Dear Brent,
    the part: “We understand that DBAs need some pharmaceutical help to survive the database tragedies we see on a regular basis.)”
    is great!
    In the last weeks I have a lot of those tragedies and ask myselg how to stand it without getting mental ill or becoming an addict or both *g*

    All the best and kind regards
    Gerald

    Reply
  • Victor Hugo Barajas Sosa
    September 21, 2017 1:09 pm

    Great post! Wait stats is the best to start troubleshooting!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}