Calculating the Cost of a Slow Query

A slow query does not reveal its full price in one execution. The cost of a slow query grows with every repeated call. A five-second report used once a month can matter less than a 100-millisecond lookup called millions of times. Put duration and frequency together, then account for server resources and user impact.

Start the Slow Query Cost With Total Time

Multiply average elapsed time by execution count over a defined period to estimate cumulative occupied time. Ten milliseconds multiplied by a million executions is ten thousand seconds of elapsed query time. That does not mean a single user waited ten thousand seconds. Executions overlap. It does show a large amount of request time flowing through one statement.

I use the same period for all candidates, such as a busy day or a complete week. A monthly report cannot be judged from a weekday afternoon. Record the time window and workload mix, because a change in traffic can look like a query regression even when per-execution behavior is stable. How many times does this statement run during your peak hour?

Separate Elapsed Time and CPU

Elapsed duration includes waits for locks, I/O, memory grants, and client consumption. CPU time measures processing consumed by the query. A long query with little CPU can be blocked or waiting on storage. A short query with substantial CPU and high frequency can saturate the server. These problems need different fixes.

Compare total CPU as average CPU multiplied by count, or use the total metrics Query Store stores for each interval. Avoid adding elapsed durations as if they were exclusive server time. Concurrent executions overlap in wall-clock time. I report both total resource use and the latency users experience.

Include Logical and Physical Reads

Logical reads count pages accessed through the buffer pool and reveal how much data a query touches. Physical reads reflect pages fetched from storage, but caching can vary. A query with many logical reads can pressure memory and CPU even if storage reads appear low today. Multiply per-execution reads by frequency to see cumulative demand.

The goal is not to minimize every read at any cost. A covering index can reduce reads but increase write work. Use a whole-workload view. A small saving on a lookup that runs all day can outweigh a big saving on a report that runs once a month. Multiply the saving by the run count before you choose.

Use Query Store for a Period View

Query Store records runtime statistics by interval when enabled and configured to retain the relevant queries. This example summarizes executions, total duration, and total CPU for a recent window. The time columns in Query Store use microseconds for these measures, so divide by 1,000,000 for seconds. Confirm Query Store capture and retention settings before treating absence as zero usage.

The query ranks total duration, which is a first pass. Add reads or focus on peak intervals when the user experience matters more than a daily sum. Review the text before taking action. Internal or maintenance queries can appear among the leaders.

SELECT TOP (20) q.query_id,
       SUM(rs.count_executions) AS executions,
       SUM(rs.avg_duration * rs.count_executions) / 1000000.0
           AS total_duration_seconds,
       SUM(rs.avg_cpu_time * rs.count_executions) / 1000000.0
           AS total_cpu_seconds
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
  ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
  ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(day, -7, SYSDATETIMEOFFSET())
GROUP BY q.query_id
ORDER BY total_duration_seconds DESC;

Translate a Slow Query Delay Into User Cost

For an interactive request, estimate how many users meet the delay, how many times per day, and whether the delay blocks the next action. A five-second wait during checkout can be more damaging than a five-second background task. Treat any money estimate as an explicit assumption, not a number produced by the DMV.

A simple model is affected requests times avoidable seconds times a chosen value per user-hour, divided by 3,600. State the value and whether waits overlap with other work. I use such estimates to frame a decision, not to pretend a precise business loss has been measured. The application owner should validate the assumptions.

Distinguish Frequency From Importance

A critical once-a-day close process can deserve immediate attention despite a low execution count. A frequent query that runs inside a noncritical cache refresh can have a lower user impact. Include deadlines, service objectives, and failure consequences. Runtime metrics show scale. They do not know business priority.

I categorize candidates by user-facing latency, resource consumption, and operational risk. This prevents an algorithm from ranking every query solely by accumulated milliseconds. It also helps explain why a slow report is scheduled for later while a faster but widely used lookup gets tuned first.

Measure Before and After Fairly

Capture baseline metrics over a comparable window, including parameter distribution and data size. After tuning, compare per-execution duration, total CPU, reads, and execution frequency. A caching change can reduce database calls but shift load into an application cache. A new index can help the query while slowing writes. Track the entire affected path.

If traffic doubles, total duration can rise even when each execution is faster. Normalize by count, then review aggregate load. I keep both views because the server pays for the total while users feel individual latency. Neither number tells the whole story alone.

Watch Tail Latency and Waits

An average can hide a small group of terrible executions. Inspect maximum and high-percentile latency in application telemetry, plus Query Store intervals and wait categories. Parameter-sensitive plans, blocking bursts, and memory grant queues can create long tails. Those cases deserve testing with the actual parameters and concurrency that trigger them.

A query can be cheap at noon and costly during an import. Define the period in which the cost occurs. I avoid saying an index fixed a slow query if it only moved the bottleneck into a different peak hour. The business cares about the window in which work must finish.

Prioritize a Measured Fix by Slow Query Cost

Estimate the potential improvement, implementation effort, and regression risk. A query rewrite that removes repeated scans can deliver a clear gain. A broad hint or new covering index can have side effects and needs stronger testing. Start with the plan, row estimates, and wait reason. The cost calculation directs attention. It does not prescribe the fix.

The cost of a slow query is the combination of repetition, resource use, and consequence. A single dramatic screenshot tells less than a week of measured behavior. Put the numbers on the same timeline, then choose the change that improves the whole workload.

SELECT TOP (10) execution_count,
       total_worker_time / 1000000.0 AS total_cpu_seconds,
       total_logical_reads
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC;

Related reading on this blog: List Expensive Queries: Updated March 2021 and Long Running Queries with Execution Plan.

The cost of a slow query is not one duration, it is repeated work and its business consequence.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.