vault/Career/Database - SQL/SQL Execution Plans.md

note type, date, done, link

note type	date	done	link
database sql note	2026-07-03		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`

!

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 !

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