Hold on hold on hold on
While the performance and licensing aspects are interesting, they're not the only aspect of a workload to consider.
One thing that can have an impact on processor choice is worker threads.
Yeah buddy! They're the things that your SQL Server will use to run your queries and do all the background stuff that it needs to do to keep things in shape.
When you run out of worker threads, you hit THREADPOOL waits
THREADPOOL. This is one of the nastiest waits you can have on your server, along with RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE. But those are memory waits, and this is a CPU question.
So back to why this is wiggity wack.
This is how SQL Server calculates worker threads:
Notice how doubling core counts doesn't double Max Worker Threads, and you get the same number with 1 core as you do with 4 cores? The equation is:
512 + ((logical CPUs - 4) * 16)
That's a shame, because when core counts go up, clock speed usually nosedives to a generation or two back.
Taking a look at any recent line of Intel chips will show a similar trend.
How do I know how many threads I need?
This will depend a lot on:
- Number of Users
- Number of parallel queries
- Number of serial queries
- Number of databases and data synchronization (Mirroring, AGs, backups for Log Shipping)
- If you leave MAXDOP and CTFP at the defaults
If you're not running out of them today, you're probably okay.
But how do you know if you are?
There are good questions, and there are great questions, and lemme tell you something, that is a GREAT QUESTION.
THREADPOOL can manifest as connection issues, and you may see messages in the error log about not being able to spawn a thread.
You can also look at your server's wait stats using a free tool like sp_Blitz or sp_BlitzFirst (full disclosure, I contribute to this project).
EXEC sp_BlitzFirst @SinceStartup = 1
Can't I just increase Max Worker Threads?
Increasing MWT can lead to increased
That's not the end of the world, but think of it like adding a buncha screaming kids to a teacher's class.
All of a sudden, it's gonna be harder for each kid to get attention.
When a process exhausts its 4ms quantum, there will potentially be more threads ahead of it waiting to get on the CPU.
Performance might feel about the same.
How can I use fewer Worker Threads?
You cruel [noun] of a [noun], those are workers with families to support! Mortgages! Dreams!
But alright, gotta respect the bottom line. You're the boss.
The easiest place to start is changing settings like MAXDOP and Cost Threshold For Parallelism from the defaults.
If you have questions about how to set those, head over here:
After that, your job gets a lot tougher. You've gotta figure out what's using all those threads. You can sometimes do that by looking at your wait stats.
More specifically, if you've got high waits on parallelism (
CXPACKET) AND high waits on locks (
LCK_), then you may be running into long blocking chains involving parallel queries.
You know what stinks? While all those parallel queries are waiting to get their locks, they don't give their allocated threads back.
You can almost hear that four core VM that your admin assured you was more than enough for any workload gasping for air, huh?
Unfortunately, the type of query and index tuning you have to do to resolve that stuff is beyond the scope of the question.
Hope this helps!