Monitoring CPU usage on your SQL Server or Azure SQL is one of the most important monitoring tasks in performance monitoring and troubleshooting. In addition, CPU usage is one of the most important parameters that you need to measure to determine the performance baseline of your workload. In this article you will see some of the common tools and methods for monitoring CPU usage on SQL Server and Azure SQL.
Monitoring CPU usage Includes:
Monitor CPU usage on your instance
Identify queries that might cause high CPU sage
SQL Server Management Studio
SQL Server Management Studio provides easy to use reports where you can monitor usage of various system parameters. Performance Dashboard is one of the useful reports that you can use to monitor CPU usage:
Once you connect to your SQL Server or Azure SQL instance, you can select Reports > Performance Dashboard and see the current and historical values of CPU usage.
In addition, you can find top CPU consumers by selecting another report - Reports > Standard Reports > Performance - Top Queries by Average CPU time. The report might look like this:
Here you can find the query texts of the top resource consumers and identify the queries that are causing the CPU issues.
If you are using Azure SQL or SQL Server 2016+, you can find the queries that used most of the CPU in Database > Query Store > Top Resource Consuming Queries.
Dynamic Management views
SQL Server Database Engine provides a set of useful system views that you can use to find CPU usage. As an example, you can use sys.dm_os_ring_buffers view to find CPU usage:
cpu_idle = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)', 'int'),
cpu_sql = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)', 'int')
SELECT TOP 1 CONVERT(XML, record) AS record
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '% %'
ORDER BY TIMESTAMP DESC
) as cpu_usage
There are many third-party tools or community scripts (that use T-SQL or PowerShell) that enable you to monitor performance of your database. Most of them use CPU utilization as one of the fundamental metrics. As an example, you can use Get-DbaCpuUsage command from PsDba tools PowerShell library to monitor CPU usage using the following PowerShell:
This library also enables you to get the information about historical CPU usage per Query Store intervals (available only in SQL server 2016+ and Azure SQL):
select start_time, execution_type_desc,
tps = sum(count_executions)/ min(interval_mi) /60,
[cpu %] = ROUND(100 * sum(count_executions*cpu_time_s)/ min(interval_mi) /60 /(SELECT top 1 cpu_count FROM sys.os_sys_info)/*cores*/,1)
group by start_time, execution_type_desc
order by start_time desc
In addition this library enables you to easily find top queries or query plans that consumed most of the CPU time:
select top 10 *
order by cpu_time_ms desc;
select top 10 *
order by cpu_time_ms desc