If you have to monitor the application using Azure SQL Database. Sometime, you need to point out what are the query consume a lot

The query will retrun the results and it is ordered by CPU by default.

Running the query

--Top Consumers (ordered by CPU by default) with SQL TEXT and other useful info.

--Can narrow the query down to last x hours or days, or a specific timeframe


declare @starttime as datetime = '03/01/2023 10:00'
declare @endtime as datetime = '03/01/2023 10:30'

SELECT TOP 20

q.query_id,

rs.count_executions as [Execution count],

rs.avg_duration/1000 as [Avg_Duration(ms)],

rs.max_duration/1000 as [Max_Duration(ms)],

rs.min_duration/1000 as [Min_Duration(ms)],

rs.avg_cpu_time/1000 as [Avg_CPU_time(ms)],

rs.max_cpu_time/1000 as [Max_CPU_time(ms)],

rs.count_executions*(rs.avg_cpu_time/1000) as [Total_CPU_Time(ms)],

q.avg_compile_duration/1000 as [avg_compile_duration(ms)],

q.last_compile_duration/1000 as [last_compile_duration(ms)],

p.compatibility_level,

rs.avg_logical_io_reads,

rs.avg_logical_io_writes,

rs.avg_physical_io_reads,

rs.last_physical_io_reads,

rs.max_physical_io_reads,

rs.avg_num_physical_io_reads,

rs.last_num_physical_io_reads,

rs.max_num_physical_io_reads,

rs.avg_dop,

rs.avg_log_bytes_used,

qt.query_sql_text,

qt.query_text_id, p.plan_id,

GETUTCDATE() AS CurrentUTCTime,

rs.last_execution_time

FROM sys.query_store_query_text AS qt

JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id

JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id

JOIN sys.query_store_runtime_stats AS rs

ON p.plan_id = rs.plan_id

WHERE rs.last_execution_time between @starttime and @endtime

ORDER BY rs.count_executions*rs.avg_cpu_time DESC --by Total CPU Time

--ORDER BY rs.count_executions*rs.avg_log_bytes_used DESC --by Total Log Space

--ORDER BY rs.avg_cpu_time DESC; -- by CPU per execution

--ORDER BY rs.max_duration DESC; -- by Duration per execution

--ORDER BY rs.avg_logical_io_reads DESC;  -- by IO per execution


You can change the query depend on your expection.


References

https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios?view=sql-server-ver16

https://sqlserverutilities.com/get-sql-server-top-cpu-consumers/