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