Assigning too many Sockets and Cores to particular SQL Servers can be a waste of resources, and in tight VMWare Environments it can lead to unnecessary over subscription of your CPU’s causing issues on your host performance.
With Configuring the CPU’s on a SQL Server; Particularly around Express and Standard Editions; We are limited with the number of Sockets and Cores SQL can use.
What Does This Mean?
Express Edition Instance we are limited to 1 socket and up to 4 CPU’s on that socket.
Standard Edition Instance we are limited to 4 sockets and up to 24 CPU’s on those sockets.
You need to know a little around how this is calculated; if you have a VM with;
- 8 sockets and 8 Cores that’s 1 core per socket.
- 4 sockets and 8 Cores that’s 2 core per socket.
- 2 sockets and 8 cores, that’s 4 cores per socket.
- 1 socket and 8 cores; 8 cores per socket.
if we were on SQL Standard Edition and we configured to the VM with 8 sockets and 8 cores we would be limited to using just 4 sockets there for 4 of the sockets and the 1 core on each of these sockets would be unused by SQL server.
That’s wasted Assigned CPU’s, possible performance issues on SQL server, possibly wasted licenses on standard edition servers.
How is this displayed in SQL Server?
Simple! From SQL Server 2008 Microsoft has provided a DMV which can tell what our schedulers are doing!
select * from sys.dm_os_schedulers
Any CPU not being used by SQL Server will show as “VISIBLE OFFLINE”
Performance issues around CPU?
It’s possible that this limitation is causing you CPU Contention within SQL Server yet your OS level monitoring is only showing you 50% CPU Utilization. you guessed it; SQL Server is not using all your CPU Power, this is clearly Visible by looking at wait stats more specifically the SOS_SCHEDULER_YEILD wait type.
There is a pretty nice rule of thumb to follow with VM Configuration;
Luke Salter 