I recently upgraded my SQL Server 2014 hardware. The CPU profile is looking weird. I have 24 CPUs, all CPUs are VISIBLE ONLINE, MAXDOP is 0. The SQL CPU usage is peaking at 60% and not going beyond that although the queries are highly CPU intensive and mostly in running/runnable state. I have checked the session log dumps that the Total Request Time is only slightly above the CPU time so have ruled out io waits, etc. Also checked the OS CPU usage is tracking only slightly higher than the SQL CPU usage. Wondering why CPU is not fully utilized. Previous hardware had 16 CPUs and used to saturate at ~95%
Sql-server – Underutilized CPU on SQL server
sql server
Related Question
- Sql-server – Non-yielding Scheduler Error with dump caused by heavy queries on SQL 2016
- Sql-server – high writelog wait on OLTP system and fast SSD storage, log flush is slow
- SQL Server Performance Troubleshooting – Major Performance Problems on Production Server
- SQL Server Performance – Total Server Memory Consumption Stagnant Despite Available Memory
- Sql-server – SQL Server instance running out of worker threads
- SQL Server 2017 – Frequent AG Disconnects with 500 Databases Since CU9
- SQL Server – Troubleshooting CPU Issues
- Sql-server – CPU for SQL Server
Best Answer
A few things:
Check licensing, depending on edition and how you are licensed it might be ignoring some of the extra cores.
It might be that your load can't efficiently use more cores. Using multiple cores for any statement doesn't necessarily make it faster, in fact for many it can make them slower due to the work to split the data and recombine the output, so the processing engine might simply not be seeing an advantage in using more cores right now. If your general workload is that it is running ~16 processes that it thinks won't benefit from multiple threads then it is only going to be using ~16 cores worth of CPU power.
You might be hitting an IO bottleneck that was previously masked by the CPU bottleneck.
Each of those cores may be more powerful than each of the ones in the previous chip(s), which will further reduce the likely peak % CPU use if you are hitting IO bottlenecks.
If your workload involves a lot of table/index scanning on objects that are of significant size compared to the CPU's cache (which most objects in a large DB will be) but not compared to the machine's RAM, then you might be hitting a memory bandwidth bottleneck instead of an IO bottleneck.
Finally, though perhaps most importantly: Is anything actually being too slow? It might be that your workload simply does not need more hardware power at this time.