Monitoring and diagnosing slow queries

Overview

First bound the routine, company/branch, time, volume and observed change. Compare duration, CPU, reads and waits; a blocked query differs from a CPU-heavy query. SQL Server Query Store retains queries, plans and statistics by time window, depending on enablement, retention and version. The SQL below reads per-row/interval metrics from the last 24 hours without query text or configuration changes. It is not an aggregated total-cost ranking. (Sources: refs. 1–2.)

Syntax

SELECT ... FROM sys.query_store_runtime_stats JOIN sys.query_store_plan ...

Commented example

-- SQL Server: per-row metric reads, no plan changes.
SELECT TOP (20)
    p.query_id, r.plan_id, r.runtime_stats_interval_id,
    r.count_executions, r.avg_duration, r.last_execution_time
FROM sys.query_store_runtime_stats AS r
JOIN sys.query_store_plan AS p ON p.plan_id = r.plan_id
WHERE r.last_execution_time >= DATEADD(hour,-24,SYSDATETIMEOFFSET())
ORDER BY r.avg_duration DESC;

Related content