Troubleshooting High Stolen CPU Scenario On SQLServer

In my last blog How To Monitor All SQLServers LIVE, I shared how I use open-source SQLMonitor every day to monitor SQLServers in my organization. This tool helps me and my team to ensure that we proactively detect any issue with SQLServers even before users can reach out to us.

At the start of my day today, as usual, I started with SQLServer / Monitoring – LIVE – All Servers dashboard.

The above panel is showing any SQLServer that needs DBA attention. But there is this server highlighted in the above screenshot showing a significant gap b/w OS CPU and SQL CPU. Any gap of over 15-20% b/w OS & SQL CPU consumption is something that should be investigated. This was an indicator that there is some other non sqlservr process that is consuming high CPU on the host.

From here, I go to SQLServer / Monitoring – LIVE – Distributed dashboard. This dashboard exposes all the key performance metrics of any specific SQLServer. In this dashboard, I expand the panel of row Trend – CPU Utilization.

From the above panel, I can clearly see a heavy gap in CPU utilization % b/w OS & SQL. The key point here is that I even know the exact times for which the CPU gap has been happening. So I set the grafana time range to the start/end of the issue duration in order to see further details in a filtered manner.

Next, I navigate to row OS Tasks (Processes) – CPU Utilization. Under this row, we have 2 panels representing CPU utilization by different processes. This is the final place that would tell me the exact culprit processes that were consuming high CPU on the host and causing CPU issues for SQLServer.

As we can notice in the above screenshot, for an average of 5-6 minutes of clock time, DatabaseMail.exe process has consumed an average of 1.63 hours of CPU time. Whereas sqlservr.exe process’s contribution is just 36.6 minutes for an average clock time duration of 5-6 minutes.

From here troubleshooting steps could be different for different servers/applications. On this server, we found out that was expected due to lots of push notifications to end users.

To summarize, having a robust tool like SQLMonitor makes SQLServer monitoring and troubleshooting very easy. I hope this would be helpful to SQLServer professionals having a hard time figuring out issues with their SQLServers.