Understanding findings
Findings are the heart of QPI. Everything else helps you explore a plan. The findings tell you what is wrong with it.
What a finding is
A finding is the output of one rule that looked at your plan and recognised a pattern worth flagging. The rules are not a generic “this operator looks expensive” highlight. They encode the kind of judgement an experienced DBA applies: a key lookup feeding off thousands of seeks, statistics that have drifted past the point of trust, a sort that spilled because the grant was sized off a low estimate. QPI ships a substantial pack of these rules covering estimates, statistics, memory grants and spills, hash joins, parameter sniffing, indexing, plan-cache health, and more.
Every finding opens to the same three-part shape:
- Evidence: the exact numbers from the plan that set the rule off. No hand-waving; you can see the values it judged on.
- Why this sucks: the mechanism. What is actually going wrong inside SQL Server and why it costs you.
- Potential action points: concrete next steps, often with the precise T-SQL to run, and just as often with a warning about testing it first.
When you are connected to the live server, many findings also carry a Live evidence block that backs the plan-based reasoning with current numbers from the server. See Connecting to a live server.
Severity
Each finding carries a severity so you can triage at a glance.
| Icon | Severity | What it means |
|---|---|---|
| 🔴 | Critical | Strong evidence this is hurting the query now. Start here. |
| 🟡 | Warning | A real problem or a clear risk. Worth your time. |
| 🔵 | Info | Useful context that shapes how you read the rest. |
Severity is derived from the plan, not fixed per rule. The same rule can fire as a warning on one plan and critical on another depending on how bad the numbers are.
Four findings, end to end
You do not need to learn every rule to get value from QPI. Here are four that show the range, and how the plan-based reasoning and the live evidence fit together.
Key lookup
When a query seeks a nonclustered index but then has to go back to the base table for columns the index does not cover, you get a key lookup. One or two are fine. Thousands of them, once per row, is a query quietly doing far more work than it should.
QPI flags the lookup and points at the operator in the plan. On its own that is already a useful nudge. Connect to the server and it gets sharper: the live evidence pulls the real seek, scan, and update counts for the index in question. If that index has served thousands of seeks and taken zero updates, QPI will say so, because that is the case where adding the missing columns as INCLUDEs is almost free. The write overhead everyone worries about is not there. The decision stops being a judgement call and becomes a reading of the numbers.
Stale statistics
This is the finding that best shows what live data does for you.
From the plan alone, QPI can already see how many row modifications have piled up against a statistics object since it was last sampled, and how close that is to the threshold where SQL Server would auto-update it. If you have changed a large fraction of a table since the histogram was built, the optimizer is estimating from a picture that no longer matches reality, and bad estimates cascade into bad join choices, oversized or undersized memory grants, and wrong parallelism decisions.
That reasoning is sound, but it is still working from the plan’s own view of the world. Connect to the server and the finding measures the drift directly. It compares what the statistics believe against the live row count of the table right now and reports the gap as a percentage. Instead of “these stats may be getting stale”, you get something like “the table has grown to 4.2 million rows, the stats were built on 900 thousand, that is a +367% drift”.
The action points are specific too: the exact UPDATE STATISTICS command for that object, a note on whether the last sample rate was low enough to worry about, and a reminder to test a FULLSCAN in dev first because it takes a schema-stability lock and forces recompiles.
Missing index
When the optimizer wishes an index existed, it records the suggestion in the plan. The built-in graphical plan shows you one of these, in green, even when the plan actually contains several.
QPI does two things the built-in viewer does not. First, it shows you all the distinct suggestions, not just the first one. Second, SQL Server emits one suggestion per query reference rather than per index, so the same index can appear several times with different impact scores. QPI consolidates those into a single suggestion and tells you it did, so you do not go and create the same index three times. Each suggestion comes with a Script button that copies a ready-to-run CREATE INDEX statement.
If you are connected to the server, this finding can grow an extra layer: the server’s own missing-index DMVs record how broadly an index has been wanted across the whole plan cache, not just by this one query. When that data is available and lines up with the plan’s suggestion, QPI shows the server-wide average impact and how many times the index has been wanted since the last restart, which is the difference between a one-off recommendation and a pattern. This live layer depends on the server still holding a matching entry and on the connecting account having the right permissions, so treat it as a bonus on top of the always-present plan-based suggestion rather than something you will see every time.
Spill to tempdb
A sort or a hash that runs out of its memory grant has to finish the job on disk, in tempdb, which is orders of magnitude slower than doing it in memory. On an actual plan QPI catches the spill, reports how deep it was, and, when the spilling operator ate a large share of the statement’s elapsed time, escalates the severity accordingly.
The valuable part is that QPI tells you the likely cause rather than just the symptom. A memory grant is sized from the optimizer’s row estimates. If the operator was estimated at a few thousand rows but processed millions, the grant was always going to be too small, and QPI says exactly that with the numbers: “estimated 4 thousand, actual 2.1 million, 525 times more than expected”. The fix it points you to is the upstream estimate (the stale stats, the parameter sniffing) rather than the tempting but wrong instinct to start tuning tempdb or server memory. If the plan also shows parameter sniffing or relevant waits, the action points fold those in.
The takeaway
Read the findings first. Use the tabs and the diagram to confirm and explore what they tell you. Connect to the server when you want the reasoning backed by today’s numbers instead of compile-time guesses. That is the whole workflow, and it is faster than reading a plan by eye every time.