Home » Microsoft » 70-762 » Which query uses the most cumulative CPU.
DRAG DROP
You are monitoring a Microsoft Azure SQL Database.
The database is experiencing high CPU consumption.
You need to determine which query uses the most cumulative CPU.
Flow should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than one or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:
Correct Answer:
Explanation/Reference:
Box 1: sys.dm_exec_query_stats sys.dm_exec_query_stats returns aggregateperformance statistics for cached query plans in SQL Server.
Box 2: highest_cpu_queries.total_worker_time DESC Sort on total worker time column
Example: The following example returns information about the top five queries ranked by average CPU time.
This example aggregates the queries according to their query hash so that logically equivalentqueries are grouped by their cumulative resource consumption.
USE AdventureWorks2012;
GO
SELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END – QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle)as ST) as query_stats GROUP BY query_stats.query_hash ORDER BY 2 DESC;
https://msdn.microsoft.com/en-us/library/ms189741.aspx