SQL Server execution plans are often the first place I look when a query behaves differently from what I would expect. They tell me how SQL Server chose to access the data, which join strategies it selected and where estimates may not match what actually happened during execution. An actual plan can add runtime information as well, so there is usually a lot to work with.
The problem starts when people treat the plan as if it were the complete explanation for a performance incident.
Suppose users report that an application was slow around 10:00. Someone investigates an hour later, runs the query again and captures an actual execution plan. The query is fast now. The plan looks reasonable. There may still be useful clues in it, but we are already looking at a different execution under different conditions. If I want to understand what happened at 10:00, the plan captured at 11:00 is only one part of the evidence.
That distinction sounds obvious, but it matters because execution plans are very persuasive. They are visual, detailed and concrete. It is easy to find an operator that looks suspicious and start tuning from there, even when we have not yet established whether that operator had anything to do with the problem users experienced.
What SQL Server execution plans can tell you
An execution plan describes the strategy SQL Server selected for a statement. The optimizer makes that decision using the information available at compile time: statistics, indexes, cardinality estimates, parameter values and other conditions that can influence the plan.
An estimated plan shows that compiled strategy without running the query. An actual execution plan adds information from the execution itself, including actual row counts and, depending on the plan and SQL Server version, other runtime details. Microsoft explains the distinction in its documentation on displaying and saving execution plans.
For troubleshooting, I usually prefer the actual plan because I can compare what SQL Server expected with what it really processed. If SQL Server expected a few rows but processed several hundred thousand instead, that is worth investigating. The mismatch may affect join choices, memory requirements or the amount of work performed further up the plan.
But the difference itself is not yet a diagnosis. I still need to know whether it helps explain the slow execution I am investigating.
Parameter-sensitive workloads change the picture
This becomes especially relevant with parameter-sensitive workloads. A stored procedure may use the same cached plan for one parameter value that returns ten rows and another that returns several hundred thousand. The plan shape can remain almost unchanged while the actual workload is very different. Data distribution can make that difference larger, and so can changes in the surrounding workload. A plan that works perfectly well for one execution can be much less suitable for another without looking obviously broken when viewed in isolation.
The same caution applies in the other direction. A scan, a warning or a high estimated cost may look uncomfortable, but none of those things automatically explains the incident. A scan against a small table may be entirely appropriate. An expensive operation that runs once at night may matter less than a small query executed thousands of times during business hours.
What I am trying to establish is not whether the plan contains something that could be improved. On a sufficiently busy SQL Server, I can usually find something. I want to know whether the plan explains the behavior that actually caused the problem.
The surrounding workload changes what the plan means
A query can use the same execution plan and still take very different amounts of time.
Another transaction may block it. Storage latency may increase. CPU pressure can come from unrelated activity, while several normally harmless queries may start running at the same time and compete for the same resources. None of that necessarily changes the plan shape.
This is one reason I do not like looking at SQL Server execution plans completely on their own during a production incident. The plan shows what SQL Server intended to do with the query, but elapsed time also includes waiting. If the query spent most of that time waiting for a lock or for storage, tuning an operator may solve the wrong problem.
Wait statistics can help here because they add another view of what SQL Server was waiting for. They have the same limitation, though: they are evidence, not a diagnosis on their own. I wrote about that in SQL Server Wait Statistics Are Not a Diagnosis.
This is also where the timing of the investigation becomes important. If I have an actual plan from the affected execution, together with runtime metrics and information about blocking or resource pressure at the same time, the pieces can start to support each other. If I only have a plan captured later, I have to be much more careful about what I conclude from it.
That is often the difference between finding a plausible tuning opportunity and actually explaining an incident.
When SQL Server execution plans need history
Teams often investigate SQL Server performance problems after the interesting part has already happened.
The application was slow earlier in the morning. Users complained, perhaps monitoring raised an alert, and by the time someone starts looking closely everything appears normal again. CPU is back at its usual level, the blocking chain has disappeared and the query that supposedly caused trouble now finishes quickly.
At that point, current SQL Server execution plans can tell me how the query behaves now. What I really need is a way to compare the current behavior with the period where the problem existed.
Looking back with Query Store
Query Store helps with exactly that. It keeps query texts, execution plans and aggregated runtime statistics over time, which makes it possible to look back instead of relying only on what I can reproduce at the moment of investigation. Microsoft describes the collection process in How Query Store Collects Data.
I may find that the query changed plans shortly before duration increased. That does not prove the new plan caused the regression, but it gives me something concrete to investigate. I can compare both plans, look at the affected runtime intervals and see whether the timing fits.
Sometimes the more interesting finding is that the plan did not change at all.
If Query Store shows the same plan before, during and after the incident, forcing a different plan becomes much harder to justify. I would start looking more closely at the parameter values used during the slow period, concurrency, blocking or resource pressure. The unchanged plan does not tell me what the cause was, but it removes one attractive explanation from the top of the list.
That historical perspective is the reason I consider Query Store so useful in production troubleshooting. I covered that in more detail in SQL Server Query Store: Why Performance History Matters.
Without history, it is very easy to compare a bad user experience from 10:00 with a perfectly healthy execution from 11:00 and assume both represent the same situation.
They may not.
Before I change production, I want the story to make sense
A performance investigation eventually reaches the point where someone wants to change something. Maybe an index is missing. Maybe a different plan would work better. Perhaps a query needs to be rewritten, or a temporary mitigation is required because the application is already affected.
During an active incident, restoring service can take priority. There are situations where I will make a reversible operational change before I understand every detail of the root cause. That is different from deciding what the permanent fix should be.
Permanent tuning changes have consequences. An index helps reads but also affects writes and consumes storage. A hint limits the optimizer’s choices. A forced plan can stabilize a regression and later become a problem itself when the data or workload changes.
Before I make that kind of change, I want the evidence to fit together reasonably well. The plan should match the execution I am trying to explain, and the runtime behavior should support the hypothesis. Timing matters too. If historical data is available, I also want to know whether the behavior was unusual or simply normal workload seen at an inconvenient moment.
That does not require perfect certainty. Production troubleshooting rarely gives us that.
It does require enough context that I can explain why I expect the change to address the actual problem rather than merely improve something that looked suspicious in the plan.
For me, that is where SQL Server execution plans are most useful. They narrow the investigation, show me where SQL Server’s decisions deserve a closer look and give me something concrete to compare against runtime behavior and history.
They are strong evidence, but rarely the whole story.
Foto von David Kristianto auf Unsplash
