majiayu000 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