SQL Server Execution Plans: Reading and Optimizing

Execution plans are your window into SQL Server’s query optimizer. Learning to read them is essential for performance tuning. Here’s how to get started.

Getting an Execution Plan

-- Estimated plan (doesn't run query)
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Orders WHERE CustomerId = 123;
GO
SET SHOWPLAN_XML OFF;

-- Actual plan (runs query)
SET STATISTICS XML ON;
SELECT * FROM Orders WHERE CustomerId = 123;
SET STATISTICS XML OFF;

-- Or in SSMS: Ctrl+M (include actual plan), then run

Key Operators to Know

• Table Scan: Reading every row. Usually bad.
• Index Seek: Using an index efficiently. Good.
• Index Scan: Reading whole index. Check if seek is possible.
• Key Lookup: Going back to table for columns not in index.
• Nested Loops: For each row in A, find matches in B.
• Hash Match: Build hash table for joins/aggregates.
• Sort: Expensive. Check if index can provide order.

Reading the Plan

Plans read right-to-left, bottom-to-top. Data flows from right to left. Thicker arrows mean more rows. Hover for details.

Warning Signs

• Yellow triangle: Missing index or statistics warning
• Fat arrows: Lots of data moving
• Key Lookups: Consider covering index
• Estimated vs Actual rows differ: Statistics may be stale

Common Fixes

-- Table scan? Add an index
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);

-- Key lookup? Make it a covering index
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId) 
INCLUDE (OrderDate, TotalAmount);

-- Stale statistics? Update them
UPDATE STATISTICS Orders;

-- Parameter sniffing? Consider OPTION (RECOMPILE)
SELECT * FROM Orders WHERE CustomerId = @Id
OPTION (RECOMPILE);

References

• Execution Plans Documentation
• Paste The Plan (share plans)