Last week, I had a very interesting situation where client believed they had an issue their slow running server. They sent me a few initial details about their server and after looking at that I told them that they have a CPU pressure issue. They were very surprised as to how did I know that information in such a little time. I eventually expressed them it is possible to detect CPU pressure with Wait Statistics. We explained to them my method during the Comprehensive Database Performance Health Check.
If you are interested, how SQL Wait Statistics works, I strongly recommend you to read my SQL Server Wait Statistics series. SQL SERVER – Wait Stats – Wait Types – Wait Queues – Day 0 of 28.
Here is the script which can help you to identify if there is a CPU pressure or not with the help of SQL Wait Statistics.
-- Signal Waits for instance SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits], CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits] FROM sys.dm_os_wait_stats OPTION (RECOMPILE);
Higher the Signal wait stats are not good for the system. A very high value indicates CPU pressure. In my experience, when systems are running smoothly and without any glitch, the Signal wait stat is lower than 20%. Again, this number can be debated. In other words, lower is better and higher is not good for the system.
When my client sent me the result of the above statement, the value of Signal Wait Time was around 75%, I am very confident that it is very high value and leading to SQL Server Performance problems.
However, just like doctors, we should always double-check every system and then give a diagnosis. Similarly, I had sent them another query SQL SERVER – Measure CPU Pressure – Detect CPU Pressure, which further proved that the organization was struggling with the CPU performance and needed my help with Comprehensive Database Performance Health Check.
Once we get on consulting call, we spent next 2 hours to identify the CPU bottleneck and removing it from the system. The problem which was blocking the CPU to be used efficiently was very simple, once we fixed it, their system started to run extremely fast. In the future blog posts, I will write about them.
Reference: Pinal Dave (https://blog.sqlauthority.com)
