SQL Server Tells You Exactly Which Queries Are Costing You the Most
Every time a query executes, SQL Server keeps score.
CPU consumed. Logical reads performed. Time elapsed. Execution count.
All of it accumulated in the plan cache via sys.dm_exec_query_stats — sitting there, waiting to be asked.
This is not a trace. This is not a profiler session you forgot to start before the problem happened. This is the actual historical record of every cached query plan since the last restart or recompile.
Read it correctly and it tells you exactly where to focus tuning effort.
What These Queries Do
The raw DMV includes everything — the good, the bad, and the query someone wrote in 2019 that nobody talks about. These two scripts cut straight to the offenders.
Each result is ranked and categorised:
| Script | Ranked By | Common Causes |
|---|---|---|
| Part 1 | Total CPU | Expensive logic, missing indexes, bad plans |
| Part 2 | Logical Reads | Table scans, missing indexes, over-fetching data |
The Avg columns tell the real story — focus on those first.
A query with 10 million logical reads across 1 million executions is a different problem to one with 10 million reads in a single execution. Same number. Completely different fix.
How to Use It
Sort by total for overall impact. Sort by average for per-call efficiency.
If the instance was recently restarted, the totals will be small — but the ratios are still meaningful.
In an energy data environment — high-frequency telemetry, rolling aggregations, SCADA historian queries — logical reads is often where the real cost hides.
Query Plan Cache Scripts
Loading…
Gareth Winterman