A SQL Server performance problem is not always still there when someone finally has time to investigate it. SQL Server Query Store becomes especially useful when the system has already returned to normal.
Users may report that an application was slow around 10:00. An hour later, response times are back to normal. CPU usage looks ordinary and there is no obvious blocking. The query mentioned by the application team now finishes without anything particularly suspicious in its execution plan.
That is a common difficulty in performance troubleshooting: the system you are looking at now may no longer be the system users experienced during the incident.
At that point, a current execution plan or a snapshot of server activity can still be useful, but it cannot tell the whole story. I want to know what happened around 10:00, not only what SQL Server looks like at 11:00.
Query Store keeps historical information about queries, execution plans and runtime behavior. That means an investigation does not have to rely entirely on the state of the database after the problem has disappeared.
Start with when the problem happened
When I have a reasonably clear incident window, that is usually where I start.
If the application team tells me that response times became poor between 09:45 and 10:15, I start with that period. I can then compare the affected queries with their behavior before or afterwards.
I am not immediately looking for the most expensive query in the database.
A query that consumes a lot of CPU every day may be completely unrelated to the incident. A query that normally gets little attention can suddenly become interesting. Its duration or execution pattern may have changed during exactly the period users reported a problem.
The first comparison is therefore fairly simple.
Did the affected query actually become slower around that time? Was another execution plan used? Did the query execute much more often than usual? And does the same behavior appear at other times as well?
Those answers do not give me a diagnosis yet, but they help narrow the investigation.
A different execution plan is a clue
One of the things Query Store makes easy to see is whether a query has used different execution plans over time.
If a query usually runs with one plan and performed much worse while another plan was active, that deserves attention. It still does not mean that the second plan explains everything that happened.
The workload may have been different. Parameter values may have changed. Data distribution may have influenced the optimizer. There may also have been resource pressure elsewhere on the server at the same time.
This is why I am careful with the jump from “different plan” to “bad plan”.
Sometimes the plan really is the immediate problem, and forcing a previously known plan can be a reasonable way to stabilize an important workload. During an incident, restoring acceptable service may be more important than understanding every detail immediately.
Afterwards, however, I still want to know why the behavior changed in the first place.
Otherwise, I have fixed the symptom without learning much from the incident.
SQL Server Query Store puts execution plans into context
The execution plan is only one part of the picture.
What I find more useful during a retrospective investigation is comparing the query across different periods. If duration increased sharply for half an hour and then returned to normal, I have a much narrower problem to investigate.
The same applies when the pattern repeats.
Perhaps the query becomes slower every morning around the same time. That immediately raises different questions. Is there a batch process running? Does the application generate a different workload during that period? Is data being imported? Is maintenance running somewhere in the environment?
This becomes particularly relevant with ETL and analytical workloads, where growing processing windows can gradually begin to overlap with reporting or other activity. I look at that scenario in more detail in SQL Server performance for data warehouse workloads.
The same pattern can appear across broader SQL Server Business Intelligence workloads, where ETL, semantic model processing and reporting may depend on the same database platform at different times of the day. Power BI is one specific example: a report or DirectQuery workload may perform normally most of the day but become slow when query patterns, concurrency or resource pressure change on the SQL Server side. The SQL Server performance for Power BI workloads page looks at that relationship in more detail.
Query Store will not answer all of those questions.
What it can do is show that the change happened and help define the period I need to investigate elsewhere.
That becomes particularly useful when I compare the Query Store history with monitoring data from SQL Server, the operating system, storage or the virtualization platform. If several independent sources show a change during the same period, the investigation becomes much more focused.
This also prevents one of the easier mistakes in troubleshooting: finding an interesting SQL Server observation and trying to make the entire incident fit around it. I discussed that problem in more detail in SQL Server Bottleneck Triage: How to Separate Signals from Causes.
Historical data has to exist before you need it
There is a practical operational consequence to all of this.
If Query Store is supposed to help with incidents that happened yesterday or during a short period overnight, it needs to retain useful data for long enough to make that possible.
That sounds obvious, but it is something I would rather decide while the system is healthy than discover during an investigation.
Capture settings, storage limits, retention and the workload itself influence how useful the available history will be. There is no single configuration that fits every database. This should therefore be an intentional operational decision rather than something left untouched indefinitely.
For an important workload, I want to know roughly how far back I can investigate. I also want to know whether the data being collected is useful for the type of incidents that matter to that system.
SQL Server Query Store does not replace monitoring or the rest of SQL Server troubleshooting. It gives me another perspective that becomes particularly useful once the immediate symptoms have disappeared.
When someone tells me that the application was slow an hour ago, the first question is no longer only what SQL Server is doing now.
I want to understand what was different when users actually noticed the problem.
