DP-800: Developing AI-Enabled Database Solutions

Question

You have an Azure SQL database that supports an AI-driven product-search API.

Identify the queries consuming the most CPU during the previous two hours by using Query Store data. The solution must aggregate CPU consumption across executions and return only the top 15 query hashes.

Complete the Transact-SQL code. Each value may be used once, more than once, or not at all.

Drag & Drop

WITH AggregatedCPU AS
(
    SELECT
        q.query_hash,
        SUM(rs.count_executions *  / 1000.0) AS total_cpu_ms
    FROM sys.query_store_query_text AS qt
    INNER JOIN sys.query_store_query AS q
        ON qt.query_text_id = q.query_text_id
    INNER JOIN sys.query_store_plan AS p
        ON q.query_id = p.query_id
    INNER JOIN sys.query_store_runtime_stats AS rs
        ON rs.plan_id = p.plan_id
    INNER JOIN  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 >= 
    GROUP BY q.query_hash
),
OrderedCPU AS
(
    SELECT
        query_hash,
        total_cpu_ms,
        ROW_NUMBER() OVER (ORDER BY total_cpu_ms DESC, query_hash ASC) AS rn
    FROM AggregatedCPU
)
SELECT query_hash, total_cpu_ms
FROM OrderedCPU
WHERE rn <= 15
ORDER BY total_cpu_ms DESC;

Explanation

Query Store records aggregated runtime statistics by plan and interval. Multiplying count_executions by avg_cpu_time produces the aggregate CPU time for each statistics row; summing those values by query_hash aggregates CPU across executions and plans. sys.query_store_runtime_stats_interval supplies the interval timestamps, which use UTC, so a UTC two-hour cutoff correctly filters the required period. Ranking the summed values and retaining rows through 15 returns the top 15 query hashes.

Learn more

DP-800: Developing AI-Enabled Database Solutions

AstroTutor

Notes

Readiness

QuestionQ69

Community Discussion