Recently, Mark Varnas posted a client's experience in his LinkedIn profile. The client has 16 core virtual processors however, SQL Server was using only 4 cores and performance was terrible. My favorite SQL Server expert Brent Ozar also commented and shared his relevant blog's link in the post. I was curious how Mark Varnas identified and resolved this. Robert Concepcion's comments and Brent's blog showed me the path. Let's go through the problem!
Figure-1: Poor VM Configuration will ruin your SQL Server
Understanding the Problem
Let's assume you are running a SQL Server Standard Edition in a virtual environment where you have 4 or more sockets with 1 virtual processor each. In this settings, everything will look fine. However, you will see, SQL Server is just using 4 CPUs instead of all available vCPUs. Top wait type will be SOS_SCHEDULER_YIELD. Though there are sufficient CPUs.
All because, SQL Server Standard Edition has compute capacity limitations on assigning sockets and CPUs. Here, tricky part is Standard Edition can use less than 4 sockets or 24 CPUs figure-2. When this compute capacity limitations blends with poor VM configuration, you will observe this problem.
Figure-2: Limitations of SQL Server Standard Edition
So, if you have a VM with 8 socket with, say, 8 virtual processors (figure-3), in this scenario your Standard Edition will end up with only 4 CPUs.
Figure-3: Inefficient per socket/per processor configuration. (Image courtesy Brent Ozar)
Identifying the Poor Configuration
You can identify this in couple of ways:
- sp_Blitz will signal you about "CPU Cores Offline" or "Memory Nodes Offline."
- SQL Server's properties will show the used CPUs (figure-4).
Figure-4: SQL Server Property Window will show the CPUs
- Below queries will show the CPU,
SELECT
(cpu_count / hyperthread_ratio) AS Number_of_PhysicalCPUs,
CPU_Count AS Number_of_LogicalCPUs
FROM sys.dm_os_sys_info
GO
SELECT
parent_node_id AS NUMA_Node,
COUNT(cpu_id) AS CPU_Count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
GROUP BY parent_node_id
ORDER BY parent_node_id;
GO
SELECT socket_count, cores_per_socket, cpu_count FROM sys.dm_os_sys_info;
GO
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
GO
Fixing it
Changing the VM's socket configuration will resolve the issue:
- Shut down the VM.
- Change the number of cores per socket from your VMWare's vCenter. For example, as shown in figure-3, make it as 2 sockets x 4 cores or even 1 socket x 8 cores. This will make:
- Better NUMA performance
- Better licensing efficiency
- Better OS scheduling and fewer CPU context switches
- Turn the VM back on.
Final Words
When poor VM configurations blends with compute capacity limitation, they become hidden performance killer of your workload. There might be long troubleshooting session, blame-game, consideration of costly licensing/hardware upgradation. Before taking any costly action, check your SQL Server for any poor VM configurations.
References
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!
For any queries, mail to mamehedi.hasan[at]gmail.com.