Here we will cover how to monitor Azure SQL Database CPU performance using dynamic management views, and show how to diagnose CPU high usage.
Dynamic management views (DMVs) are a valuable tool for monitoring the performance of Azure SQL Database. These views provide a wealth of information about the current state of the database, including resource usage, the performance of queries, and the health of the database. By using DMVs, you can gain insight into the performance of your database and take steps to improve it.
One of the key DMVs for monitoring the performance of Azure SQL Database is the sys.dm_db_resource_stats view. This view contains information about the resource usage of the database, including CPU usage, memory usage, and storage usage. By querying this view, you can determine the current workload of the database, and take appropriate action to manage it.
To view the resource usage of a database, you can use the following query:
SELECT
cpu_percent,
memory_percent,
storage_percent
FROM sys.dm_db_resource_stats;
This query returns the current CPU, memory, and storage usage of the database, as a percentage of the total available resources. For example, if the CPU usage is 50%, this indicates that the database is using half of its available CPU resources.
Another useful DMV for monitoring the performance of Azure SQL Database is the sys.dm_exec_query_stats view. This view contains information about the performance of queries in the database, including the execution count, the total execution time, and the average execution time. By querying this view, you can identify slow-running queries and other performance bottlenecks.
The query example (microsoft.com) returns a number of queries that collectively consume a large amount of CPU time.
PRINT '-- top 10 Active CPU Consuming Queries (aggregated)--';
SELECT TOP 10 GETDATE() runtime, *
FROM (SELECT query_stats.query_hash, SUM(query_stats.cpu_time)
'Total_Request_Cpu_Time_Ms', SUM(logical_reads) 'Total_Request_Logical_Reads',
MIN(start_time) 'Earliest_Request_start_Time', COUNT(*) 'Number_Of_Requests',
SUBSTRING(REPLACE(REPLACE(MIN(query_stats.statement_text), CHAR(10), ' '),
CHAR(13), ' '), 1, 256) AS "Statement_Text"
FROM (SELECT req.*, SUBSTRING(ST.text,
(req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN
DATALENGTH(ST.text)ELSE req.statement_end_offset
END-req.statement_start_offset)/ 2)+1) AS statement_text
FROM sys.dm_exec_requests AS req
CROSS APPLY
sys.dm_exec_sql_text(req.sql_handle) AS ST ) AS query_stats
GROUP BY query_hash) AS t
ORDER BY Total_Request_Cpu_Time_Ms DESC;
A second example (microsoft.com) will return the top 10 active long-running CPU-intensive queries.
PRINT '--top 10 Active CPU Consuming Queries by sessions--';
SELECT TOP 10 req.session_id, req.start_time, cpu_time 'cpu_time_ms',
OBJECT_NAME(ST.objectid, ST.dbid) 'ObjectName',
SUBSTRING(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset /
2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE
req.statement_end_offset END-req.statement_start_offset)/ 2)+1), CHAR(10), '
'), CHAR(13), ' '), 1, 512) AS statement_text
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY cpu_time DESC;
GO
Furthermore, CPU issues from the past can be retrieved (microsoft.com).
-- Top 15 CPU consuming queries by query hash
-- note that a query hash can have many query id if not parameterized or
not parameterized properly
-- it grabs a sample query text by min
WITH AggregatedCPU AS (SELECT q.query_hash, SUM(count_executions *
avg_cpu_time / 1000.0) AS total_cpu_millisec, SUM(count_executions *
avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_millisec,
MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec, MAX(max_logical_io_reads)
max_logical_reads, COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans,
COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, SUM(CASE WHEN
rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS
Aborted_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Regular' THEN
count_executions ELSE 0 END) AS Regular_Execution_Count, SUM(CASE WHEN
rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS
Exception_Execution_Count, SUM(count_executions) AS total_executions,
MIN(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON
qt.query_text_id=q.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
JOIN sys.query_store_runtime_stats AS rs ON
rs.plan_id=p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi ON
rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id
WHERE rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception')AND
rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
GROUP BY q.query_hash), OrderedCPU AS (SELECT query_hash,
total_cpu_millisec, avg_cpu_millisec, max_cpu_millisec, max_logical_reads,
number_of_distinct_plans, number_of_distinct_query_ids, total_executions,
Aborted_Execution_Count, Regular_Execution_Count, Exception_Execution_Count,
sampled_query_text, ROW_NUMBER() OVER (ORDER BY total_cpu_millisec DESC,
query_hash ASC) AS RN
FROM
AggregatedCPU)
SELECT OD.query_hash, OD.total_cpu_millisec, OD.avg_cpu_millisec,
OD.max_cpu_millisec, OD.max_logical_reads, OD.number_of_distinct_plans,
OD.number_of_distinct_query_ids, OD.total_executions,
OD.Aborted_Execution_Count, OD.Regular_Execution_Count,
OD.Exception_Execution_Count, OD.sampled_query_text, OD.RN
FROM OrderedCPU AS OD
WHERE OD.RN<=15
ORDER BY total_cpu_millisec DESC;
As a result, once you identify problematic queries, you can tune them to reduce CPU utilization. This will be a new blog post on tuning queries.