Finding the Most Frequently Run Queries

The fastest query on the screen can still dominate CPU if it runs constantly. Finding the most frequently run queries exposes that pattern. A statement that finishes in a few milliseconds can still consume serious CPU when it runs continuously. Frequency deserves its own ranking beside average duration.

Why the Most Frequently Run Queries Matter

Each execution has setup, plan, CPU, reads, and result delivery costs. If a tiny lookup runs once per page component rather than once per page, it can multiply quickly. The database sees the aggregate even when each caller sees a fast response. High call volume can also amplify lock and network overhead.

I begin by asking whether the application needs every call. An application cache, batched request, or set-based query can remove work before tuning individual milliseconds. But caching introduces freshness rules, so first establish how frequently the data changes. The highest-frequency query is not necessarily the highest-cost one. Which application action generates those repeated calls?

Read the Plan Cache Carefully

sys.dm_exec_query_stats tracks statistics for cached query plans. execution_count is useful for a quick inventory, but the cache is not a durable history. Restart, memory pressure, recompilation, and plan eviction can remove entries. Multiple plans for a query can split its count. Record when the server started and treat the result as a current sample.

The following query extracts statement text from cached batches and ranks by execution count. It returns an instance-wide view subject to permissions. Keep the statement offsets because a batch can contain several statements.

SELECT TOP (20) qs.execution_count,
       qs.total_worker_time / 1000.0 AS cpu_ms,
       qs.total_logical_reads,
       SUBSTRING(st.text,
         (qs.statement_start_offset / 2) + 1,
         ((CASE qs.statement_end_offset
             WHEN -1 THEN DATALENGTH(st.text)
             ELSE qs.statement_end_offset END
           - qs.statement_start_offset) / 2) + 1)
           AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.execution_count DESC;

Use Query Store for History

When enabled, Query Store records runtime statistics in intervals and can retain a longer view than plan cache. It can aggregate counts across plans for a query. Retention and capture policy still matter: absence does not prove the query never ran. Query Store also works per database, so repeat the review across databases where appropriate.

This example sums counts over the last seven days and includes total CPU. It helps distinguish a frequent cheap query from a frequent expensive one. Check the database clock context and selected interval range for your environment.

SELECT TOP (20) q.query_id,
       SUM(rs.count_executions) AS executions,
       SUM(rs.avg_cpu_time * rs.count_executions) / 1000000.0
         AS total_cpu_seconds,
       MIN(CONVERT(nvarchar(4000), qt.query_sql_text)) AS query_text
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
  ON qt.query_text_id = q.query_text_id
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 executions DESC;

Normalize the Most Frequently Run Queries by Time

A million calls since an unknown restart is less useful than calls per hour during a known interval. Record collection timestamps and calculate rates. Compare peak and quiet periods. A query called every second during business hours has different operational impact from one called in a short nightly burst. Rate also helps detect an application loop or retry storm.

I chart count per interval when possible. A sudden rise after a deployment can indicate a missing cache, new polling behavior, or a client retrying errors. Check application logs and error rates before assuming the SQL text itself is defective. The database can show the repeated request, but the caller explains why it repeats.

Rank the Most Frequently Run Queries by Cost

Frequency alone does not prioritize tuning. Multiply executions by average CPU and logical reads, or use total counters directly. A very frequent query that hits one page can still be cheap. A moderately frequent query that scans a large table can dominate resources. Compare both, then examine the user-facing path.

Look for repeated identical parameters. If the same lookup runs thousands of times with unchanged data, caching or request consolidation is a strong candidate. If each call has a distinct key and strict freshness requirements, an efficient index and lower round-trip overhead can be more appropriate. The remedy follows the call pattern.

Inspect Parameter and Plan Variation

One query_id can have multiple plans, and one text shape can serve very different parameter values. Check whether the frequent query is slow only for a subset of customers or date ranges. Query Store plan and interval views can reveal variation. Averages can conceal a problematic plan used at peak time.

I test a representative small parameter, a large one, and the parameter that caused an incident if known. If a plan change appears after statistics updates, investigate estimates and plan choice before adding a hint. A high count magnifies a small regression, so even modest per-call changes deserve careful comparison.

Reduce Calls at the Right Layer

Application batching can replace many one-row requests with one set-based call. Caching can avoid repeated reads when invalidation rules are clear. A summary table can serve a stable aggregate. These approaches change data freshness and failure behavior, so include product requirements. Avoid hiding stale data behind a performance win.

SQL-side work still matters: a narrow index, typed parameters, and a compact result set can reduce each call. I prefer removing unnecessary calls first when the pattern is obvious. Saving two milliseconds on a query called a million times is valuable. Removing half a million calls can be more valuable.

Keep the Measurement Honest

Plan cache counters reset with plan lifetime, and Query Store retains only what its capture policy and storage allow. Label the evidence window. Exclude administrative polling if the goal is user workload, but document the exclusion. Permissions can also limit visibility, so a blank result is not proof that the instance is idle.

Finding the most frequently run queries is a practical way to see cumulative cost hiding inside fast requests. Rank the calls, add CPU and reads, trace the caller, and then reduce avoidable repetition. A thousand tiny taps can fill a bucket faster than one dramatic splash.

A high-frequency query can be the visible symptom of application retry logic. If a client retries every timeout immediately, traffic rises while the underlying cause remains. Compare query calls with request and error logs. Set bounded retries with backoff and idempotency, then measure whether database executions fall. That repair can help more than shaving a millisecond from the plan. Also separate health checks from business requests. A monitoring probe that runs every second deserves a cheap query, but it should not obscure the workload your users generate.

Related reading on this blog: List All Frequently Ran Stored Procedure From Server Cache and Finding Frequently Running Query and Elapsed Time: Notes from the Field #005.

Finding the most frequently run queries is not counting calls alone, it is connecting repetition to total cost.

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