Tuning a slow query from its text alone is often guesswork. An execution plan shows how SQL Server reaches the data, which join algorithms it chooses, and how many rows it expects between operations. The goal is not to remove the most expensive-looking icon immediately, but to identify where optimizer estimates diverge from real execution.

Actual versus estimated plans

An estimated plan exposes optimizer choices without executing the query, which is useful when production execution would change data. An actual plan executes the statement and adds runtime facts such as real row counts. Large differences between Estimated Rows and Actual Rows can indicate stale statistics, skewed data, unsuitable predicates, or parameter-sniffing behavior.

Analyzing a sales query

This query searches orders by customer and date range. A scan on Orders is not automatically wrong; the percentage of rows returned and the number of pages read matter more than the operator name alone.

SELECT o.Id, o.OrderDate, o.TotalAmount
FROM dbo.Orders AS o
WHERE o.CustomerId = @CustomerId
  AND o.OrderDate >= @StartDate
  AND o.OrderDate < @EndDate
ORDER BY o.OrderDate DESC;

CREATE INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders(CustomerId, OrderDate DESC)
INCLUDE (TotalAmount);

The composite index begins with the equality predicate CustomerId and then supports the date range. Including TotalAmount can satisfy the projection without a key lookup. Before creating it, use Query Store to verify query frequency and compare the additional write cost.

A repeatable reading workflow

1. Record duration, CPU, logical reads, and returned rows before changing anything.
2. Follow data from right to left to understand the operator pipeline.
3. Mark operators with large differences between estimated and actual rows.
4. Inspect sort warnings, hash spills, and repeated key lookups with runtime data.
5. Rerun with the same parameters and compare the result through Query Store.

Misleading shortcuts

• Treating the plan cost percentage as elapsed time even though it is an optimizer estimate.
• Trying to convert every scan into a seek by adding another index.
• Assuming a query that is fast on small test data will behave identically with production distribution.

Conclusion

An execution plan is evidence explaining SQL Server's decisions, not a ready-made prescription. Reliable tuning combines the plan with IO measurements, Query Store history, and workload context. Reduce incorrect cardinality estimates and unnecessary reads first, then evaluate both query gains and write overhead before keeping an index change.