Post reply

Is it possible get from Query Store historical ~ CPU % usage by each query ?

  • November 18, 2019 at 6:29 pm

    #3698609

    Is it possible get from Query Store historical ~ CPU % usage by each query in 1 hr range?

    Many places share code  where "avg cpu utilization " or max cpu utilization" extracted from query store

    (https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/sql-database/sql-database-monitoring-with-dmvs.md)

    -- 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;

     

     

     

    Question

    1. is possible convert historical  cpu time utilization to cpu percentage  for each query for 1 hour period

    remove WHERE OD.RN<=15    replace rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())  with range   (rsi.start_time >= @Period1Start) AND (rsi.end_time <= @Period1End)

    and you will get all the queries with given  period ,

     

    but I want to see percentage of total cpu usage....

    Thank  you

  • November 19, 2019 at 7:10 pm

    #3698957

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply