Your server is underpowered

That’s an understatement. Your server sucks.

It has four cores in a single socket, data outpaces RAM by a country mile, the disks have whiskers, and the network card still has a phone jack in it.

Alright, so maybe it’s not that bad, but it’s bad enough that you run into trouble.

Always Be Blitzing

You’re a smart goofball, though. You run sp_Blitz, and sometimes you even read the output. For servers in really sad shape, it’ll warn you about something we call Poison Waits. These are a group of wait types that can really ruin your day.

Side effects range from login and query timeouts, degraded performance, nausea, vomiting, and diarrhea. Actually, those last three are limited to you, when your phone and inbox start lighting up with frantic high priority emails from people with office doors.

Unfortunate

One thing I see pretty frequently on these 99 cent bin servers, is people will respond to THREADPOOL waits by upping Max Worker Threads to accommodate additional requests. This may seem fine at first, and when your server is under similar load performance degrades… well, less. But adding all those threads to your CPUs that came with a download code for Blood II only gets you so far.

The problem with all those new worker threads is that each one takes up memory. How much? 2048 KB, or 2 MB for the KB impaired.

All those new threads, taking all that same old memory up. Any guesses where that memory comes from?

Hint: Stuff your users need. And eventually, if you have enough going on, stuff your system needs. I’ve seen people push MWT up to 2000, which is 4 GB of memory if they all get used. Depending on how much is in your system, and what else is going on, this can contribute to other Poison Waits like RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE.

Better way?

The first thing to look at if you hit THREADPOOL waits are your parallelism settings. If you’re curious about those, checkout our Setup Guide. If you’ve already set those, you may need to cut MAXDOP in half, and/or increase Cost Threshold For Parallelism, so fewer queries go parallel, and fewer cores get used when they do.

After that, take a good hard look at your queries and indexes. You can use sp_BlitzCache and sp_BlitzIndex, which are also available at the First Responder Kit link above. Reducing query cost will further reduce CPU strain. This is a good thing.

Finally, tell your cheapskate boss to buy some decent hardware. If you need a starting point, this is what I have sitting under my desk. If that’s way more than what’s in your production server, you’ve got a problem.

Thanks for reading!

Brent says: when we see these symptoms, it’s almost always on VMs with 2-4 cores and 8-16GB RAM. Remember, folks, licensing is expensive. You wouldn’t bring home a present from Tiffany’s and wrap it in a Happy Meal box. Don’t run SQL Server on your grandpa’s laptop.