SQL Script to Monitor CPU Utilization

SQL Script to Monitor CPU Utilization

This post helps you in understanding and using SQL Script to Monitor CPU utilization. There are certain scenarios where we can quickly use this script to get SQL Server CPU utilization.

  • When there is a performance issue, and you need to quickly check the CPU usage
  • When doing Root Cause Analysis
  • When we only have access to SQL Server not to Windows

SQL Server CPU Utilization history report for last N minutes:

Below is the SQL Script to Monitor CPU utilization. This script captures the CPU usage history report from last 10 min. This can be customized by changing the variable “@lastNmin” value.

 

SQL Server Database wise CPU Utilization

From above script we come to know that SQL Server is utilizing high CPU, now the next step is to find out which database from the SQL instance is causing the high CPU utilization. Below is the SQL Script to Monitor CPU Utilization database wise.

SQL Server Query Wise CPU Utilization

From the above scripts you confirmed that there is a huge CPU utilization from one of the SQL Instance and you identified the database which is causing high CPU. Now the next step is to identify top 10 queries which are causing high CPU utilization. Here is the SQL Script to Monitor CPU Utilization query level. This is a wonderful script provided by SQLBlog and SQLKnowlwdge.

Note: Remember that it returns the list of costly queries which causes high CPU utilization when only the CPU usage is >=80% from last 10 Min, otherwise it returns nothing. You can modify the script as per your needs.

Script to find Top 20 Costliest Stored Procedures – High CPU:

 This script sourced from here. It results a list of stored procedures which are utilizing high CPU. This script goes through the buffer cache and find out these results based on Total and Average worker thread times. Below is the SQL Script to Monitor CPU Utilization from the stored procedure point of view.

Script to find Top 20 Costliest Queries – High CPU

 This script sourced from here. It results a list of queries which are utilizing high CPU. Below is the SQL Script to Monitor CPU Utilization from Ad-hoc queries.

Summary:

I believe these scripts will be helpful to quickly get the Instance, Database and Query level CPU utilization reports.

References:

http://sqlblog.com/blogs/ben_nevarez/archive/2009/07/26/getting-cpu-utilization-data-from-sql-server.aspx

http://sqlknowledge.com/2010/12/how-to-monitor-sql-server-cpu-usage-and-get-auto-alerts/

https://sqlserverperformance.wordpress.com/2013/05/06/sql-server-2012-diagnostic-information-queries-may-2013/

http://www.johnsansom.com/how-to-identify-the-most-costly-sql-server-queries-using-dmvs/

 

 

Posted in Performance Tuning, SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , | 2 Comments

Leave a Reply

2 Comments on "SQL Script to Monitor CPU Utilization"

Notify of
avatar
Sort by:   newest | oldest | most voted
trackback

[…] SQL Server CPU Utilization […]

trackback

[…] SQL Server – CPU […]

wpDiscuz