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.

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.