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