none
Metric CPU show 100% , but Query Performance Insight show only 60% same period,what takes other 40% RRS feed

  • Question

  • I am investigation DTU spike for  specific period of time

    In my case  time period  in question between 08:00 PM and 11:00 UTC

    Azure portal ->db ->metric -> Cpu % avg  show that I have 100% usage of CPU  (db DTU =  1750) between 08:15 PM and 08:45 PM

    Azure portal->db-Query Performance Insight-> Cpu ->custom -> time frame  between 08:00 PM and 09:00 PM (top 5 queries) show maximum of 60 %  CPU usage 

    when I scroll for CPU usage for from left to right I see that cpu usage of each query and total ,they again no more then 60%

    Questions

    Any way to find  what is taking other 40 %  of CPU, but not showing  as  query...?

    usage of extended events , triggers ,e t.c  is not options. I can use only Azure portal and Query store(any changes to configuration of query store also restricted)

    I am not looking at  real time  CPU, but CPU at  time period that happen 72 hours ago in particular time frame

    I adjusted script bellow  pull info from query store for all queries executed in period of time , problem that am not getting any data if  time is <24 hours from real time , otherwise you get list of all queries (I hope) executed (at least I can use it clients want to check what happen less then 24 hours ago )

     

    Now  I trying to convert  avc_cpu_time_seconds   to % of  cpu usedany idea how to do this ?

    select

    p.plan_id,

    rs.count_executions,

    qsqt.query_sql_text,

    convert(numeric(10,2), (rs.avg_cpu_time/1000)) as 'avg_cpu_time_seconds',

    convert(numeric(10,2), (rs.avg_duration/1000)) as 'avg_duration_seconds',

    convert(numeric(10,2), rs.avg_logical_io_reads) as 'avg_logical_io_reads',

    convert(numeric(10,2), rs.avg_logical_io_writes) as 'avg_logical_io_writes',

    convert(numeric(10,2), rs.avg_physical_io_reads) as 'avg_physical_io_reads',

    convert(numeric(10,0), rs.avg_rowcount) as 'avg_rowcount'

    from sys.query_store_query q

    JOIN sys.query_store_plan p ON q.query_id = p.query_id

    JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id

    JOIN sys.query_store_query_text qsqt

    ON q.query_text_id = qsqt.query_text_id

    WHERE

    rs.last_execution_time between '2019-11-14 22:05:12.370'  and'2019-11-14 23:05:12.370'

    order by

    rs.count_executions

    Wednesday, November 20, 2019 3:42 PM

Answers

  • Sorry I will clarify

     1.  >24 (greater than) or <24 (less than)? 

             24 + hour back from real time 

     2.   Query Performance Insights show data when I choose Past week 

          but Query Performance Insights Metric CPU  and Database overview CPU show different values for CPU % for 

         same period of time, but i think I found reason for it ...

         Customer located in Europe ,we are in EST zone, so we use UTC time for communication

         When I open Database overview (Compute utilization) CPU I switch to UTC in Time range 

         but noticed  Query Performance Insights  did not switch to UTC... ,so I was looking in wrong time period 

         

    Thursday, November 21, 2019 3:08 PM

All replies

  • Hi AlexMtl,

    Please take a look at the Custom Tools section of the below blog post:

    Monitor CPU usage on SQL Server and Azure SQL

    The Query Performance Insights provides a set of views you can leverage for historical metrics. In your post, you mention you are not seeing any data for events less than 24 hours,"problem that am not getting any data if  time is <24 hours from real time".

    I have a question. Is it >24 (greater than) or <24 (less than)? Are you leveraging Log Analytics to collect and store historical events?

    Regards,

    Mike

    Wednesday, November 20, 2019 10:59 PM
    Moderator
  • Sorry I will clarify

     1.  >24 (greater than) or <24 (less than)? 

             24 + hour back from real time 

     2.   Query Performance Insights show data when I choose Past week 

          but Query Performance Insights Metric CPU  and Database overview CPU show different values for CPU % for 

         same period of time, but i think I found reason for it ...

         Customer located in Europe ,we are in EST zone, so we use UTC time for communication

         When I open Database overview (Compute utilization) CPU I switch to UTC in Time range 

         but noticed  Query Performance Insights  did not switch to UTC... ,so I was looking in wrong time period 

         

    Thursday, November 21, 2019 3:08 PM
  • Thank you for the additional detail. I am going to mark this as the answer as it addresses your issue. 

    Regards,

    Mike

    Thursday, November 28, 2019 1:55 AM
    Moderator