SQL Server Query Store Troubleshooting: Practical DBA Scenarios and T-SQL Examples
A hands-on guide for SQL Server DBAs investigating slow queries, plan regressions, CPU pressure, missing-index warnings, and CLR execution time.
SQL Server Query Store Performance Tuning DBA Guide
Download the file- Query Store Scenarios
SQL Server Query Store captures query text, execution plans, and runtime performance statistics over time. It helps database administrators compare plans, investigate performance changes, and identify queries that deserve deeper analysis. Instead of relying only on the plan currently in cache, a DBA can use Query Store history to understand how a query behaved during different time periods.
- Investigate intermittent slow queries and parameter sniffing
- Compare performance before and after a database upgrade
- Find queries associated with high CPU usage
- Find query plans with missing-index warnings
- Identify queries with long compile times
- Review recent query performance and plan details
- Measure CLR time reported by Query Store
- Practical cautions and DBA checklist
1. Investigating Intermittent Slow Queries: Parameter Sniffing
Parameter sniffing occurs when SQL Server compiles a parameterized statement using parameter values available at compilation time. The resulting plan is cached and may later be reused for different values. If data distribution is skewed, one plan may not perform well for every parameter value.
For example, a table might contain one row for most IDs but thousands of rows for a particular ID. An index seek may work well for a selective value, while a scan or a different access strategy may be more efficient for a value that returns many rows. The correct choice depends on the query, indexes, statistics, row counts, and overall plan—not on a fixed rule that scans are always better for large results.
Example setup
Use only in a disposable test database. This demonstration creates a sample table and stored procedure. It drops an existing table with the same name, so do not run it in a production database.
-- TEST ENVIRONMENT ONLY: this drops the table if it exists.
DROP TABLE IF EXISTS dbo.large_table;
GO
CREATE TABLE dbo.large_table
(
id int,
name varchar(30),
c3 binary(2000) NULL
);
GO
SET NOCOUNT ON;
DECLARE @i int = 1;
WHILE @i < 10000
BEGIN
INSERT INTO dbo.large_table (id, name)
VALUES (@i, 'test');
SET @i += 1;
END;
GO
-- Add many rows for one value to create skewed data.
INSERT INTO dbo.large_table (id, name)
VALUES (100001, 'test');
GO 10000
CREATE INDEX IX_large_table_id
ON dbo.large_table(id);
GO
CREATE OR ALTER PROCEDURE dbo.test_proc (@i int)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM dbo.large_table
WHERE id = @i;
END;
GOExecute the procedure with different parameter values and compare the actual execution plans and elapsed time:
EXEC dbo.test_proc @i = 10;
EXEC dbo.test_proc @i = 100001;
GOThe first execution can influence the cached plan used by later executions. However, the exact plan selected depends on SQL Server version, compatibility level, statistics, optimizer decisions, and database configuration. Reproduce and measure the behavior in your own environment rather than assuming a particular plan will always appear.
Step 1: Find the query in Query Store
SELECT query_text_id, query_sql_text
FROM sys.query_store_query_text
WHERE query_sql_text LIKE '%large_table%';
Use the returned query_text_id to locate the associated query:
SELECT query_id, query_text_id, object_id
FROM sys.query_store_query
WHERE query_text_id = <query_text_id>;
Replace the placeholder with the actual ID returned by the first query.
Step 2: Compare plans for the query
SELECT
q.query_id,
OBJECT_NAME(q.object_id) AS containing_object,
qt.query_sql_text,
p.plan_id,
p.is_forced_plan,
p.last_compile_start_time,
p.last_execution_time,
TRY_CONVERT(xml, p.query_plan) AS query_plan_xml
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
WHERE qt.query_sql_text LIKE '%large_table%'
ORDER BY q.query_id, p.plan_id;
Compare the plans and their runtime history. Check logical reads, duration, CPU, execution counts, and the parameter values or cardinality assumptions visible in the plan when available. A plan that is good for one parameter may be poor for another, but multiple plans alone do not prove parameter sniffing.
Step 3: Force a known-good plan only after validation
EXEC sys.sp_query_store_force_plan
@query_id = <query_id>,
@plan_id = <plan_id>;
EXEC sys.sp_query_store_unforce_plan
@query_id = <query_id>,
@plan_id = <plan_id>;In SQL Server Management Studio, open the database's Query Store reports, review tracked or regressed queries, compare available plans, and use Force Plan only after verifying that the selected plan is consistently appropriate. UI labels vary by SSMS version.
2. Investigating Regressions After a Database Upgrade
Query Store can help identify queries whose performance changed after an upgrade, compatibility-level change, service pack, or cumulative update. The most useful comparison is between a workload baseline and the same workload after the change.
- Confirm Query Store is enabled and retaining sufficient history before the change.
- Run representative application workloads and record a baseline.
- Perform the planned upgrade or configuration change.
- Run the same or comparable workload after the change.
- Review Query Store's Regressed Queries report and compare plans and runtime statistics.
- Validate any proposed fix against representative workload and application requirements.
If Query Store was enabled only after the upgrade, it may not contain the earlier baseline. Also check whether the database compatibility level changed; an engine upgrade and a compatibility-level change are separate factors that can affect query optimization.
3. Finding Queries Associated with High CPU Usage
The following query ranks Query Store queries by estimated total CPU time over runtime-statistics intervals that overlap the last hour. Query Store stores runtime statistics in intervals, so the result is an interval-based approximation rather than a precise live-session measurement. CPU values in Query Store are reported in microseconds.
DECLARE @SinceUtc datetimeoffset =
DATEADD(hour, -1, SYSUTCDATETIME());
WITH AggregatedCPU AS
(
SELECT
q.query_id,
SUM(CONVERT(float, rs.count_executions)
* rs.avg_cpu_time) AS total_cpu_time,
COUNT(DISTINCT p.plan_id) AS number_of_plans
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id =
rs.runtime_stats_interval_id
WHERE rsi.end_time >= @SinceUtc
AND rs.execution_type_desc = 'Regular'
GROUP BY q.query_id
)
SELECT TOP (25)
q.query_id,
OBJECT_NAME(q.object_id) AS containing_object,
qt.query_sql_text,
a.total_cpu_time,
a.number_of_plans
FROM AggregatedCPU AS a
JOIN sys.query_store_query AS q
ON q.query_id = a.query_id
JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
ORDER BY a.total_cpu_time DESC;
Use the results to prioritize investigation, then inspect the plans and query-level runtime statistics. High total CPU can be caused by a query that runs many times, a query that consumes substantial CPU per execution, or both. For current active requests, also inspect DMVs such as sys.dm_exec_requests; Query Store is primarily historical and aggregated.
Disk I/O and memory pressure
Query Store runtime statistics can help identify expensive queries, but Query Store alone does not provide a complete diagnosis of server-wide disk or memory pressure. For I/O-related query analysis, compare logical reads, physical reads where available in the relevant SQL Server version, execution count, and plan operators. For memory issues, inspect actual plans for memory grants and spills and use appropriate DMVs, Extended Events, and operating-system monitoring. Confirm the columns supported by your SQL Server version before adapting a query.
4. Finding Query Plans with Missing-Index Warnings
Execution plans may contain missing-index recommendations. The following example searches recent Query Store plans for a missing-index element and returns the estimated impact value recorded in the plan.
SELECT
qt.query_sql_text,
TRY_CONVERT(xml, p.query_plan).value(
'declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/
QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]',
'decimal(18,4)') AS estimated_missing_index_impact,
q.query_id,
p.plan_id,
rs.last_execution_time,
rs.avg_duration,
TRY_CONVERT(xml, p.query_plan) AS query_plan_xml
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
WHERE rs.last_execution_time >=
DATEADD(hour, -1, SYSUTCDATETIME())
AND TRY_CONVERT(xml, p.query_plan).exist(
'declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/
QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex') = 1
ORDER BY rs.avg_duration DESC;
5. Finding Queries with Long Compile Times
Compilation overhead can matter when queries compile frequently or have complex optimization requirements. The following query lists plans with high average compile duration among queries executed recently.
SELECT TOP (10)
qt.query_sql_text,
q.query_id,
p.plan_id,
p.avg_compile_duration,
rs.last_execution_time,
rs.avg_duration
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
WHERE rs.last_execution_time >=
DATEADD(hour, -1, SYSUTCDATETIME())
ORDER BY p.avg_compile_duration DESC;
Interpretation: this is a screening query, not proof that compilation caused the application's delay. Query Store's compile-duration data is associated with plans and may not represent a compile event for every execution. Correlate the result with compilation frequency, recompilation events, query complexity, parameterization, and observed application latency. Confirm that the relevant columns are available in your SQL Server version.
6. Reviewing Recent Query Plans and Runtime Statistics
Use this query to review recent Query Store activity, including the query text, plan ID, execution timing, and runtime metrics available in the view.
SELECT TOP (100)
qt.query_sql_text,
q.query_id,
p.plan_id,
p.is_forced_plan,
rs.last_execution_time,
rs.count_executions,
rs.avg_duration,
rs.max_duration,
rs.avg_cpu_time,
rs.max_cpu_time,
rs.avg_logical_io_reads,
rs.max_logical_io_reads,
TRY_CONVERT(xml, p.query_plan) AS query_plan_xml
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
ORDER BY rs.last_execution_time DESC;
Query Store view columns differ across SQL Server releases. If a column is not available in your version, remove it or consult the documentation for that version. Runtime statistics are stored by interval and may produce multiple rows per plan; aggregate them appropriately when comparing periods.
7. Measuring Time Reported for CLR Queries
Suppose an UPDATE statement calls a user-defined function that invokes a SQL CLR assembly. Query Store runtime statistics may expose CLR timing metrics for supported SQL Server versions. For example:
-- Example only: use the actual table and function in your environment.
UPDATE dbo.Account_tbl_ENC
SET AcctNumber_RNDL = dbo.TryEncrypt(AcctNumber, 'None');
To review CLR-related metrics recorded in Query Store:
SELECT TOP (100)
qt.query_sql_text,
q.query_id,
p.plan_id,
rs.last_execution_time,
rs.avg_clr_time,
rs.max_clr_time,
rs.avg_cpu_time,
rs.max_cpu_time,
rs.avg_duration,
rs.max_duration,
TRY_CONVERT(xml, p.query_plan) AS query_plan_xml
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
ORDER BY rs.last_execution_time DESC;
Review the CLR time alongside total duration and CPU time. The values are Query Store runtime statistics, not a per-line profiler trace. If the CLR metrics are unavailable in your SQL Server version or environment, verify the supported schema and use suitable profiling or Extended Events methods for deeper analysis.
8. Practical DBA Checklist
- Verify Query Store is enabled, collecting data, and retaining the period you need.
- Use a representative time window and account for UTC timestamps in the examples.
- Compare query text, plan IDs, execution counts, CPU, duration, and I/O—not only one metric.
- Inspect actual execution plans and validate findings with application owners.
- Test plan forcing and index changes in a safe environment before production use.
- Capture a performance baseline before upgrades or compatibility-level changes.
- Remember that Query Store aggregates historical data; use live DMVs and other monitoring tools for current blocking and active-request investigations.
- Check the SQL Server version and Query Store feature availability before running scripts.
Conclusion
Query Store is a valuable tool for SQL Server performance troubleshooting. It helps DBAs investigate plan changes, find expensive queries, review missing-index warnings, examine compile-time metrics, and understand CLR timing where supported. The best results come from combining Query Store history with execution-plan analysis, live monitoring, workload knowledge, and controlled testing.
Do not force a plan or add an index solely because a query appears in a report. Confirm the root cause, measure the change, and monitor the workload after implementation.
Disclaimer: The scripts are examples for learning and troubleshooting. Validate them against your SQL Server version, schema, workload, permissions, and operational standards before production use. Performance outcomes are environment-dependent; no script, index recommendation, or forced plan is guaranteed to improve performance.
