---
note type:
  - database
  - sql
  - note
date: 2026-07-03
done:
link: https://bertwagner.com/posts/introduction-to-sql-server-execution-plans/
---
## What is an Execution Plan?

- SQL Server is a **declarative language**:
    - You specify _what_ data you want, not _how_ to retrieve it.
- An **execution plan** shows:
    - How SQL Server decides to retrieve the data.
    - The sequence of operations (joins, scans, seeks, etc.).

### Key Idea

- The **Query Optimizer**:
    - Evaluates multiple possible ways to execute a query.
    - Chooses a plan that is **“good enough”**, not necessarily the absolute fastest.
- Reason:
    - Evaluating every possible plan would be too slow and impractical.

### When Problems Occur

- Poor performance happens when:
    - The optimizer picks an inefficient plan.
- Causes may include:
    - Inaccurate statistics
    - Incorrect assumptions about data distribution
- Execution plans help diagnose these issues.

---

## Types of Execution Plan Views

### 1. Text-Based Execution Plan

To see an execution plan for your query, you can run `SET SHOWPLAN_ALL ON`. This will provide a text-based tree representation of the plan:

`SET SHOWPLAN_ALL ON GO --Query 
`GO SET SHOWPLAN_ALL OFF GO`

![[Pasted image 20260703150027.png]]

### 2. Graphical Execution Plan (Preferred)

- Generated via:
    - **“Display Estimated Execution Plan”** in SSMS
- Features:
    - Visual flow of query execution
    - Easier to interpret than text-based plans
![[Pasted image 20260703150047.png]]
#### XML View

- Access via:
    - Right-click → “Show Execution Plan XML”
- Contains:
    - Detailed metadata and properties not always visible graphically

---

## Estimated vs Actual Execution Plans

### Estimated Execution Plan

- Created **before execution**
- Based on:
    - Table statistics
    - Metadata
- Does **NOT include runtime data**

---

### Actual Execution Plan

- Created **after execution**
- Includes:
    - Actual number of rows processed
    - Number of executions
    - Runtime metrics

### Key Difference

|Feature|Estimated Plan|Actual Plan|
|---|---|---|
|When generated|Before execution|After execution|
|Uses real data|No|Yes|
|Structure|Same|Same|
|Runtime metrics|No|Yes|

**Important Insight:**

- The plan structure is usually identical.
- Only the **runtime statistics differ**.

---

## Live Query Statistics (SQL Server 2016+)

### What It Does

- Shows execution progress in **real time**
- Combines:
    - Estimated plan + live runtime data

### Benefits

- Helps identify **bottlenecks quickly**
- Useful for:
    - Long-running queries
    - Beginners learning execution plans

### Example Insight

- If a **Clustered Index Scan** takes most time:
    - Focus optimisation efforts there.

---

## Historical Execution Plans

### 1. Plan Cache

- SQL Server stores plans for reuse.
- Access via DMV:
    - `sys.dm_exec_query_plan`

#### Key Notes

- Contains:
    - Cached (previously executed) query plans
- Limitations:
    - Plans may be removed from cache
    - Typically shows **estimated plans only**

---

### 2. Query Store

- Stores historical query performance data
- Requirements:
    - Must be enabled on the database

#### Benefits

- Allows:
    - Retrieval of past execution plans
    - Query performance analysis over time

---

## Why Execution Plans Matter

- Provide visibility into:
    - How SQL Server retrieves data
- Essential for:
    - **Performance tuning**
    - Identifying inefficient operations
    - Diagnosing slow queries

---

## Summary

- Execution plans reveal **how SQL Server executes queries**.
- The optimizer selects a **“good enough” plan**, not always optimal.
- Main viewing options:
    - Text plan
    - Graphical plan (most useful)
    - XML plan
- Types of plans:
    - **Estimated** → Pre-execution
    - **Actual** → Includes runtime data
- Advanced tools:
    - **Live Query Statistics** → real-time insights
    - **Plan Cache & Query Store** → historical analysis