Troubleshooting High Stolen CPU Scenario On SQLServer

Spread the love

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.

Monitor All SQLServers using Open Source tool SQLMonitor

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.

Analyze CPU Utilization Trend of SQLServers using Open Source tool SQLMonitor

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.

Find CPU consumers on SQLServer host machine using Open Source tool SQLMonitor

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.

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.