Query rewrites that beat hint stacking

Start by simplifying predicates and joins before sprinkling OPTION clauses.

2026-08-04

/*
================================================================================
Purpose:        Identifies the top 20 statements consuming the most CPU, 
                physical/logical reads, or having the longest elapsed durations.
Provides:       Execution counts, total/avg CPU, logical reads, duration, 
                query text, and associated execution plan XML.
Importance:     Pinpoints the "heavy hitters" in the workload that offer the 
                highest return on investment for query tuning and optimization.
Interpretation: Focus on queries with high "Avg_Logical_Reads" or "Avg_CPU_ms". 
                 Correlate with wait statistics to understand the wait type.
Action: Copy the query text from the top-ranked queries and analyze the execution plan in SSMS. Look for: (1) missing index warnings (green text), (2) table scans on large tables, (3) implicit conversions, (4) key lookups. For queries with high Avg_CPU_ms, create recommended indexes and re-evaluate. For large batches, consider breaking into smaller operations or optimizing the worst-performing steps.
Criticality:    High
Author:        Ravi Sharma
================================================================================
*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

SELECT TOP (20)
    qs.execution_count AS [Execution_Count],
    qs.total_worker_time / 1000 AS [Total_CPU_ms],
    (qs.total_worker_time / qs.execution_count) / 1000 AS [Avg_CPU_ms],
    qs.total_logical_reads AS [Total_Logical_Reads],
    qs.total_logical_reads / qs.execution_count AS [Avg_Logical_Reads],
    qs.total_elapsed_time / 1000 AS [Total_Duration_ms],
    (qs.total_elapsed_time / qs.execution_count) / 1000 AS [Avg_Duration_ms],
    -- Extract the specific statement from the batch
    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 [Query_Text],
    qp.query_plan AS [Query_Plan_XML],
    CAST('Displays worst performing queries from SQL Server plan cache. ' + 
         'Threshold: Queries with Avg_Logical_Reads > 10,000 or Avg_CPU_ms > 500ms are candidates for optimization. ' +
         'Recommendation: Open Query_Plan_XML to locate clustered index scans or key lookups. Add missing indexes, reduce sorting operations, or verify parameter statistics.'
         AS VARCHAR(1000)) AS [Metric_Context]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY [Total_CPU_ms] DESC; -- Can change to total_logical_reads or total_elapsed_time depending on diagnostic focus

Review the full script and confirm the connected SQL Server instance before execution. The run instructions and output guide appear later in this article.

Query rewrites that beat hint stacking

Dummy guide: rewrite for readability and cardinality first.

Pull non-SARGable expressions off columns, remove redundant joins, and collapse OR trees that block index seeks. Only then consider hints or plan forcing.

If the rewrite still needs a hint, you probably still have a stats or indexing problem — treat the hint as temporary evidence, not the fix.

Why query rewrites deserves a method

The central principle is that a rewrite is successful only when it preserves results and improves the measured workload. A single metric, recommendation, or successful command is not enough. The decision has to connect an observed symptom to a plausible cause, a controlled change, and a result that can be verified under the workload that matters.

Evidence to collect

Use evidence from the same time window whenever possible:

result equivalence including duplicates and NULL
logical reads, CPU, and duration
estimate quality and plan shape
representative parameter and concurrency behavior

A practical workflow

Capture the original result and plan.
Identify one dominant source of work.
Make the smallest semantic change.
Compare correctness and performance under the same conditions.

Common mistakes

Avoid these shortcuts:

rewriting for style alone
accepting a lower estimated cost as proof
testing with an unrepresentative parameter

Verification and completion

Define completion before making the change. Confirm correctness first, then compare latency, throughput, reads, CPU, waits, storage behavior, or recovery time as appropriate for Query rewrites that beat hint stacking. Test negative and boundary cases, not only the path expected to succeed. Watch the system long enough to cover the relevant workload pattern.

Download and run the diagnostic

Top resource-consuming queries

Download top_resource_queries.sql

Before you run it

Review every statement and confirm the connection points to the intended SQL Server database server.
Run through a non-production or read-only diagnostic account first when possible.
Required access: VIEW SERVER STATE.
Save the output with the server name, database name, collection time, and workload window so another engineer can reproduce the analysis.

What it does

Identifies high-resource statements from the plan cache and provides the baseline totals needed to choose a rewrite candidate. The script is diagnostic and does not apply an automatic remediation. Result values still need workload context, a second supporting signal, and a before-and-after comparison.

How to read the output values

Use Execution_Count to normalize totals. Compare logical reads, CPU, and elapsed time before and after the rewrite under equivalent parameters. A missing row may mean the plan was evicted, not that the query stopped running.