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 ?
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
rs.last_execution_time between '2019-11-14 22:05:12.370' and'2019-11-14 23:05:12.370'