Query Optimization

Comprehensive guide to T-SQL query optimization techniques.

Quick Reference

SARGable vs Non-SARGable Patterns

Non-SARGable (Bad)	SARGable (Good)
WHERE YEAR(Date) = 2024	WHERE Date >= '2024-01-01' AND Date < '2025-01-01'
WHERE LEFT(Name, 3) = 'ABC'	WHERE Name LIKE 'ABC%'
WHERE Amount * 1.1 > 1000	WHERE Amount > 1000 / 1.1
WHERE ISNULL(Col, 0) = 5	WHERE Col = 5 OR Col IS NULL
WHERE VarcharCol = 123	WHERE VarcharCol = '123'

Join Types Performance

Join Type	Best For	Characteristics
Nested Loop	Small outer, indexed inner	Low memory, good for small sets
Merge Join	Sorted inputs, similar sizes	Efficient for sorted data
Hash Join	Large unsorted inputs	High memory, good for large sets

Query Hints Quick Reference

Hint	Purpose
OPTION (RECOMPILE)	Fresh plan each execution
OPTION (OPTIMIZE FOR (@p = value))	Optimize for specific value
OPTION (OPTIMIZE FOR UNKNOWN)	Use average statistics
OPTION (MAXDOP n)	Limit parallelism
OPTION (FORCE ORDER)	Use exact join order
WITH (NOLOCK)	Read uncommitted (dirty reads)
WITH (FORCESEEK)	Force index seek

Core Optimization Principles

1. SARGability

SARG = Search ARGument. SARGable queries can use index seeks:

-- Non-SARGable: Function on column
WHERE DATEPART(year, OrderDate) = 2024
WHERE UPPER(CustomerName) = 'JOHN'
WHERE OrderAmount + 100 > 500

-- SARGable: Preserve column
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
WHERE CustomerName = 'john' COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE OrderAmount > 400

2. Implicit Conversions

Avoid data type mismatches:

-- Bad: Implicit conversion (varchar column compared to int)
WHERE VarcharColumn = 12345

-- Good: Match types exactly
WHERE VarcharColumn = '12345'

-- Check for implicit conversions in execution plan
-- Look for CONVERT_IMPLICIT warnings

3. OR Optimization

OR on different columns prevents seek:

-- Inefficient: OR on different columns
SELECT * FROM Orders
WHERE CustomerID = 1 OR ProductID = 2

-- Better: UNION for OR optimization
SELECT * FROM Orders WHERE CustomerID = 1
UNION ALL
SELECT * FROM Orders WHERE ProductID = 2 AND CustomerID <> 1

4. EXISTS vs IN vs JOIN

-- EXISTS: Best for semi-joins (checking existence)
SELECT * FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID)

-- IN: Good for small static lists
SELECT * FROM Products WHERE CategoryID IN (1, 2, 3)

-- JOIN: Best when you need data from both tables
SELECT c.*, o.OrderDate
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID

Parameter Sniffing Solutions

Problem

-- First execution with CustomerID=1 (10 rows) creates plan
-- Subsequent execution with CustomerID=999 (1M rows) uses same plan
CREATE PROCEDURE GetOrders @CustomerID INT AS
    SELECT * FROM Orders WHERE CustomerID = @CustomerID

Solution 1: OPTION (RECOMPILE)

CREATE PROCEDURE GetOrders @CustomerID INT AS
    SELECT * FROM Orders
    WHERE CustomerID = @CustomerID
    OPTION (RECOMPILE)
-- Best for: Infrequent queries, highly variable data distribution

Solution 2: OPTIMIZE FOR

-- Optimize for specific value
OPTION (OPTIMIZE FOR (@CustomerID = 1))

-- Optimize for unknown (average statistics)
OPTION (OPTIMIZE FOR UNKNOWN)

Solution 3: Local Variables

CREATE PROCEDURE GetOrders @CustomerID INT AS
BEGIN
    DECLARE @LocalID INT = @CustomerID
    SELECT * FROM Orders WHERE CustomerID = @LocalID
END
-- Hides parameter from optimizer, similar to OPTIMIZE FOR UNKNOWN

Solution 4: Query Store Hints (SQL 2022+)

EXEC sys.sp_query_store_set_hints
    @query_id = 12345,
    @hints = N'OPTION (RECOMPILE)'
-- Apply hints without code changes

Solution 5: PSP Optimization (SQL 2022+)

-- Enable Parameter Sensitive Plan optimization
ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 160
-- Automatically creates multiple plans based on parameter values

Execution Plan Analysis

Key Operators to Watch

Operator	Warning Sign	Action
Table Scan	Missing index	Add appropriate index
Index Scan	Non-SARGable predicate	Rewrite query
Key Lookup	Missing covering index	Add INCLUDE columns
Sort	Missing index for ORDER BY	Add sorted index
Hash Match	Large memory grant	Consider index
Spools	Repeated scans	Restructure query

Estimated vs Actual Rows

-- Large difference indicates statistics problem
-- Check if stats need updating:
UPDATE STATISTICS TableName WITH FULLSCAN

-- Or enable auto-update:
ALTER DATABASE YourDB SET AUTO_UPDATE_STATISTICS ON

Finding Missing Indexes

SELECT
    CONVERT(DECIMAL(18,2), migs.avg_user_impact) AS AvgImpact,
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
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 migs.avg_user_impact DESC

Statistics Management

View Statistics Info

DBCC SHOW_STATISTICS('TableName', 'IndexName')

Update Statistics

-- Update all statistics on table
UPDATE STATISTICS TableName

-- Update with full scan (most accurate)
UPDATE STATISTICS TableName WITH FULLSCAN

-- Update specific statistics
UPDATE STATISTICS TableName StatisticsName

Auto-Update Settings

-- Enable async auto-update (better for OLTP)
ALTER DATABASE YourDB SET AUTO_UPDATE_STATISTICS_ASYNC ON

Additional References

For deeper coverage of performance diagnostics, see:

• references/dmv-diagnostic-queries.md - DMV queries for performance analysis