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:
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 the following one:
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.
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)', 'int'), cpu_sql = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)', '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
Perf Mon is one 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
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:
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 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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.