Read an actual SQL Server plan as a chain of evidence

A plan diagram can make a slow query look easy to diagnose: find the largest percentage and remove that operator. Unfortunately, those percentages are estimates of optimizer cost, not a stopwatch for the execution you just observed. A useful investigation connects the request, the rows flowing through the plan, and measured resource use.

Establish what actually ran

Capture the statement text, parameter values and types, database compatibility level, and relevant session settings. A query pasted into another window with different parameters is a different experiment. Include the actual execution plan in SSMS, or use STATISTICS XML, and execute only a workload whose effects you understand. Obtaining an actual plan runs the statement; it is not a harmless substitute for an estimated plan when the batch writes data.

The following disposable example compares the same aggregation before and after adding an index. Enable the actual plan before executing it in a test session.

CREATE TABLE #PlanOrders
(
    OrderId int NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    Amount decimal(12,2) NOT NULL
);
;WITH N AS
(
    SELECT TOP (10000)
        CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS n
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT #PlanOrders (OrderId, CustomerId, Amount)
SELECT n, n % 100, CONVERT(decimal(12,2), n % 250)
FROM N;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT SUM(Amount) AS Total
FROM #PlanOrders WHERE CustomerId = 42;

CREATE INDEX IX_PlanOrders_Customer
ON #PlanOrders (CustomerId) INCLUDE (Amount);

SELECT SUM(Amount) AS Total
FROM #PlanOrders WHERE CustomerId = 42;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
DROP TABLE #PlanOrders;

Both SELECT statements should return the same total. Compare their logical reads in the Messages output and inspect the access operators. The second plan has a covering access path for CustomerId and Amount, but record the actual choice rather than promising that an optimizer must use a particular icon. The table is deliberately small enough for a safe demonstration; production selectivity and row width can change the tradeoff.

Separate setup work from query work. The index build also appears in the batch and consumes resources, but its plan does not describe the SELECT. Save the before and after plans with their measurements. Do not clear a shared server's buffer or plan cache to manufacture a clean test.

Follow the first important row-count error

Read the data flow from the access operators toward the result and compare estimated and actual rows at meaningful boundaries. If a scan estimates 100 qualifying rows but returns 200,000, a later expensive join may be a consequence of that early misunderstanding. Tuning only the final sort can leave the cause untouched.

Distinguish rows returned from rows read. A seek can locate a broad range and then discard most rows through a residual predicate. The word Seek does not prove that little work occurred. Open the properties, inspect seek predicates and residual predicates, and compare the amount examined with the amount passed onward. Similarly, scanning most of a small table can be cheaper than performing many random lookups.

Nested loops require attention to execution counts. A cheap inner operation repeated 100,000 times can dominate total work. Check whether the displayed estimate is per execution or aggregated, especially across parallel threads. Compare like-for-like quantities instead of treating every number in a tooltip as the same metric.

Lookups, sorts, spools, and hash joins are not automatically defects. Ask why each is present and how much work it performs. An index that removes a lookup may increase storage and write cost. A spool may save repeated work. A sort may implement an explicit ordering requirement that the application genuinely needs.

Turn one observation into one measured change

Choose a hypothesis you can falsify: a residual predicate reads too wide a range, an estimate is wrong after a skewed filter, or an unnecessary projection increases sort width. Make one relevant change, then repeat representative parameter cases and compare result correctness, reads, CPU, elapsed time, and concurrency impact.

Runtime warnings deserve investigation, but their existence is not a complete diagnosis. A spill can matter greatly on a busy system and little in a tiny one-off query. A missing-index suggestion describes a candidate for one optimization context; compare it with existing indexes and the write workload before creating it.

Finally, reconcile server work with user latency. Low CPU and modest reads do not exclude blocking, waiting for memory, or a slow client consuming results. An actual plan is powerful evidence about execution, but it does not replace wait information and end-to-end timing. Keep the original plan and the exact reason for the change so the next regression can be investigated against a reproducible baseline.

Technical references: Microsoft Learn: Actual execution plans · Microsoft Learn: Showplan operators · Microsoft Learn: STATISTICS IO.