Answered by:
Metric CPU show 100% , but Query Performance Insight show only 60% same period,what takes other 40%

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 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
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
- Proposed as answer by Mike Ubezzi (Azure)Microsoft employee, Moderator Thursday, November 28, 2019 1:54 AM
- Marked as answer by Mike Ubezzi (Azure)Microsoft employee, Moderator Thursday, November 28, 2019 1:56 AM
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
-
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
- Proposed as answer by Mike Ubezzi (Azure)Microsoft employee, Moderator Thursday, November 28, 2019 1:54 AM
- Marked as answer by Mike Ubezzi (Azure)Microsoft employee, Moderator Thursday, November 28, 2019 1:56 AM
-