How to View Query Execution History Over Time in SQL Server

by

In SQL Server, Query Store aggregates performance statistics into time intervals, allowing you to see how query performance changes over time. This historical view helps you identify when performance degraded, spot patterns like daily or hourly spikes, and correlate performance changes with deployments or data changes.

This assumes that Query Store is enabled on the database in question. So assuming Query Store is enabled on the database, you can use the following queries to check the execution history of queries over time.

Finding the Query ID

To get the execution history of a given query, you’ll need the query ID. You can use the following query to get that:

SELECT 
    q.query_id,
    qt.query_sql_text,
    q.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE qt.query_sql_text LIKE '%your_table_name%'
ORDER BY q.last_execution_time DESC;

In this example I used WHERE qt.query_sql_text LIKE '%your_table_name%', but you’ll need to replace your_table_name with text or a table name that you know is in the query. You’ll need to adjust the filter if it returns too many results.

Once you identify the right query, you can use its query_id in the following queries.

Viewing Execution History

To view execution history for a specific query across time intervals:

DECLARE @query_id INT = 123;  -- Replace with your query_id

SELECT 
    rsi.start_time,
    rsi.end_time,
    p.plan_id,
    rs.count_executions,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    rs.avg_logical_io_reads AS avg_logical_reads,
    rs.avg_physical_io_reads AS avg_physical_reads
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE p.query_id = @query_id
ORDER BY rsi.start_time, p.plan_id;

This shows performance metrics broken down by Query Store’s configured aggregation intervals (default is 60 minutes), allowing you to see exactly when performance changed.

Viewing History for a Specific Time Range

To focus on a particular time period:

DECLARE @query_id INT = 123;

SELECT 
    rsi.start_time,
    rsi.end_time,
    p.plan_id,
    rs.count_executions,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    rs.avg_logical_io_reads AS avg_logical_reads
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE p.query_id = @query_id
  AND rsi.start_time >= '2025-12-01'
  AND rsi.end_time <= '2025-12-08'
ORDER BY rsi.start_time, p.plan_id;

This narrows the results to a specific date range for more focused analysis.

Identifying When Performance Changed

To spot when performance degraded or improved:

DECLARE @query_id INT = 123;

SELECT 
    rsi.start_time,
    rsi.end_time,
    p.plan_id,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    LAG(rs.avg_duration) OVER (ORDER BY rsi.start_time) / 1000.0 AS previous_avg_duration_ms,
    ((rs.avg_duration - LAG(rs.avg_duration) OVER (ORDER BY rsi.start_time)) 
        / NULLIF(LAG(rs.avg_duration) OVER (ORDER BY rsi.start_time), 0) * 100) AS percent_change
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE p.query_id = @query_id
ORDER BY rsi.start_time;

The percent_change column shows how much performance changed from one interval to the next, making it easy to identify sudden regressions or improvements.

Viewing Execution Patterns

To understand when a query runs most frequently:

DECLARE @query_id INT = 123;

SELECT 
    DATEPART(hour, rsi.start_time) AS hour_of_day,
    AVG(rs.count_executions) AS avg_executions_per_interval,
    AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE p.query_id = @query_id
GROUP BY DATEPART(hour, rsi.start_time)
ORDER BY hour_of_day;

This shows execution patterns by hour of day, helping you understand workload distribution and identify peak usage times.

Comparing Multiple Plans Over Time

When a query has multiple plans, you can see when each plan was active:

DECLARE @query_id INT = 123;

SELECT 
    rsi.start_time,
    rsi.end_time,
    p.plan_id,
    p.query_plan_hash,
    rs.count_executions,
    rs.avg_duration / 1000.0 AS avg_duration_ms
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE p.query_id = @query_id
ORDER BY rsi.start_time, p.plan_id;

When you see the plan_id or query_plan_hash change between intervals, it indicates when SQL Server started using a different execution plan, which often correlates with performance changes.

Understanding Statistics Intervals

Query Store aggregates statistics based on the INTERVAL_LENGTH_MINUTES setting (default is 60 minutes). Each interval captures:

  • Execution counts
  • Average, minimum, and maximum values for duration, CPU, reads, and writes
  • Standard deviation for key metrics

Shorter intervals (15-30 minutes) provide more granular data but consume more storage. Longer intervals (2-4 hours) reduce storage overhead but make it harder to pinpoint exactly when issues occurred. You can change this with the ALTER TABLE statement.

Here are examples of changing the INTERVAL_LENGTH_MINUTES setting:

-- Change to 15-minute intervals for more granular tracking
ALTER DATABASE YourDatabaseName
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

-- Change to 30-minute intervals
ALTER DATABASE YourDatabaseName
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 30);

-- Change to 2-hour intervals to reduce storage overhead
ALTER DATABASE YourDatabaseName
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 120);

-- Verify the current setting
SELECT interval_length_minutes
FROM sys.database_query_store_options;

Note that changing this setting only affects new intervals going forward (it doesn’t retroactively change how historical data was aggregated). Existing intervals remain at whatever length was configured when they were created.