Monitor CPU usage on SQL Server and Azure SQL

[JovanPop]

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:

SELECT
         cpu_idle = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int'),
         cpu_sql = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
FROM (
         SELECT TOP 1 CONVERT(XML, record) AS record
         FROM sys.dm_os_ring_buffers
         WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
         AND record LIKE '% %'
		 ORDER BY TIMESTAMP DESC
) as cpu_usage

If you are using Azure SQL Managed Instance, you can use sys.server_resource_starts view:

select top 200
    start_time,
    [cpu usage %] = avg_cpu_percent
from sys.server_resource_stats
order by start_time desc

This view can also return historical information about the CPU usage on your instance. In Azure SQL single database you can use equivalent sys.resource_stats

Performance Monitor (SQL Server-only)

Perf Mon is a useful tool that you can use to track CPU usage on the server or virtual machine where your SQL Server is running. Some of the parameters that you can monitor are:

- Processor: % Privileged Time

- Processor: %User Time

- System: Processor Queue Length

Azure Portal (Azure SQL-only)

The Azure portal enables you to monitor performance with easy-to-use interface. If you navigate to your Managed Instance or Single Database in the Azure portal you can see CPU usage on the main blade: 

Custom tools

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:

Get-DbaCpuUsage -SqlInstance sql2017

You can also use custom scripts  and libraries like the Query Performance Insights library that enables you to query CPU usage:

select *
from qpi.cpu_usage;

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)
from qpi.db_query_plan_exec_stats_history
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 *
from qpi.db_query_exec_stats_history
order by cpu_time_ms desc;

select top 10 *
from qpi.db_query_plan_exec_stats_history
order by cpu_time_ms desc