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.
How 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:

Answer:

Explanation:
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.
Thisexample 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,
((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