is a way to correlate cpu % usage with result from query_store_query,query_store_runtime_stats (see query attached) RRS feed

  • Question

  • Hi, 

     Azure portal show cpu % at 100%   between 2019-11-20 18:00:00 and 2019-11-20 19:00:00 (UTC)

    I executed query bellow 

       case  execution_type
         when 0 then 'successfully finished'
    when 3 then '! Client initiated aborted execution'
    when 4 then 'Exception aborted execution'
       [rs].[count_executions] * round([rs].[avg_cpu_time]/1000000,1) as total_cpu_time_sec,
       [rs].[count_executions] * round([rs].[avg_duration]/1000000,1) as total_duration_sec,
       round([rs].[avg_logical_io_reads],0)as avg_logical_io_reads,
    FROM [sys].[query_store_query] [qsq]
    JOIN [sys].[query_store_query_text] [qst]
       ON [qsq].[query_text_id] = [qst].[query_text_id]
    JOIN [sys].[query_store_plan] [qsp]
       ON [qsq].[query_id] = [qsp].[query_id]
    JOIN [sys].[query_store_runtime_stats] [rs]
       ON [qsp].[plan_id] = [rs].[plan_id]
    JOIN [sys].[query_store_runtime_stats_interval] [rsi]
       ON [rs].[runtime_stats_interval_id] = [rsi].[runtime_stats_interval_id]
    [rsi].[start_time] ='2019-11-20 18:00:00.0000000 +00:00'

    ORDER BY [rs].[runtime_stats_interval_id];

    and get result (image show only part of it)

    I pasted result to excel  

    Sum of column total_cpu_time_sec = 358 seconds

    Sum of total_duration_sec =  2850 seconds

    Since CPU %  was 100 for full 1 hour(3600 seconds) , why "Sum of Total_cpu_time_sec" is only 358 seconds ?

    DB set to use  1000 DTU

    Thank you

    Friday, November 22, 2019 5:24 PM

All replies

  • Hi AlexMtl,

    Apologies for the delay in responding to this post. Can you use the Query Store views as a comparison, as the headers in your query do not include CPU %, unless you are seeing this detail other than in this post.

    The query headers in the Excel spreadsheet:

    plan_id execution_type

    Total CPU Time and Total Duration are not necessarily the same metric. The Query Store views will help you understand the data and you can modify your custom query as needed.

    Please let me know if I have missed a detail in your question and I would be happy to replicate your specific issue. Do let me know if you have additional questions.



    Tuesday, November 26, 2019 8:03 PM
  • ---This first thing to check if CPU is at 100% is to look for parallel queries:

    -- Tasks running in parallel (filtering out MARS requests below):
    select * from sys.dm_os_tasks as t
     where t.session_id in (
       select t1.session_id
        from sys.dm_os_tasks as t1
       group by t1.session_id
      having count(*) > 1
      and min(t1.request_id) = max(t1.request_id));

    -- Requests running in parallel:
     select *
       from sys.dm_exec_requests as r
       join (
               select t1.session_id, min(t1.request_id)
              from sys.dm_os_tasks as t1
             group by t1.session_id
            having count(*) > 1
               and min(t1.request_id) = max(t1.request_id)
          ) as t(session_id, request_id)
         on r.session_id = t.session_id
        and r.request_id = t.request_id;

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 27, 2019 4:45 AM