Query Optimization

Query Optimization

Comprehensive guide to T-SQL query optimization for SQL Server and Azure SQL Database. Optimize from verified evidence: schema, data types, indexes, row counts, partitioning, execution plans, and allowed change types.

Mandatory Workflow

1. Schema-First Validation

Before recommending rewrites, indexes, or hints, verify or mark unknown:

• SQL Server version, edition, compatibility level, and Azure SQL tier if applicable.
• Full query/procedure text, parameter values, frequency, and runtime target.
• DDL for tables, views, temp tables, table variables, and TVFs.
• Data types for join, filter, grouping, ordering, and temp-table columns, including length, precision, scale, collation, and nullability.
• Existing indexes, constraints, statistics age, partition function/scheme, and row counts.
• Whether objects are local or linked-server/remote sources.
• Allowed change types: query rewrite, new index, huge-table index change, stats update, staging table, computed column, or no code change.

Use ../_shared/optimization-intake.md and ../_shared/assumption-tracker.md. If key facts are missing, provide conditional guidance plus diagnostics instead of final prescriptions.

2. Identify the Root Bottleneck

Use actual execution plans and STATISTICS IO, TIME when possible. Rank findings by measured impact, not just estimated plan percentage:

• High logical reads or row reads.
• Bad estimate vs actual row gaps.
• Scans caused by non-SARGable predicates or missing access paths.
• Key lookups multiplied by many executions.
• Sort/hash/window spills from poor estimates or missing order.
• Remote queries that fail to push predicates or joins to the linked server.

For detailed .sqlplan inspection, load tsql-master:execution-plan-analysis.

3. Fix SARGability and Type Mismatches

SARGable predicates can use ordered index access. Avoid functions or conversions on indexed columns.

Check actual data types. A syntactically SARGable predicate can still scan if a parameter, temp column, or join key has the wrong type or collation.

4. Prove Join Changes

Never remove or replace joins only because selected columns come from one table. Prove:

• Does the join filter rows? Compare base count vs joined count and check trusted foreign keys.
• Does the join multiply rows? Check uniqueness on the joined key and duplicates in the referenced table.
• Can it be replaced with `EXISTS`? Use a semi-join when only existence is needed and row multiplication must be avoided.
• Are outer joins preserved? Predicates in WHERE can accidentally convert LEFT JOIN to inner join.

Move join-removal experiments into a proof harness and validate equivalence with EXCEPT in both directions. See references/rewrite-proof-harnesses.md.

5. Check Temp Tables and Staging Types

Temp tables are often the right optimization tool, but bad types can create hidden conversions.

Before using or recommending a temp table:

• Compare staged column types against source metadata.
• Match string length and collation for join/filter columns.
• Match numeric precision/scale and date/time precision.
• Add appropriate clustered or nonclustered indexes after load when row counts justify them.
• Update temp-table statistics when phased optimization depends on accurate cardinality.

Flag mismatches as first-order findings because they can invalidate plan analysis and index recommendations. Use the checker in references/rewrite-proof-harnesses.md.

6. Select the Rewrite Template

Choose the least invasive rewrite that addresses the verified bottleneck:

Do not stage huge unfiltered tables or aggregate early unless the row reduction and semantic equivalence are proven.

Parameter Sensitivity

Parameter sniffing occurs when a plan compiled for one value is reused for a very different value. Confirm skew and compile/runtime values before applying fixes.

Execution Plan Checks

Watch these operators and warnings:

Statistics and Cardinality

Use statistics work when evidence points to stale or insufficient estimates:

DBCC SHOW_STATISTICS('dbo.TableName', 'IndexOrStatsName');
UPDATE STATISTICS dbo.TableName IndexOrStatsName WITH FULLSCAN;

For large partitioned tables, evaluate incremental statistics and filtered stats. Do not run broad fullscan updates in production without maintenance-window and blocking considerations.

Output Format

Respond with:

1. Intake status: verified, unverified, disproved, needs diagnostic. 2. Bottleneck evidence: plan nodes, reads, rows, estimates, warnings. 3. Recommendation path: rewrite, index/stat change, or diagnostic, separated by allowed change type. 4. Proof harness: result equivalence and before/after performance metrics. 5. Risks: parameter sensitivity, write overhead, blocking, partition safety, remote pushdown.

References

• ../_shared/optimization-intake.md - mandatory intake checklist.
• ../_shared/assumption-tracker.md - assumption status protocol.
• references/rewrite-proof-harnesses.md - join proof, temp type checker, and rewrite templates.
• references/dmv-diagnostic-queries.md - DMV queries for performance analysis.

DMV Diagnostic Queries

Essential Dynamic Management View queries for SQL Server performance troubleshooting.

Wait Statistics

Current Wait Statistics

-- Top waits since server restart
SELECT TOP 20
    wait_type,
    wait_time_ms / 1000.0 AS wait_time_sec,
    signal_wait_time_ms / 1000.0 AS signal_wait_sec,
    waiting_tasks_count,
    wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
    'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
    'WAITFOR', 'BROKER_RECEIVE_WAITFOR', 'CLR_AUTO_EVENT',
    'CLR_MANUAL_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
    'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH'
)
AND wait_time_ms > 0
ORDER BY wait_time_ms DESC

Session Wait Statistics (SQL 2016+)

-- Waits for current session
SELECT * FROM sys.dm_exec_session_wait_stats
WHERE session_id = @@SPID
ORDER BY wait_time_ms DESC

Currently Waiting Tasks

SELECT
    wt.session_id,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.blocking_session_id,
    st.text AS query_text
FROM sys.dm_os_waiting_tasks wt
LEFT JOIN sys.dm_exec_requests r ON wt.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE wt.session_id > 50
ORDER BY wt.wait_duration_ms DESC

Query Performance

Top Resource-Consuming Queries

-- Top queries by CPU
SELECT TOP 20
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
    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
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC

Top Queries by Logical Reads (I/O)

SELECT TOP 20
    qs.total_logical_reads,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_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 query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_logical_reads DESC

Currently Running Queries

SELECT
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    r.reads,
    r.writes,
    r.wait_type,
    r.blocking_session_id,
    st.text AS query_text,
    qp.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE r.session_id > 50
  AND r.session_id <> @@SPID
ORDER BY r.cpu_time DESC

Index Analysis

Missing Indexes

SELECT
    CONVERT(DECIMAL(18,2), migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
    'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id) + '_'
        + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''), ', ', '_'), '[', ''), ']', '')
        + '] ON ' + mid.statement
        + ' (' + ISNULL(mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
        + ISNULL(mid.inequality_columns, '')
        + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC

Index Usage Statistics

SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id
    AND i.index_id = ius.index_id
    AND ius.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(i.object_id), i.index_id

Unused Indexes

SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    ius.user_updates AS writes,
    ius.user_seeks + ius.user_scans + ius.user_lookups AS reads
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id
    AND i.index_id = ius.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.index_id > 0
  AND (ius.user_seeks + ius.user_scans + ius.user_lookups) = 0
  AND ius.user_updates > 0
ORDER BY ius.user_updates DESC

Index Fragmentation

SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
  AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC

Memory and Buffer Pool

Buffer Pool Usage by Object

SELECT TOP 20
    OBJECT_NAME(p.object_id) AS TableName,
    COUNT(*) * 8 / 1024 AS buffer_mb,
    COUNT(*) AS pages
FROM sys.dm_os_buffer_descriptors bd
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.hobt_id
WHERE bd.database_id = DB_ID()
GROUP BY p.object_id
ORDER BY COUNT(*) DESC

Memory Grants

SELECT
    session_id,
    request_id,
    scheduler_id,
    dop,
    requested_memory_kb,
    granted_memory_kb,
    used_memory_kb,
    query_cost,
    timeout_sec,
    wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC

Blocking and Locking

Current Blocking

SELECT
    blocked.session_id AS blocked_session,
    blocked.blocking_session_id AS blocking_session,
    blocked.wait_type,
    blocked.wait_time / 1000.0 AS wait_sec,
    blocked_text.text AS blocked_query,
    blocking_text.text AS blocking_query
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) AS blocked_text
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) AS blocking_text
WHERE blocked.blocking_session_id > 0

Lock Waits

SELECT
    tl.request_session_id AS session_id,
    OBJECT_NAME(p.object_id) AS table_name,
    tl.resource_type,
    tl.request_mode,
    tl.request_status
FROM sys.dm_tran_locks tl
JOIN sys.partitions p ON tl.resource_associated_entity_id = p.hobt_id
WHERE tl.resource_database_id = DB_ID()
  AND tl.request_status = 'WAIT'

Query Store (SQL 2016+)

Top Queries from Query Store

SELECT TOP 20
    qt.query_sql_text,
    q.query_id,
    rs.count_executions,
    rs.avg_duration / 1000 AS avg_duration_ms,
    rs.avg_cpu_time / 1000 AS avg_cpu_ms,
    rs.avg_logical_io_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_duration DESC

Regressed Queries

SELECT
    qt.query_sql_text,
    q.query_id,
    p.plan_id,
    rs.avg_duration / 1000 AS recent_avg_ms,
    hist.avg_duration / 1000 AS baseline_avg_ms,
    (rs.avg_duration - hist.avg_duration) / hist.avg_duration * 100 AS regression_pct
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
JOIN (
    SELECT plan_id, AVG(avg_duration) AS avg_duration
    FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
    WHERE rsi.start_time >= DATEADD(day, -30, GETUTCDATE())
      AND rsi.start_time < DATEADD(day, -1, GETUTCDATE())
    GROUP BY plan_id
) hist ON p.plan_id = hist.plan_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
  AND rs.avg_duration > hist.avg_duration * 1.5
ORDER BY regression_pct DESC

Azure SQL Database Specific

Resource Usage

SELECT
    end_time,
    avg_cpu_percent,
    avg_data_io_percent,
    avg_log_write_percent,
    avg_memory_usage_percent,
    max_worker_percent,
    max_session_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC

Query Performance Insight

SELECT TOP 20
    qt.query_sql_text,
    rs.avg_cpu_time / 1000 AS avg_cpu_ms,
    rs.avg_logical_io_reads,
    rs.avg_duration / 1000 AS avg_duration_ms,
    rs.count_executions
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -1, GETUTCDATE())
ORDER BY rs.avg_cpu_time * rs.count_executions DESC

Rewrite Proof Harnesses

Use these harnesses to prove that a T-SQL rewrite preserves semantics and improves plan shape. Adapt object and column names before running. Prefer COUNT_BIG for large tables.

Join Removal Proof

Goal: determine whether a join filters rows, multiplies rows, or can be replaced by EXISTS.

1. Does the join filter rows?

SELECT COUNT_BIG(*) AS BaseRows
FROM dbo.FactTable AS f;

SELECT COUNT_BIG(*) AS JoinedRows
FROM dbo.FactTable AS f
JOIN dbo.Dimension AS d
  ON d.DimensionID = f.DimensionID;

If counts differ, the join filters or multiplies. Continue with duplicate checks.

2. Does the join multiply rows?

SELECT f.DimensionID, COUNT_BIG(*) AS Matches
FROM dbo.FactTable AS f
JOIN dbo.Dimension AS d
  ON d.DimensionID = f.DimensionID
GROUP BY f.DimensionID
HAVING COUNT_BIG(*) > COUNT_BIG(DISTINCT f.PrimaryKeyColumn);

A simpler dimension-key duplicate check is often enough:

SELECT d.DimensionID, COUNT_BIG(*) AS DuplicateRows
FROM dbo.Dimension AS d
GROUP BY d.DimensionID
HAVING COUNT_BIG(*) > 1;

3. Is there a trusted constraint proof?

SELECT
    fk.name,
    fk.is_disabled,
    fk.is_not_trusted
FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = OBJECT_ID(N'dbo.FactTable')
  AND fk.referenced_object_id = OBJECT_ID(N'dbo.Dimension');

A trusted FK plus unique referenced key can prove the join does not filter when the FK column is non-null and no dimension predicates are applied.

4. Replace join with EXISTS when only existence is needed

-- Before: may multiply rows if Dimension is not unique on DimensionID
SELECT f.PrimaryKeyColumn, f.Amount
FROM dbo.FactTable AS f
JOIN dbo.Dimension AS d
  ON d.DimensionID = f.DimensionID
WHERE d.Status = 'Active';

-- Safer semi-join shape
SELECT f.PrimaryKeyColumn, f.Amount
FROM dbo.FactTable AS f
WHERE EXISTS
(
    SELECT 1
    FROM dbo.Dimension AS d
    WHERE d.DimensionID = f.DimensionID
      AND d.Status = 'Active'
);

Validate with EXCEPT in both directions:

WITH before_query AS
(
    SELECT f.PrimaryKeyColumn, f.Amount
    FROM dbo.FactTable AS f
    JOIN dbo.Dimension AS d
      ON d.DimensionID = f.DimensionID
    WHERE d.Status = 'Active'
),
after_query AS
(
    SELECT f.PrimaryKeyColumn, f.Amount
    FROM dbo.FactTable AS f
    WHERE EXISTS
    (
        SELECT 1
        FROM dbo.Dimension AS d
        WHERE d.DimensionID = f.DimensionID
          AND d.Status = 'Active'
    )
)
SELECT 'before_minus_after' AS DiffType, * FROM before_query
EXCEPT
SELECT 'before_minus_after', * FROM after_query
UNION ALL
SELECT 'after_minus_before', * FROM after_query
EXCEPT
SELECT 'after_minus_before', * FROM before_query;

Temp Table Type Checker

Goal: prove temp or staging columns match source columns used in joins and predicates.

SELECT
    temp_col.name AS TempColumn,
    temp_type.name AS TempType,
    temp_col.max_length AS TempMaxLength,
    temp_col.precision AS TempPrecision,
    temp_col.scale AS TempScale,
    temp_col.collation_name AS TempCollation,
    source_col.name AS SourceColumn,
    source_type.name AS SourceType,
    source_col.max_length AS SourceMaxLength,
    source_col.precision AS SourcePrecision,
    source_col.scale AS SourceScale,
    source_col.collation_name AS SourceCollation
FROM tempdb.sys.columns AS temp_col
JOIN tempdb.sys.types AS temp_type
  ON temp_type.user_type_id = temp_col.user_type_id
JOIN sys.columns AS source_col
  ON source_col.object_id = OBJECT_ID(N'dbo.SourceTable')
 AND source_col.name = temp_col.name
JOIN sys.types AS source_type
  ON source_type.user_type_id = source_col.user_type_id
WHERE temp_col.object_id = OBJECT_ID(N'tempdb..#StageKeys');

Flag mismatches in data type, string length, numeric precision/scale, collation, and nullability. A temp table that stores an int key as nvarchar(50) can create CONVERT_IMPLICIT joins and scans.

Rewrite Template Selection

Stage selective keys first

Use when a small, selective predicate can be isolated before joining huge tables.

SELECT DISTINCT s.KeyColumn
INTO #Keys
FROM dbo.SelectiveSource AS s
WHERE s.FilterDate >= @StartDate
  AND s.FilterDate < @EndDate;

CREATE UNIQUE CLUSTERED INDEX CX_Keys ON #Keys(KeyColumn);

SELECT b.*
FROM #Keys AS k
JOIN dbo.BigTable AS b
  ON b.KeyColumn = k.KeyColumn;

Reduce before joining huge tables

Use when grouping can shrink rows early without changing semantics.

WITH reduced AS
(
    SELECT DetailKey, SUM(Amount) AS Amount
    FROM dbo.BigDetail
    WHERE TranDate >= @StartDate
      AND TranDate < @EndDate
    GROUP BY DetailKey
)
SELECT h.HeaderID, r.Amount
FROM reduced AS r
JOIN dbo.Header AS h
  ON h.DetailKey = r.DetailKey;

Aggregate early only when grouping keys preserve semantics

Before aggregating early, prove that columns needed later are functionally dependent on grouping keys or are aggregated intentionally.

SELECT GroupKey, COUNT_BIG(DISTINCT LaterColumn) AS DistinctLaterValues
FROM dbo.BigDetail
GROUP BY GroupKey
HAVING COUNT_BIG(DISTINCT LaterColumn) > 1;

Avoid unsafe partition predicates

Prefer direct range predicates on the partitioning column.

-- Unsafe if OrderDateTime is the partitioning column: function wraps the column
WHERE CONVERT(date, OrderDateTime) = @OrderDate;

-- Safer range predicate
WHERE OrderDateTime >= @OrderDate
  AND OrderDateTime < DATEADD(day, 1, @OrderDate);

If filtering on a related date column instead of the partition key, require a trusted constraint or proof that the columns are equivalent for the target rows.

Before/After Measurement Template

SET STATISTICS IO, TIME ON;

-- Run baseline query with representative parameters.
-- Capture actual execution plan.

-- Run candidate rewrite with same parameters.
-- Capture actual execution plan.

SET STATISTICS IO, TIME OFF;

Compare logical reads, CPU time, elapsed time, spills, memory grant, actual vs estimated rows, rows read vs rows returned, and result-set equivalence.