DB-유

Reading SQL Server Execution Plans: The Basics

· mssql, performance, tuning

The first time I opened an execution plan I closed it again straight away. All those arrows and icons. But the common cases take about twenty minutes to learn, and after that it's the first thing I open when someone says a query is slow. It tells you which indexes the optimizer chose, how tables were joined, how many rows it expected versus got, and where the time went.

I use SQL Server 2012 or later with SSMS 17.x (the plan viewer improved a lot in 17), and the examples run against AdventureWorks2016. You need SHOWPLAN permission, which db_owner and sysadmin already have.

Capture an actual plan

In SSMS, press Ctrl+M (or Query → Include Actual Execution Plan) and run the query. A new Execution plan tab appears in the results pane. The estimated plan (Ctrl+L) is compiled without running the query and is useful for something that takes an hour, but the actual plan is what you want when possible because it includes real row counts.

To get a plan as XML that you can save and share:

SET STATISTICS XML ON;
SELECT p.Name, SUM(sod.LineTotal) AS Total
FROM Sales.SalesOrderDetail sod
JOIN Production.Product p ON p.ProductID = sod.ProductID
WHERE sod.ModifiedDate >= '2014-01-01'
GROUP BY p.Name;
SET STATISTICS XML OFF;

Click the XML link in the results to open it graphically. Right-click the plan and choose Save Execution Plan As... to produce a .sqlplan file.

Read right to left, top to bottom

Data flows from the right-hand leaf operators (scans and seeks) leftward through joins and aggregates to the SELECT operator on the far left. Each arrow carries rows; the thicker the arrow, the more rows. Hover over an arrow to see Actual Number of Rows and Estimated Number of Rows.

Each operator shows a Cost percentage. This is the optimizer's estimate, not a measurement, so treat it as a hint about where to look rather than truth. In SSMS 17 the actual plan also shows elapsed time and CPU per operator when you hover, which is much more reliable.

Access methods

  • Clustered Index Scan / Table Scan reads the whole table. Fine for a small lookup table or when you genuinely need most rows; a problem when a WHERE clause should have narrowed things down.
  • Index Seek uses the B-tree to jump to the matching rows. This is what you want for selective predicates.
  • Key Lookup (or RID Lookup on a heap) means the nonclustered index did not contain all the columns the query needs, so SQL Server goes back to the clustered index for each row. A Key Lookup next to a Nested Loops join with thousands of rows flowing through it is a classic sign that a covering index would help.

Hover over a scan and check the Predicate property. If you see a predicate on a scan of a large table, the query is filtering after reading everything, and an index on that column is the first thing to try.

Joins

  • Nested Loops — for each row from the top input, seek into the bottom input. Excellent when the top input is small; terrible when the estimate said 10 rows and 2 million showed up.
  • Hash Match — builds a hash table from one input and probes it with the other. Good for large unsorted inputs, but it needs a memory grant; a yellow warning triangle on the operator means it spilled to tempdb.
  • Merge Join — both inputs must be sorted on the join key. Very efficient when the sort order comes free from an index; if you see a Sort operator feeding it, the optimizer paid for that order.

Estimated vs actual rows

If I only get to do one check, it's this one. Hover over the arrows or operators and compare Estimated Number of Rows to Actual Number of Rows. A difference of an order of magnitude or more means the optimizer chose the plan on bad information, typically because of stale statistics, a non-SARGable predicate such as WHERE YEAR(OrderDate) = 2014, a table variable (always estimated at 1 row before 2019), or parameter sniffing.

Quick fix to test the statistics theory:

UPDATE STATISTICS Sales.SalesOrderDetail WITH FULLSCAN;

Re-run with the actual plan and see if the estimates move.

The yellow triangles

Operators with a yellow triangle carry a warning. Hover to read it. The ones I see most:

  • Operator used tempdb to spill data during execution on a Sort or Hash Match — the memory grant was too small, usually because of a bad estimate upstream.
  • Type conversion in expression (CONVERT_IMPLICIT(...)) may affect "CardinalityEstimate" in query plan choice — an implicit conversion, typically nvarchar parameter compared to a varchar column, which also prevents an index seek.
  • Columns With No Statistics — auto-create statistics is off or the query ran against a read-only database.

The SELECT operator at the far left has its own properties worth checking: Memory Grant, Reason For Early Termination Of Statement Optimization (TimeOut means the optimizer gave up before finding a good plan), and Parameter List showing the compiled versus runtime values.

Did the change help?

After a new index, rewritten predicate or updated statistics, capture the actual plan again and compare: the scan should have become a seek, the Key Lookup should be gone, estimates should be close to actuals, and SET STATISTICS IO ON should show fewer logical reads:

SET STATISTICS IO, TIME ON;
-- run the query
SET STATISTICS IO, TIME OFF;

I compare logical reads rather than duration, because they don't depend on what happens to be cached.

Odds and ends

  • Missing index hints shown in green at the top of the plan are a starting point, not a recommendation to be applied blindly. They often suggest very wide INCLUDE lists and never consider existing indexes that could be extended.
  • Cost percentages add up to 100 within a batch, so a cheap-looking operator in a plan with an expensive neighbor can still be slow in absolute terms.
  • Plans for stored procedures are cached with the first parameter values compiled. If a plan looks wrong for the parameters you are testing with, check the Parameter List on the SELECT operator.
  • Plan Explorer from SentryOne is a free tool that shows the same plan with actual row counts on the arrows and a sortable operator list. Worth installing once you are comfortable with the SSMS view.

Arrows, estimates versus actuals, the scan or lookup that shouldn't be there. That finds most slow queries for me.