SQL Server Query Optimization: Execution Plans and Performance Tuning

SQL Server Query Optimization: Execution Plans and Performance Tuning

Introduction

Query optimization is critical for SQL Server performance. This guide covers reading execution plans, managing statistics, using query hints strategically, implementing index recommendations, diagnosing parameter sniffing, and applying query rewrite techniques to maximize database performance.

Execution Plans

Reading Graphical Execution Plans

-- Enable actual execution plan in SSMS (Ctrl+M)
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT 
    c.CustomerID,
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    o.TotalAmount
FROM dbo.Customers c
INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= '2025-01-01'
ORDER BY o.TotalAmount DESC;

Key Execution Plan Operators:

Table Scan - Reads entire table (slow for large tables)

• Cost: High
• Fix: Add appropriate index

Index Seek - Uses index to find specific rows (fast)

• Cost: Low
• Ideal operation

Index Scan - Reads entire index (better than table scan)

• Cost: Medium
• Consider filtered index or covering index

Key Lookup - Looks up additional columns not in index

• Cost: Medium-High
• Fix: Create covering index

Nested Loops - Joins by iterating outer to inner

• Cost: Low for small datasets
• Best for: <100 rows outer table

Hash Match - Builds hash table for join

• Cost: Medium-High (memory intensive)
• Best for: Large datasets, no indexes

Merge Join - Merges two sorted inputs

• Cost: Low (if already sorted)
• Best for: Both inputs sorted/indexed

Estimated vs. Actual Plans

-- Estimated plan (Ctrl+L) - doesn't execute query
-- Shows estimates based on statistics

-- Actual plan (Ctrl+M) - executes query
-- Shows actual row counts and execution metrics

-- Compare estimated vs actual rows
SELECT 
    p.ProductID,
    p.ProductName,
    COUNT(od.OrderDetailID) AS OrderCount
FROM dbo.Products p
LEFT JOIN dbo.OrderDetails od ON p.ProductID = od.ProductID
GROUP BY p.ProductID, p.ProductName
HAVING COUNT(od.OrderDetailID) > 100;

-- If estimated rows differ significantly from actual rows:
-- Statistics may be outdated → UPDATE STATISTICS

Expensive Operators

-- Query to find expensive operators in plan cache
SELECT TOP 20
    qs.execution_count,
    qs.total_worker_time / 1000 AS TotalCPUTimeMs,
    qs.total_elapsed_time / 1000 AS TotalDurationMs,
    qs.total_logical_reads,
    qs.total_logical_writes,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

Missing Index Warnings

-- Execution plans show missing index recommendations
-- Green text: "Missing Index (Impact: XX%)"

-- Query missing index DMV
SELECT 
    CONVERT(DECIMAL(18,2), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS ImprovementScore,
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(mid.statement, '[', ''), ']', ''), '.', '_') + 
    ' ON ' + mid.statement + 
    ' (' + ISNULL(mid.equality_columns, '') + 
    CASE WHEN mid.inequality_columns IS NOT NULL THEN ', ' + mid.inequality_columns ELSE '' END + ')' +
    CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END AS CreateIndexStatement
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER 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 ImprovementScore DESC;

Statistics

Auto-Create and Auto-Update Statistics

-- Check database statistics settings
SELECT 
    name,
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases
WHERE name = 'AdventureWorks';

-- Enable auto-create/update (recommended)
ALTER DATABASE AdventureWorks SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS_ASYNC ON;  -- Async for large tables

Manual Statistics Update

-- Update statistics for specific table
UPDATE STATISTICS dbo.Orders;

-- Update statistics with full scan (more accurate)
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Update specific index statistics
UPDATE STATISTICS dbo.Orders IX_Orders_CustomerID WITH FULLSCAN;

-- Update all statistics in database
EXEC sp_updatestats;

Viewing Statistics

-- View statistics histogram
DBCC SHOW_STATISTICS('dbo.Orders', 'IX_Orders_OrderDate');

-- Results include:
-- 1. Header: Rows, rows sampled, last update date
-- 2. Density vector: Column correlations
-- 3. Histogram: Distribution of values (up to 200 steps)

-- Script to check stale statistics
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter,
    sp.modification_counter * 100.0 / sp.rows AS PercentChanged
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECT_NAME(s.object_id) LIKE 'Orders%'
    AND sp.modification_counter > 0
ORDER BY sp.modification_counter DESC;

Filtered Statistics

-- Create filtered statistics for subset of data
CREATE STATISTICS ST_Orders_Recent
ON dbo.Orders(OrderDate)
WHERE OrderDate >= '2025-01-01';

-- Useful for partitioned or skewed data
CREATE STATISTICS ST_Products_Active
ON dbo.Products(Price)
WHERE IsActive = 1;

Query Hints

Table Hints

-- NOLOCK (READ UNCOMMITTED) - allows dirty reads
SELECT * FROM dbo.Orders WITH (NOLOCK);

-- ROWLOCK - forces row-level locks
UPDATE dbo.Products WITH (ROWLOCK)
SET Price = Price * 1.1
WHERE ProductID = 100;

-- PAGLOCK - forces page-level locks
UPDATE dbo.Orders WITH (PAGLOCK)
SET Status = 'Processed'
WHERE OrderDate < '2024-01-01';

-- TABLOCK - forces table-level lock
DELETE FROM dbo.OrdersArchive WITH (TABLOCK)
WHERE OrderDate < '2020-01-01';

-- UPDLOCK - prevents deadlocks in read-then-update scenarios
BEGIN TRANSACTION;
SELECT * FROM dbo.Inventory WITH (UPDLOCK, ROWLOCK)
WHERE ProductID = 100;

UPDATE dbo.Inventory
SET Quantity = Quantity - 5
WHERE ProductID = 100;
COMMIT;

Join Hints

-- Force nested loops join
SELECT 
    c.CustomerID,
    o.OrderID
FROM dbo.Customers c
INNER LOOP JOIN dbo.Orders o ON c.CustomerID = o.CustomerID;

-- Force hash join
SELECT 
    p.ProductID,
    od.OrderID
FROM dbo.Products p
INNER HASH JOIN dbo.OrderDetails od ON p.ProductID = od.ProductID;

-- Force merge join
SELECT 
    c.CategoryID,
    p.ProductID
FROM dbo.Categories c
INNER MERGE JOIN dbo.Products p ON c.CategoryID = p.CategoryID;

Query Hints

-- FORCESEEK - forces index seek instead of scan
SELECT 
    CustomerID,
    CustomerName
FROM dbo.Customers WITH (FORCESEEK)
WHERE CustomerID > 1000;

-- FORCESCAN - forces index/table scan
SELECT 
    OrderID,
    TotalAmount
FROM dbo.Orders WITH (FORCESCAN)
WHERE OrderDate >= '2025-01-01';

-- OPTIMIZE FOR - optimizes for specific parameter value
SELECT 
    OrderID,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID = 1001));

-- OPTIMIZE FOR UNKNOWN - uses average statistics
SELECT 
    OrderID,
    TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));

-- RECOMPILE - generates new plan each execution
SELECT 
    ProductID,
    ProductName,
    Price
FROM dbo.Products
WHERE CategoryID = @CategoryID
OPTION (RECOMPILE);

-- MAXDOP - controls parallelism
SELECT 
    COUNT(*)
FROM dbo.Orders
OPTION (MAXDOP 4);  -- Use maximum 4 cores

Index Recommendations

Missing Index DMVs

-- Comprehensive missing index analysis
SELECT TOP 25
    CAST(ROUND(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 0) AS INT) AS ImprovementMeasure,
    DB_NAME(mid.database_id) AS DatabaseName,
    OBJECT_NAME(mid.object_id, mid.database_id) AS TableName,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.last_user_scan,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    'CREATE NONCLUSTERED INDEX IX_' + 
        OBJECT_NAME(mid.object_id, mid.database_id) + '_' +
        REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') +
        CASE WHEN mid.inequality_columns IS NOT NULL THEN '_' + REPLACE(REPLACE(REPLACE(mid.inequality_columns, ', ', '_'), '[', ''), ']', '') ELSE '' END +
        ' ON ' + mid.statement +
        ' (' + ISNULL(mid.equality_columns, '') +
        CASE WHEN mid.inequality_columns IS NOT NULL THEN ', ' + mid.inequality_columns ELSE '' END + ')' +
        CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END +
        ' WITH (ONLINE = ON, FILLFACTOR = 90);' AS CreateIndexStatement
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
    AND migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) > 100
ORDER BY ImprovementMeasure DESC;

Index Usage Statistics

-- Find unused indexes (consider dropping)
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates,
    'DROP INDEX ' + i.name + ' ON ' + OBJECT_SCHEMA_NAME(s.object_id) + '.' + OBJECT_NAME(s.object_id) + ';' AS DropStatement
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND i.type_desc <> 'CLUSTERED'  -- Don't drop clustered indexes
    AND s.user_seeks = 0
    AND s.user_scans = 0
    AND s.user_lookups = 0
    AND s.user_updates > 0  -- Index maintained but never used
ORDER BY s.user_updates DESC;

-- Find most used indexes
SELECT TOP 20
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks + s.user_scans + s.user_lookups AS TotalReads,
    s.user_updates AS TotalWrites,
    CAST((s.user_seeks + s.user_scans + s.user_lookups) * 1.0 / NULLIF(s.user_updates, 0) AS DECIMAL(10,2)) AS ReadWriteRatio
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
    AND s.database_id = DB_ID()
ORDER BY TotalReads DESC;

Covering Indexes

-- Before: Key lookup required (expensive)
SELECT 
    CustomerID,
    FirstName,
    LastName,
    Email,
    Phone
FROM dbo.Customers
WHERE LastName = 'Smith';

-- Index on LastName only → requires key lookup for other columns
CREATE INDEX IX_Customers_LastName ON dbo.Customers(LastName);

-- After: Covering index eliminates key lookup
DROP INDEX IX_Customers_LastName ON dbo.Customers;
CREATE INDEX IX_Customers_LastName 
ON dbo.Customers(LastName)
INCLUDE (FirstName, Email, Phone);  -- Add columns to index

Parameter Sniffing

Detecting Parameter Sniffing

-- Symptom: Query sometimes fast, sometimes slow with same structure

-- Check for parameter sniffing
SELECT 
    qs.execution_count,
    qs.min_elapsed_time / 1000 AS MinDurationMs,
    qs.max_elapsed_time / 1000 AS MaxDurationMs,
    (qs.max_elapsed_time - qs.min_elapsed_time) / 1000 AS VarianceMs,
    qs.total_logical_reads,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.max_elapsed_time > qs.min_elapsed_time * 10  -- 10x variance
    AND qs.execution_count > 10
ORDER BY VarianceMs DESC;

Solutions for Parameter Sniffing

Solution 1: OPTIMIZE FOR UNKNOWN

CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerID INT
AS
BEGIN
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
    OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));  -- Use average statistics
END;
GO

Solution 2: Local Variables

CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerID INT
AS
BEGIN
    -- Use local variable to prevent parameter sniffing
    DECLARE @LocalCustomerID INT = @CustomerID;
    
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @LocalCustomerID;
END;
GO

Solution 3: RECOMPILE

CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerID INT
AS
BEGIN
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
    OPTION (RECOMPILE);  -- Generate new plan each execution
END;
GO

Solution 4: Plan Guides (Advanced)

-- Create plan guide to force OPTIMIZE FOR UNKNOWN
EXEC sp_create_plan_guide
    @name = N'PlanGuide_GetOrdersByCustomer',
    @stmt = N'SELECT OrderID, OrderDate, TotalAmount FROM dbo.Orders WHERE CustomerID = @CustomerID',
    @type = N'OBJECT',
    @module_or_batch = N'dbo.GetOrdersByCustomer',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN))';

Query Rewrite Techniques

EXISTS vs. IN

-- ✅ GOOD: EXISTS (stops at first match)
SELECT 
    c.CustomerID,
    c.CustomerName
FROM dbo.Customers c
WHERE EXISTS (
    SELECT 1 FROM dbo.Orders o
    WHERE o.CustomerID = c.CustomerID
);

-- ❌ SLOWER: IN (may evaluate all matches)
SELECT 
    c.CustomerID,
    c.CustomerName
FROM dbo.Customers c
WHERE c.CustomerID IN (
    SELECT CustomerID FROM dbo.Orders
);

JOIN vs. Subquery

-- ✅ GOOD: JOIN (usually faster)
SELECT 
    c.CustomerID,
    c.CustomerName,
    COUNT(o.OrderID) AS OrderCount
FROM dbo.Customers c
INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName;

-- ❌ SLOWER: Correlated subquery (executes per row)
SELECT 
    c.CustomerID,
    c.CustomerName,
    (SELECT COUNT(*) FROM dbo.Orders o WHERE o.CustomerID = c.CustomerID) AS OrderCount
FROM dbo.Customers c;

UNION ALL vs. UNION

-- ✅ FASTER: UNION ALL (no duplicate removal)
SELECT CustomerID, CustomerName FROM dbo.Customers WHERE CustomerID < 1000
UNION ALL
SELECT CustomerID, CustomerName FROM dbo.CustomersArchive WHERE CustomerID < 1000;

-- ❌ SLOWER: UNION (removes duplicates with sort)
SELECT CustomerID, CustomerName FROM dbo.Customers WHERE CustomerID < 1000
UNION
SELECT CustomerID, CustomerName FROM dbo.CustomersArchive WHERE CustomerID < 1000;

Set-Based vs. Cursor

-- ❌ SLOW: Cursor approach
DECLARE @OrderID INT;
DECLARE cur CURSOR FOR 
    SELECT OrderID FROM dbo.Orders WHERE Status = 'Pending';

OPEN cur;
FETCH NEXT FROM cur INTO @OrderID;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.ProcessOrder @OrderID;
    FETCH NEXT FROM cur INTO @OrderID;
END;

CLOSE cur;
DEALLOCATE cur;

-- ✅ FAST: Set-based approach
UPDATE o
SET o.Status = 'Processed',
    o.ProcessedDate = GETUTCDATE()
FROM dbo.Orders o
WHERE o.Status = 'Pending';

Key Takeaways

• Read execution plans to identify expensive operators
• Keep statistics updated with AUTO_UPDATE or manual updates
• Use query hints sparingly - only when optimizer fails
• Implement missing index recommendations carefully
• Detect parameter sniffing with execution time variance
• Solve parameter sniffing with OPTIMIZE FOR UNKNOWN or RECOMPILE
• Prefer EXISTS over IN for existence checks
• Use JOINs instead of correlated subqueries
• Choose UNION ALL over UNION when duplicates acceptable
• Always use set-based operations over cursors

Next Steps

• Enable execution plans for slow queries
• Update outdated statistics
• Implement high-impact missing indexes
• Audit procedures for parameter sniffing
• Review query patterns for optimization opportunities

Additional Resources

• Execution Plans
• Statistics
• Query Hints
• Parameter Sniffing

---

Measure. Analyze. Optimize. Repeat.