"Why is this query slow" is one of those questions that people either answer by staring at the SQL text looking for something that feels wrong, or by pasting the query into a forum and hoping someone recognizes the pattern. Neither works reliably. The execution plan already has the answer, every time — the skill is reading it in the right order without getting overwhelmed by the operator tree, and knowing which handful of signals actually matter for the kind of aggregation-heavy queries BI tools generate.

Get the right plan first

An estimated plan (SET SHOWPLAN_ALL ON, or the "Display Estimated Execution Plan" button) tells you what the optimizer thinks will happen. An actual plan (SET STATISTICS PROFILE ON, or "Include Actual Execution Plan") tells you what actually happened, including real row counts at each step. For a performance problem that's already occurred, always get the actual plan — the gap between estimated and actual row counts is frequently the entire diagnosis by itself.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT c.CategoryName, SUM(f.SalesAmount) AS Revenue
FROM FactSales f
JOIN DimProduct p ON f.ProductKey = p.ProductKey
JOIN DimDate d ON f.DateKey = d.DateKey
JOIN DimCategory c ON p.CategoryKey = c.CategoryKey
WHERE d.CalendarYear = 2025
GROUP BY c.CategoryName;

STATISTICS IO is underused relative to how much it tells you — logical reads per table, straight in the messages tab, no plan-reading required. If one table's logical read count is absurdly higher than its row count would suggest, that's a scan where a seek should be happening, full stop, before you even open the graphical plan.

Reading the plan: right to left, and mind the arrows

Graphical plans read right to left, and the width of the arrows between operators is data volume flowing through, not time. A thick arrow coming out of a table access operator into a join is the first thing to look at — it tells you how many rows survived to that point, which is either expected or a sign that a filter that should have happened earlier didn't.

The specific operators to know on sight:

  • Clustered Index Seek / Index Seek — the engine used an index to jump directly to the rows it needed. This is what you want to see on your large fact and dimension tables for a selective query.
  • Clustered Index Scan / Table Scan — the engine read every row and filtered afterward. Not automatically bad — for a query that legitimately needs most of a table (a full-year aggregation over a fact table with mostly-relevant rows), a scan can be the right plan, and columnstore scans in particular are designed to be read efficiently in bulk. It's a problem specifically when the query is selective (a handful of rows expected) and the engine is still scanning, because that means there's no usable index for the predicate.
  • Key Lookup — a seek on a nonclustered index followed by a lookup back into the clustered index to fetch columns not covered by that index. One or two of these are fine; seeing a Key Lookup with a high row count next to it is a sign the nonclustered index isn't covering the query and an INCLUDE column would remove the extra hop.
  • Nested Loops Join — efficient when one side is small; becomes expensive when both sides are large, because it's evaluated once per outer row.
  • Hash Match — the optimizer's choice for joining or aggregating large, unsorted sets. Reasonable for big joins, but a Hash Match spilling to disk (visible in the plan's warning icon, or in the actual plan's "Number of Rows" vs the grant) means the memory grant was undersized for the actual data volume.
  • Sort — expensive, and a sort appearing where you didn't ask for an ORDER BY usually means it's servicing a downstream Merge Join or a GROUP BY that couldn't use an ordered index.

The warning signs worth actually stopping for

Hover tooltips and the plan XML carry more than the icons show. The ones I check every time:

  • Estimated rows vs. actual rows, wildly apart. This means statistics are stale, or a parameter-sensitive predicate got a bad estimate (parameter sniffing), and it cascades into every operator downstream picking a plan shape suited for the wrong row count.
  • A yellow warning triangle on a join or sort. Usually a spill to tempdb or an implicit conversion — both worth fixing, the second one often silently by a mismatched data type between a join column and a literal or parameter.
  • Implicit conversions on a join predicate. If ProductKey is INT on one side and gets compared to a parameter typed as VARCHAR from the application layer, SQL Server converts one side, which can make an otherwise-perfect index unusable for a seek. This shows up as a CONVERT_IMPLICIT in the predicate of a scan operator — easy to miss, expensive when present.
  • A scan on a table with millions of rows and no corresponding filter pushed down. Means the predicate isn't sargable — wrapped in a function, like WHERE YEAR(OrderDate) = 2025 instead of a range comparison — so the optimizer can't use an index on OrderDate even if one exists.
-- Not sargable: forces a scan even with an index on OrderDate
WHERE YEAR(OrderDate) = 2025

-- Sargable: can use an index seek/range scan on OrderDate
WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'

The performance killers specific to BI queries

A handful of patterns show up over and over in reporting workloads specifically, more than in general OLTP queries:

  • Implicit fan-out from an unintended many-to-many join. A dimension with an accidental duplicate key inflates a SUM silently and also inflates the row count through every downstream operator, which shows up in the plan as unexpectedly large intermediate row counts well before the final aggregation.
  • Non-sargable date filtering, as above — extremely common in BI queries because report filters are naturally date-range-shaped, and it's easy to write a convenient function-wrapped predicate instead of a range comparison.
  • Missing or non-covering indexes on foreign keys used for star-schema joins. A fact table's foreign key columns are exactly what every join in every report will filter and join on; if they're not indexed (or not included in a covering nonclustered index alongside the measures being aggregated), every report query pays for it.
  • Wide SELECT * pulls into a report dataset that then gets filtered client-side. Pulling far more columns and rows than the visual needs forces a wider covering index requirement or extra key lookups than a narrower, purpose-built query would need.
  • Row-by-row processing disguised as SQL — cursors or scalar UDFs applied per row inside an otherwise set-based query, which block the optimizer from using efficient set-based join and aggregation operators at all. A scalar UDF referenced in a WHERE clause is one of the most reliable ways to force a scan regardless of what indexes exist.
Before touching an index or rewriting a query, read the plan right to left and ask one question at each operator: is the row count flowing into this step roughly what I'd expect? The first place that answer is "no" is almost always where the actual problem lives, and everything downstream of it is just inheriting the damage.

None of this requires memorizing every operator SQL Server has. It requires knowing the five or six that show up constantly in reporting workloads, checking estimated against actual row counts first, and treating a scan as a question ("is this expected?") rather than an automatic verdict. Most BI-query slowness traces back to one of: a non-sargable predicate, a missing covering index on a fact table's join columns, stale statistics giving the optimizer a bad row estimate, or an accidental fan-out join — and the plan will show you which one it is if you read it in that order.