Metric CPU show 100% , but Query Performance Insight show only 60% same period

  • ebooklub

    Hall of Fame

    Points: 3885

    Hi,

    I am investigation DTU spike for period 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...?

    Can I trust metric in Azure portal?

    Can run SQL to get historical usage of CPU by processes  at particular period of time ?

    Thank you

     

     

     

     

     

     

     

     

     

     

     

     

     

     

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 18999

    You can use Whoisactive and sp_BlitzFirst both supports Azure, Also try to look query store and extended events.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • ebooklub

    Hall of Fame

    Points: 3885

    Hi,

    I should clarify it ,

    1. 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)

    2. 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 used , any 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

     

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

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