Query Optimization Skill

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

Agent Skills: Query Optimization

Install this agent skill to your local

Skill Files