Mastering Execution Plans

An Execution Plan is a visual map showing how SQL Server executed your query. It shows you exactly which indexes were used, how many rows were processed, and where the "cost" is. If you can't read an execution plan, you can't tune a database.

1. Index Seek vs Index Scan

• Index Seek (GOOD): SQL Server used the index tree to jump directly to the data it needed. Fast and efficient.
• Index Scan (BAD-ish): SQL Server had to read the *entire* index from beginning to end. Faster than a Table Scan but still means an index is being misused.
• Table Scan (UGLY): No index was used. SQL Server read every single page in the table. Kill this immediately.

2. Key Look-ups & Spills

Look for the Key Lookup operator. It means your index isn't "Covering" the query. Also watch out for Sort/Hash Spills (yellow warning icons)—this means SQL Server ran out of RAM and had to use the slow TempDB to finish the task.

4. Interview Mastery

Q: "What is the difference between an Estimated and an Actual execution plan?"

Architect Answer: "The **Estimated Plan** is generated by the Optimizer based on statistics *before* any code runs. It is just a guess. The **Actual Plan** is generated *after* the query finishes and shows the real number of rows and memory used. You should always use the Actual Plan when debugging, as the Optimizer can be wrong if your statistics are out of date."