Execution Plans

Execution plans are the roadmap that SQL Server uses to retrieve data. Understanding how to read and analyze execution plans is crucial for performance tuning.

Topics

Overview

An execution plan shows how SQL Server will execute a query, including:

Which indexes are used
Join methods
Scan types (table scan vs index scan/seek)
Sort operations
And various other operators

Why Execution Plans Matter

Performance Tuning: Identify bottlenecks in queries
Index Analysis: Understand if indexes are being used effectively
Query Optimization: See why a query is slow and how to improve it
Cardinality Estimates: Understand row count predictions

How to View Execution Plans

Using SSMS (SQL Server Management Studio)

Enable “Include Actual Execution Plan” (Ctrl + M)
Run your query
View the Execution Plan tab

Using T-SQL

SET STATISTICS XML ON;
-- Your query here
SET STATISTICS XML OFF;

Common Operators

Operator	Description
Table Scan	Reads all rows from a table
Index Scan	Reads all entries from an index
Index Seek	Uses index to find specific rows
Clustered Index Scan	Scans entire clustered index (entire table)
Nested Loops	Joins using nested iteration
Hash Join	Joins using hash table
Merge Join	Joins using sorted inputs
Compute Scalar	Calculates new values from existing data
Sort	Orders data
Filter	Filters rows based on condition

Learning