Jam SQL Studio Published: 2026-02-17
How to Read SQL Execution Plans (Visual Guide)
Your query returns the right data, but it takes 12 seconds. Why? The execution plan tells you exactly what the database engine is doing, step by step. This guide teaches you to read plans, spot performance problems, and fix them.
In This Guide
1. What Is an Execution Plan?
When you write SQL, you describe what data you want. The database engine decides how to get it. That "how" is the execution plan.
The query optimizer evaluates different strategies for retrieving your data — which indexes to use, what join algorithm to pick, whether to sort in memory or on disk — and selects the one it estimates will be fastest. The execution plan is a map of that strategy.
Reading execution plans is the single most effective skill for SQL performance tuning. No amount of guessing replaces the concrete information a plan provides about what the engine is actually doing.
2. Estimated vs Actual Plans: Which to Use
There are two types of execution plans, and they serve different purposes:
| Aspect | Estimated Plan | Actual Plan |
|---|---|---|
| Runs the query? | No — predictions only | Yes — executes the query |
| Row counts | Estimated (from statistics) | Real numbers from execution |
| Execution time | Not available | Per-operator elapsed time |
| Memory grants | Predicted | Actual allocation and usage |
| Speed to generate | Instant (no execution) | Requires full query execution |
| Best for | Quick checks, safe on production | Accurate diagnosis, development |
Rule of thumb: Use estimated plans for quick checks (especially on production databases where you don't want to run expensive queries). Use actual plans when you need accurate diagnosis — estimated row counts can be wildly off if statistics are outdated.
3. How to Generate an Execution Plan
The method varies by database engine and tool. Here are the most common approaches:
Jam SQL Studio
Click Query > Display Estimated Plan to generate a plan without running the query. Or execute the query normally and click the Execution Plan tab in the results panel to see the actual plan. Both display an interactive graphical diagram. See the full execution plans documentation for details.
SQL Server / SSMS
Enable Include Actual Execution Plan (Ctrl+M) before running your query, or click Display Estimated Execution Plan (Ctrl+L). You can also use T-SQL:
SET SHOWPLAN_XML ON; -- Estimated plan (XML)
GO
SELECT * FROM Orders WHERE CustomerID = 42;
GO
SET SHOWPLAN_XML OFF;PostgreSQL
Use the EXPLAIN command:
EXPLAIN SELECT * FROM orders WHERE customer_id = 42; -- Estimated
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; -- Actual
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM orders; -- DetailedMySQL
Use EXPLAIN or the newer EXPLAIN ANALYZE:
EXPLAIN SELECT * FROM orders WHERE customer_id = 42; -- Estimated
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; -- Actual (MySQL 8.0.18+)4. Reading the Plan: A Visual Walkthrough
A graphical execution plan is a flow diagram. Here's how to read it:
Data Flows Right to Left
The rightmost operators are where data originates (from tables or indexes). Data flows left through intermediate operations (joins, filters, sorts) until it reaches the leftmost node — the final result. Think of it as a pipeline: raw data enters on the right and refined results emerge on the left.
Arrow Thickness = Data Volume
The thickness of the arrows between operators represents how many rows flow between them. A thick arrow means lots of data is moving. If you see a thick arrow going into an operator and a thin one coming out, that operator is filtering effectively. If arrows stay thick throughout the plan, the query might be processing more data than necessary.
Cost Percentages
Each operator node displays a cost percentage relative to the total query cost. An operator showing "45%" accounts for nearly half the total estimated work. This is where you should focus optimization efforts. However, costs are estimates — in actual plans, also check elapsed time per operator.
Click for Details
In graphical plan viewers (Jam SQL Studio, SSMS), clicking an operator reveals detailed properties: the specific index used, predicate expressions, memory allocation, I/O statistics, and more. These details are essential for understanding why the optimizer chose a particular strategy.
5. The 6 Operators Every SQL Developer Must Know
Table Scan / Clustered Index Scan
WatchReads every row in the table (or clustered index). Necessary for small tables. On large tables, this is usually a sign that an appropriate index is missing. If you see a scan on a table with millions of rows, check whether the WHERE clause columns are indexed.
Index Seek
GoodUses an index to jump directly to matching rows. This is what you want for selective queries. The optimizer picks index seeks when it estimates only a small fraction of the table matches the predicate. If you see a scan where you expected a seek, check that the right index exists and that statistics are up to date.
Key Lookup (Bookmark Lookup)
WatchAfter an index seek finds matching rows, a key lookup fetches additional columns from the base table that aren't in the index. A few lookups are fine. Thousands of lookups can be slower than a table scan. Fix this by creating a covering index that includes the needed columns.
Nested Loops Join
Good for small setsFor each row in the outer input, scans the inner input for matches. Very efficient when the outer input is small and the inner input has an index. Problematic when both inputs are large — the work grows multiplicatively.
Hash Match Join
Good for large setsBuilds a hash table from the smaller input, then probes it with the larger input. Efficient for joining two large, unsorted datasets. Requires memory for the hash table. If the hash table spills to disk (tempdb), performance drops significantly.
Sort
WatchOrders rows for ORDER BY, GROUP BY, DISTINCT, or merge joins. Sorts require memory and can spill to disk for large datasets. If you see an expensive sort, consider creating an index that delivers rows in the needed order, eliminating the sort entirely.
6. 5 Common Performance Problems and How to Fix Them
Problem 1: Table Scan on a Large Table
What you see: A Table Scan or Clustered Index Scan operator with high cost on a table with thousands or millions of rows.
Why it happens: No suitable index exists for the WHERE clause, or the optimizer estimates that a scan is cheaper (e.g., when the query returns most of the table).
How to fix:
- Create a nonclustered index on the filtered columns
- Make it a covering index by including the SELECT columns
- Check if the predicate uses functions that prevent index use (e.g.,
WHERE YEAR(created_at) = 2025)
Problem 2: Expensive Key Lookups
What you see: An Index Seek followed by a Key Lookup, both with significant cost. The seek finds rows efficiently, but the lookup dominates total cost.
Why it happens: Your index covers the WHERE clause but the SELECT list or JOIN references columns not in the index.
How to fix:
- Add INCLUDE columns to the existing index to cover the missing columns
- Limit the SELECT columns to only what's needed (avoid
SELECT *)
Problem 3: Estimated vs Actual Row Count Mismatch
What you see: In the actual plan, the estimated row count on an operator is very different from the actual row count (e.g., estimated 10 rows, actual 50,000).
Why it happens: Outdated table statistics, parameter sniffing, or complex predicates that the optimizer can't estimate well.
How to fix:
- Update statistics:
UPDATE STATISTICS TableName(SQL Server) orANALYZE table_name(PostgreSQL) - For parameter sniffing: use
OPTION (RECOMPILE)or plan guides
Problem 4: Sort Spilling to Disk
What you see: A Sort operator with a warning icon indicating a tempdb spill. Actual memory usage exceeds the granted memory.
Why it happens: The optimizer underestimated the data size, or the server doesn't have enough memory to sort in-memory.
How to fix:
- Create an index that delivers rows in the required order, eliminating the sort
- Reduce the amount of data being sorted (filter earlier in the query)
- Update statistics so the optimizer requests an accurate memory grant
Problem 5: Implicit Type Conversions
What you see: A warning on a predicate about an implicit conversion, or a Compute Scalar operator converting data types before a comparison.
Why it happens: The WHERE clause compares columns of different types (e.g., comparing a VARCHAR column with an INT parameter). The engine converts every row, preventing index use.
How to fix:
- Ensure parameters match the column data type
- Use explicit CAST/CONVERT on the parameter, not the column
- Fix the application code to pass the correct type
7. Comparing Plans: Before and After
After making an optimization (adding an index, rewriting a query), you need to verify it actually helped. Plan comparison makes this concrete.
How to Compare Plans in Jam SQL Studio
- Generate the execution plan for the original query
- Save the plan (right-click → Save Plan)
- Make your optimization (add an index, rewrite the query)
- Generate the new execution plan
- Click Compare Plans and select the saved plan
- Review the side-by-side comparison with highlighted differences
Plan comparison highlights changes in operators, costs, and row estimates. You can verify that your index eliminated a table scan, that a sort disappeared, or that row estimates became more accurate after updating statistics.
This is especially valuable when optimizing complex queries with multiple joins — a change that improves one part of the plan might negatively impact another, and the comparison view makes this immediately visible.
8. FAQ
Do execution plans work for all databases?
Yes, all major databases support execution plans, though the format differs. SQL Server produces graphical XML plans. PostgreSQL uses text-based EXPLAIN output (or JSON). MySQL provides tabular EXPLAIN output. Jam SQL Studio renders graphical plans for SQL Server and shows text-based plans for PostgreSQL and MySQL.
Will running an actual plan affect my production database?
An actual plan runs the query for real. For SELECT queries, the impact is the same as running the query normally. For INSERT/UPDATE/DELETE queries, the changes will be applied. Always use estimated plans on production if you're not sure about the impact, or wrap the query in a transaction you can roll back.
Why does my plan look different each time?
The optimizer re-evaluates plans based on current statistics, parameter values, and server load. If statistics change (after data modifications) or parameters vary, the optimizer may choose a different strategy. This is expected behavior. Cached plans are reused for repeated queries with the same parameters.
Can AI help me read execution plans?
Yes. Jam SQL Studio's MCP integration lets AI agents generate execution plans, read operator details, and suggest optimizations based on what the plan reveals. You can ask Claude Code to analyze a slow query's plan and recommend indexes without manually interpreting every operator.
Visualize Your Execution Plans
Jam SQL Studio renders interactive graphical execution plans with plan comparison, operator details, and missing index hints.