We are beginning to provision a set of physical servers for a virtual cluster of SQL Server 2016 nodes within VMware. We will be utilizing Enterprise Edition licenses.
We plan on setting up 6 nodes, but there is a bit of a debate on what the ideal way to provision the physical servers with regards to CPU clock speed versus CPU core count.
I know this is largely dependent on transaction volume and number of databases stored among other software-specific factors, but is there a general rule of thumb that is advised?
For instance, is a dual 8-core, 3.2 GHz physical server (16 cores) more preferential to a dual 16-core, 2.6 GHz server (32 cores)?
Has anyone come across a white paper that further delves into this type of topic?
**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.
**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][1] waits
**THREADPOOL?**
THREADPOOL. This is one of the nastiest waits you can have on your server, along with [RESOURCE_SEMAPHORE and RESOURCE_SEMAPHORE_QUERY_COMPILE][2]. 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][3]:
[![NUTS][4]][4]
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.
[![NUTS][5]][5]
Taking a look at any [recent line of Intel chips][6] 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][7], and you may see messages in the error log about not being able to [spawn a thread][8].
You can also look at your server's wait stats using a free tool like [sp_Blitz or sp_BlitzFirst][9] (full disclosure, I contribute to this project).
`EXEC sp_Blitz`
[![NUTS][10]][10]
`EXEC sp_BlitzFirst @SinceStartup = 1`
[![NUTS][11]][11]
**Can't I just increase Max Worker Threads?**
Increasing MWT can lead to increased `SOS_SCHEDULER_YIELD` waits.
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][12], 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:
- [MAXDOP setting algorithm for SQL Server][14]
- [Why Cost Threshold For Parallelism Shouldn’t Be Set To 5][13]
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!
[1]: https://www.sqlskills.com/help/waits/threadpool/
[2]: https://www.brentozar.com/archive/2018/01/pass-summit-2017-video-worked-development/
[3]: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-2017
[4]: https://i.stack.imgur.com/hxsuq.png
[5]: https://i.stack.imgur.com/eGYRZ.png
[6]: https://ark.intel.com/products/series/93797/Intel-Xeon-Processor-E7-v4-Family
[7]: https://www.brentozar.com/archive/2018/01/network-issue-threadpool-waits/
[8]: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d511a15e-8f35-400c-a02e-4aa63dbc0ba9/sql-server-failed-with-error-code-0xc0000000-to-spawn-a-threaderror?forum=sqldatabaseengine
[9]: http://firstresponderkit.org
[10]: https://i.stack.imgur.com/UFmhq.jpg
[11]: https://i.stack.imgur.com/NZJWx.png
[12]: https://www.sqlskills.com/help/waits/sos_scheduler_yield/
[13]: https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-parallelism-shouldnt-be-set-to-5/
[14]: https://dba.stackexchange.com/questions/36522/maxdop-setting-algorithm-for-sql-server
The general rule of thumb is keep the core count as low as possible, and the processor speed as high as possible. The licensing math on that proves the point at ~$7,500 USD per core for Expensive Edition.
Buying the correct hardware can pay for itself in reduced licensing costs. See [Processor Selection for SQL Server][1] by Glenn Berry. It's a great resource on how to choose a processor for SQL Server.
[1]: https://www.sqlskills.com/blogs/glenn/sqlskills-sql101-processor-selection-for-sql-server/
Once you take into consideration SQL Server's per-core licensing structure, it makes sense to always go with the fastest processor speed available, regardless of workload type, whether OLTP or analytics. Having the fastest possible core speed is never going to be a problem. Increase the core count as required, but never do that by reducing core speed.
In other words, don't think of 16 x 2.2Ghz processors being the same as 8 x 4.5Ghz processors. The cost savings of using the 2.2Ghz processors over the 4.5Ghz processors is likely to be a maximum of about $10,000 USD (for a typical Xeon-based two processor machine). Jumping from 8 cores to 16 cores with SQL Server Enterprise Edition is likely to cost over $60,000 USD in licensing fees. In other words, you might save $10,000 in hardware costs, but you'll lose an extra $50,000 in licensing.
If you decide you need a lot of parallel processing muscle, and decide you need 32 cores for the task at hand, going with the fastest cores will pay dividends in reduced processing time. Nobody will fault you for that.
Having said all that, if the choice is one CPU or more than one CPU, always go with *more than one*. Running SQL Server (or any DBMS) on a single CPU can cause all kinds of problems since the capability for concurrent operations is vastly limited.
