SQL Performance Tuning: Indexes, Execution Plans, and Query Rewrites

The Performance Tuning Mindset

Don't guess. Measure. Every performance investigation follows the same pattern:

Find the slow query — DMVs, slow query log, Application Insights
Get the actual execution plan — not estimated, actual
Identify the bottleneck — table scan, key lookup, large sort, blocking
Form a hypothesis — one specific change
Measure before and after — logical reads, execution time, CPU

A query that takes 2 seconds with 200,000 logical reads becomes 5ms with 8 reads after the right index. That's the goal.

Finding Slow Queries

From SQL Server DMVs

SQL

-- Top 10 queries by average logical reads (most I/O per execution)
SELECT TOP 10
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_duration_ms,
    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) st
WHERE qs.execution_count > 10  -- exclude one-offs
ORDER BY avg_logical_reads DESC;

Currently Running Slow Queries

SQL

SELECT
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time / 1000 AS wait_seconds,
    r.cpu_time / 1000  AS cpu_seconds,
    r.logical_reads,
    SUBSTRING(st.text, (r.statement_start_offset / 2) + 1, 200) AS current_statement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50  -- exclude system processes
ORDER BY r.logical_reads DESC;

Reading Execution Plans

Get the actual execution plan in SSMS: Query menu → Include Actual Execution Plan, then run the query.

Key Operators to Spot

Table Scan / Clustered Index Scan — reads every row. On a large table, this is almost always wrong. Fix: add a selective index.

Index Seek — uses the B-tree to jump to matching rows. What you want.

Key Lookup — found rows in a non-clustered index, now fetching extra columns from the clustered index. Fix: add a covering INCLUDE.

Sort — explicit sort happening in memory/tempdb. Fix: provide data in the required order via an index.

Hash Match — join using a hash table. Memory-intensive. Often unavoidable for large inputs, but can indicate missing indexes.

Nested Loops — for each row in outer input, scan the inner. Fast when outer set is small; catastrophic when it's large.

Reading the Cost

Costs are percentages — the width of arrows represents row estimates. A thick arrow going into an expensive operator is your bottleneck.

Always check estimated vs actual row counts. If estimated = 1 but actual = 50,000, statistics are stale or a parameter sniffing issue exists.

SQL

-- Update statistics for a table
UPDATE STATISTICS Orders;

-- Or for a specific index
UPDATE STATISTICS Orders IX_Orders_CustomerId;

-- Or all tables in the database
EXEC sp_updatestats;

Index Strategy

The Index Tuning Process

Look at the WHERE clause — those columns need to be in the index key
Look at ORDER BY / GROUP BY — if those columns are the index key, the sort is free
Look at SELECT columns — add them to INCLUDE to cover the query
Check selectivity — an index on a column with 2 distinct values (IsActive: true/false) helps little

Checking Index Usage

SQL

-- Indexes ordered by how useful they are (seeks vs scans vs lookups)
SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates,
    us.last_user_seek,
    -- High updates vs seeks = overhead-only index, consider dropping
    us.user_updates - (us.user_seeks + us.user_scans + us.user_lookups) AS update_overhead
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
    ON i.object_id = us.object_id AND i.index_id = us.index_id
    AND us.database_id = DB_ID()
WHERE OBJECT_NAME(i.object_id) NOT LIKE 'sys%'
ORDER BY us.user_seeks DESC;

Missing Indexes

SQL

-- SQL Server's suggestions for missing indexes
SELECT TOP 20
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS priority_score,
    migs.user_seeks,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY priority_score DESC;

These are suggestions — always review them. SQL Server doesn't know about your write volume or existing indexes.

Covering Indexes

The single most impactful index optimisation for read-heavy queries.

Before (Key Lookup):

SQL

-- Query
SELECT Name, Email, Country FROM Customers WHERE Country = 'UK';

-- Index exists on Country only
CREATE INDEX IX_Customers_Country ON Customers (Country);

-- Execution plan:
-- Index Seek (Country) → Key Lookup (Name, Email) ← expensive for many rows

After (Covering Index):

SQL

-- Add Name and Email to INCLUDE — they're in SELECT but not needed for seek
CREATE INDEX IX_Customers_Country ON Customers (Country) INCLUDE (Name, Email);

-- Execution plan:
-- Index Seek only — no key lookup needed
-- Logical reads: 200 → 3

Rule: Key in index = columns you filter/order/join on. INCLUDE = columns you only SELECT.

Query Rewrite Patterns

1. Replace Functions on Columns

SQL

-- SLOW — function prevents index use, full scan
SELECT * FROM Orders WHERE YEAR(CreatedAt) = 2025;

-- FAST — range on the column, index seek
SELECT * FROM Orders
WHERE CreatedAt >= '2025-01-01' AND CreatedAt < '2026-01-01';

2. Replace `OR` with `UNION ALL`

SQL

-- SLOW — OR can prevent index use, often results in a scan
SELECT * FROM Customers WHERE City = 'London' OR City = 'Manchester';

-- FAST — each branch can use the index independently
SELECT * FROM Customers WHERE City = 'London'
UNION ALL
SELECT * FROM Customers WHERE City = 'Manchester';

3. Avoid `SELECT *` in JOINs

SQL

-- Fetches all columns including large text/binary columns
SELECT * FROM Orders o JOIN Customers c ON o.CustomerId = c.Id;

-- Only what's needed
SELECT o.Id, o.Amount, c.Name, c.Email
FROM Orders o JOIN Customers c ON o.CustomerId = c.Id;

4. Push Filters Down

SQL

-- SLOW — JOIN happens first, then filter
SELECT o.Id, c.Name
FROM Orders o
JOIN Customers c ON o.CustomerId = c.Id
WHERE o.CreatedAt > '2025-01-01';

-- FAST — filter Orders before JOIN (fewer rows to join)
SELECT o.Id, c.Name
FROM (SELECT Id, CustomerId FROM Orders WHERE CreatedAt > '2025-01-01') o
JOIN Customers c ON o.CustomerId = c.Id;
-- Or equivalently, trust the query optimiser — it usually does this automatically
-- Use the subquery form only when the optimiser doesn't

5. Avoid `NOT IN` with Subqueries Containing NULLs

SQL

-- DANGEROUS — if subquery returns any NULL, NOT IN returns no rows
SELECT * FROM Customers
WHERE Id NOT IN (SELECT CustomerId FROM Orders);
-- If any order has CustomerId = NULL, result is empty!

-- SAFE — NOT EXISTS handles NULLs correctly
SELECT * FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id);

6. Batch Large Updates/Deletes

SQL

-- SLOW and blocking — deletes 5 million rows in one transaction
DELETE FROM AuditLog WHERE CreatedAt < '2024-01-01';

-- FAST — batch in small chunks, releases locks between batches
DECLARE @BatchSize INT = 10000;
DECLARE @Rows INT = 1;

WHILE @Rows > 0
BEGIN
    DELETE TOP (@BatchSize) FROM AuditLog
    WHERE CreatedAt < '2024-01-01';

    SET @Rows = @@ROWCOUNT;
    WAITFOR DELAY '00:00:00.100'; -- brief pause to allow other queries through
END

Temp Tables vs CTEs vs Subqueries

Understanding when to use each has a significant performance impact.

SQL

-- Subquery — inline, may be re-evaluated multiple times
SELECT * FROM (SELECT Id, SUM(Amount) FROM Orders GROUP BY Id) s WHERE s.Amount > 1000;

-- CTE — syntactic sugar for subquery, same execution (evaluated once in most cases)
WITH Summary AS (SELECT Id, SUM(Amount) AS Total FROM Orders GROUP BY Id)
SELECT * FROM Summary WHERE Total > 1000;

-- Temp table — materialised, has statistics, can be indexed
SELECT Id, SUM(Amount) AS Total INTO #Summary FROM Orders GROUP BY Id;
CREATE INDEX IX_Summary_Total ON #Summary (Total);
SELECT * FROM #Summary WHERE Total > 1000;
-- Use when: CTE is referenced multiple times, or needs an index

Statistics Deep Dive

SQL Server estimates row counts using statistics histograms. Bad estimates → bad plans.

SQL

-- View statistics for an index
DBCC SHOW_STATISTICS ('Orders', 'IX_Orders_CustomerId');

-- Shows:
-- RANGE_ROWS: estimated rows between histogram steps
-- EQ_ROWS: estimated rows equal to the step value
-- AVG_RANGE_ROWS: average rows between steps

-- Check when statistics were last updated
SELECT
    OBJECT_NAME(object_id) AS TableName,
    name AS StatName,
    last_updated = STATS_DATE(object_id, stats_id),
    rows,
    rows_sampled,
    modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id)
WHERE OBJECT_NAME(object_id) = 'Orders';

Blocking and Locking

SQL

-- Find blocking chains
SELECT
    blocking_session_id,
    session_id,
    wait_type,
    wait_time / 1000 AS wait_seconds,
    SUBSTRING(st.text, (r.statement_start_offset / 2) + 1, 100) AS blocked_query
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id > 0;

Common causes of blocking:

Long-running transactions (commit or roll back ASAP)
Missing indexes (table scans hold locks longer)
Using SERIALIZABLE isolation when not needed
Application code that opens a transaction, then waits for user input

Fix: Enable READ_COMMITTED_SNAPSHOT at the database level for row-level versioning — readers don't block writers.

Practical Tuning Workflow

SQL

-- Step 1: Find the query
-- Step 2: Capture logical reads BEFORE
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT Name, Email FROM Customers WHERE Country = 'UK' AND IsActive = 1;

-- Output: Table 'Customers'. Scan count 1, logical reads 1842
-- SQL Server Execution Times: CPU time = 47ms, elapsed time = 89ms

-- Step 3: Add index
CREATE INDEX IX_Customers_CountryActive ON Customers (Country, IsActive)
INCLUDE (Name, Email);

-- Step 4: Capture AFTER
SELECT Name, Email FROM Customers WHERE Country = 'UK' AND IsActive = 1;
-- Output: Table 'Customers'. Scan count 1, logical reads 4
-- SQL Server Execution Times: CPU time = 0ms, elapsed time = 1ms

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Logical reads dropped from 1842 to 4. That's the measurement that proves the fix worked.

Interview Questions

Q: How do you find the most expensive queries in a SQL Server database? Query sys.dm_exec_query_stats joined with sys.dm_exec_sql_text. Sort by total_logical_reads / execution_count for average I/O per call, or total_elapsed_time for total wall clock time. Filter by execution_count > n to exclude one-off queries.

Q: What is the difference between an index seek and an index scan? A seek uses the B-tree to jump directly to matching rows — O(log n). A scan reads every row in the index or table — O(n). A seek is almost always what you want. Scans happen when there's no suitable index, the predicate is non-selective, or there's a function on the indexed column.

Q: What is a covering index and why does it eliminate key lookups? A covering index contains all columns a query needs — in the index key or INCLUDE clause. SQL Server satisfies the entire query from the index without going back to the clustered index for additional columns. Key lookups are eliminated, reducing logical reads dramatically.

Q: Why does putting a function in a WHERE clause slow a query? SQL Server can't use an index to seek when the comparison involves a computed value — it must evaluate the function for every row, forcing a scan. Rewrite to a range or sargable predicate on the raw column.

Q: How do you tune a query that's slow for one parameter value but fast for others? This is parameter sniffing — the plan was compiled for a different parameter value. Options: OPTION (RECOMPILE) to recompile per execution, OPTION (OPTIMIZE FOR UNKNOWN) to use average statistics, clearing the plan cache for that query, or creating a plan guide.

SQL Performance Tuning: Indexes, Execution Plans, and Query Rewrites

The Performance Tuning Mindset

Finding Slow Queries

From SQL Server DMVs

Currently Running Slow Queries

Reading Execution Plans

Key Operators to Spot

Reading the Cost

Index Strategy

The Index Tuning Process

Checking Index Usage

Missing Indexes

Covering Indexes

Query Rewrite Patterns

1. Replace Functions on Columns

2. Replace `OR` with `UNION ALL`

3. Avoid `SELECT *` in JOINs

4. Push Filters Down

5. Avoid `NOT IN` with Subqueries Containing NULLs

6. Batch Large Updates/Deletes

Temp Tables vs CTEs vs Subqueries

Statistics Deep Dive

Blocking and Locking

Practical Tuning Workflow

Interview Questions

Enjoyed this article?

Leave a comment

The Performance Tuning Mindset

Finding Slow Queries

From SQL Server DMVs

Currently Running Slow Queries

Reading Execution Plans

Key Operators to Spot

Reading the Cost

Index Strategy

The Index Tuning Process

Checking Index Usage

Missing Indexes

Covering Indexes

Query Rewrite Patterns

1. Replace Functions on Columns

2. Replace OR with UNION ALL

3. Avoid SELECT * in JOINs

4. Push Filters Down

5. Avoid NOT IN with Subqueries Containing NULLs

6. Batch Large Updates/Deletes

Temp Tables vs CTEs vs Subqueries

Statistics Deep Dive

Blocking and Locking

Practical Tuning Workflow

Interview Questions

Enjoyed this article?

Leave a comment

2. Replace `OR` with `UNION ALL`

3. Avoid `SELECT *` in JOINs

5. Avoid `NOT IN` with Subqueries Containing NULLs