SQL Server Execution Plan: Read & Tune

This guide shows you how to read a SQL Server execution plan and use it to find and fix performance bottlenecks in your queries. You’ll learn what the icons mean, how to spot the most expensive operations, and how to apply common tuning techniques like adding indexes or rewriting joins. By the end, you’ll be able to open a plan, identify the problem, and make a targeted change that speeds up your query.

What You’ll Need

• SQL Server Management Studio (SSMS) – any recent version.
• A query that you want to analyze (or a slow-running query from your application).
• Permission to view execution plans (usually granted by default) and, ideally, permission to create indexes or modify the query for testing.
• Basic familiarity with SQL syntax (SELECT, JOIN, WHERE).

Step-by-Step

1. Open SSMS and connect to your database. Use a test environment if possible, so you don’t accidentally affect production performance.
2. Write or paste your query into a new query window. For example: SELECT * FROM Orders WHERE OrderDate > '2024-01-01'.
3. Enable the actual execution plan. Click the “Include Actual Execution Plan” button on the toolbar (or press Ctrl+M). This tells SSMS to capture the plan when you run the query.
4. Run the query. Press F5 or click Execute. The query runs, and the results appear in the grid. Below the results, you’ll see a new tab labeled “Execution plan.” Click it.
5. Orient yourself. The plan reads from right to left. The rightmost icons are the data access operations (scans, seeks, lookups), and the leftmost is the final SELECT or INSERT/UPDATE/DELETE. Arrows show the flow of rows.
6. Find the most expensive step. Look for the thickest arrows – they represent the largest number of rows. Also, hover over each icon to see a tooltip with cost percentages. The step with the highest “Cost” (e.g., 70%) is your primary bottleneck.
7. Identify the operation type. Common icons include:
   • Clustered Index Scan – reads all rows from a table; often expensive for large tables.
   • Clustered Index Seek – reads only matching rows; usually good.
   • Key Lookup – for a nonclustered index, it fetches the full row; can be costly if many lookups.
   • Hash Match – used for joins or aggregations; often indicates a large input.
   • Nested Loops – for each row in one input, it looks up a match in the other; good for small inputs.
   • Sort – orders rows; can be expensive if sorting a large dataset.
8. Check the properties of the expensive step. Right-click the icon and select “Properties.” Look at:
   • Estimated vs. Actual Rows – a big discrepancy (e.g., estimated 10, actual 100,000) signals a bad cardinality estimate, often due to outdated statistics.
   • Output List – which columns are returned; if it includes many columns, a covering index might help.
   • Seek Predicates – the columns used to seek; if missing, you might need an index on those columns.
9. Apply a fix. Based on what you see:
   • If you see a Scan on a large table with a WHERE clause, create an index on the columns used in the WHERE. Example: CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate).
   • If you see a Key Lookup, consider a covering index that includes the columns you need, or use INCLUDE to add them.
   • If you see a Hash Match on a join, check if the join columns are indexed on both sides. Also, consider rewriting the query to filter earlier.
   • If you see a Sort for an ORDER BY, an index on the sort columns can eliminate it.
10. Re-run the query to see the new plan. Compare the cost percentages and the actual rows. The plan should show the new index being used (look for a Seek instead of a Scan).
11. Iterate. If the performance is still poor, repeat steps 6–9. Sometimes you need to tune multiple steps.

Common Mistakes

• Focusing on the wrong step. The step with the highest cost percentage is not always the root cause – a downstream step might be expensive because it received too many rows from an earlier step. Always trace back to the source of the row explosion.
• Ignoring estimated vs. actual rows. If the estimates are wildly off, your indexes might be fine, but your statistics are stale. Update statistics with UPDATE STATISTICS or let auto-update run.
• Adding too many indexes. Every index slows down writes and takes disk space. Only add indexes that directly address a proven bottleneck.
• Not testing with realistic data. A plan on a tiny test table can look great but fail on production-sized data. Always test with a representative data volume.
• Misinterpreting the direction. Remember, the plan flows right-to-left. A common mistake is to read it left-to-right and think the SELECT is the start.

Tips

• Use the “Actual” plan, not just the estimated one. Actual plans include actual row counts, which are essential for diagnosing cardinality issues.
• Save the plan as a .sqlplan file (File > Save Results As) so you can compare before/after changes or share with colleagues.
• Use the “Live Query Statistics” feature in SSMS to watch the plan flow in real time – it helps spot which step is slow.
• Look for warnings. Yellow warning icons on steps indicate issues like missing statistics or implicit conversions. Fix these first – they often cause poor estimates.
• Consider the whole query. Sometimes rewriting the query (e.g., using a CTE or splitting into temp tables) can give the optimizer a better path, even without new indexes.
• Test with SET STATISTICS IO ON to see logical reads – often more useful than execution time for tuning.

Troubleshooting

• No execution plan appears. Make sure you clicked “Include Actual Execution Plan” before running. Also, check that you have permission; if not, you’ll see an error. Try using the estimated plan (Ctrl+L) instead.
• The plan is too complex to read. Use the “Find Node” search box in the plan tab to locate a specific table or index. You can also zoom out (Ctrl+Minus) to see the whole picture.
• My index isn’t being used. The optimizer might still prefer a scan for small tables, or your query might have a function on the indexed column (e.g., WHERE YEAR(OrderDate) = 2024) which prevents a seek. Rewrite the condition to be sargable (e.g., OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01').
• The plan says “Estimated rows” but no actual rows. This happens when the query is still running or was cancelled. Make sure the query completes.
• I still see a Key Lookup after adding a covering index. Double-check that the index includes all columns in the SELECT and the JOIN/WHERE. If you used INCLUDE, verify the columns are listed correctly.
• Performance didn’t improve. The bottleneck might be I/O or memory, not CPU. Check wait stats (e.g., PAGEIOLATCH for disk) and consider if you need a better hardware or query plan.

Frequently Asked Questions

• What is the difference between estimated and actual execution plans? The estimated plan is generated without running the query, based on statistics. The actual plan is generated after running the query and includes actual row counts and elapsed times. Always use the actual plan for tuning.
• How do I know which index to create? Look at the columns in the WHERE, JOIN, and ORDER BY clauses. Create an index on those columns (in that order). If you need to return additional columns, include them with INCLUDE to make it a covering index.
• Why does my query use a scan even though I have an index? The optimizer may estimate that the index would return too many rows (more than ~5% of the table) and choose a scan instead. This can happen if your statistics are outdated or if the predicate is not selective enough. Update statistics and ensure the predicate is sargable.
• What does a Key Lookup mean and is it always bad? A Key Lookup occurs when a nonclustered index doesn’t contain all the columns needed, so SQL Server fetches the full row from the clustered index. It’s not always bad – if the number of lookups is small, it’s fine. But if there are thousands, it’s a problem. A covering index can eliminate it.
• Can I tune a query without changing the query text? Yes, you can add or modify indexes, update statistics, or use query hints (though hints should be a last resort). Sometimes you can also change the database’s compatibility level or enable features like columnstore indexes, but those are more advanced.
• How do I read the percentages in the plan? Each step shows a cost percentage relative to the total query cost. The sum of all steps is 100%. The step with the highest percentage is the most expensive, but as mentioned, it may not be the root cause – trace the arrows to see where rows multiply.

Related

• Fix Windows Insider Build Ran Into a Problem
• Turn Off Google Web & App Activity on iPhone
• Turn Off Focused Inbox in Outlook: Step-by-Step