3

We are using SQL Server 2016. We have some problems in terms of queries duration on 2023-09-11. Would like to check it on QueryStore and I saw on query store executed on 2023-09-11 05:00 PM:

enter image description here

That we have much more execution counts on 2023-09-11 (around 1150 VS 800 on 2023-09-08). I run same Query Store check on 2023-09-12 and I see that execution count for yesterday is much less than I checked yesterday.

Do you know why we have such difference ? What is more I still see that execution count is still decreasing for 2023-09-11.

enter image description here

Query store settings have been added.enter image description here

Query execution 2023-09-14 9:52 AM:

enter image description here

Query execution 2023-09-14 10:53 AM: enter image description here

Query execution 2023-09-14 12:36 PM: Even 2023-09-11 has been changed...enter image description here

0

1 Answer 1

12
+50

Query Store Execution count in GUI maps to the column count_executions in sys.query_store_runtime_stats

Total count of executions for the query plan within the aggregation interval.

Since you're looking at the "Overall Resource Consumption," I've captured the underlying query being called with Extended Events, and it's roughly this:

SELECT
    CONVERT(float, SUM(rs.count_executions)) as total_count_executions,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) as total_duration,
    ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) as total_cpu_time,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) as total_logical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) as total_logical_io_writes,
    ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) as total_physical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2) as total_clr_time,
    ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0) as total_dop,
    ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) as total_query_max_used_memory,
    ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0) as total_rowcount,
    ROUND(CONVERT(float, SUM(rs.avg_log_bytes_used*rs.count_executions))*0.0009765625,2) as total_log_bytes_used,
    ROUND(CONVERT(float, SUM(rs.avg_tempdb_space_used*rs.count_executions))*8,2) as total_tempdb_space_used,
    DATEADD(d, ((DATEDIFF(d, 0, rs.last_execution_time))),0 ) as bucket_start,
    DATEADD(d, (1 + (DATEDIFF(d, 0, rs.last_execution_time))), 0) as bucket_end
FROM sys.query_store_runtime_stats rs
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY DATEDIFF(d, 0, rs.last_execution_time)

Since there is almost no filter apart from the date, there are only a couple of options left.

  1. Your statement didn't get called. Maybe there was no testing done that day, maybe your code has some branching logic, etc.

  2. Someone purged only that query from the Query store using sp_query_store_remove_query or a query plan with sp_query_store_remove_plan

  3. The Query Store was temporarily switched to read-only. You cannot check this after the fact, but there are options to monitor this with Extended events in the future if you suspect this is the case

EDIT

After more information has been presented, I'm inclined to point at the SIZE_BASED_CLEANUP_MODE

The documentation says:

1 = AUTO - size-based cleanup will be automatically activated when size on disk reaches 90 percent of max_storage_size_mb. This is the default configuration value.

Size-based cleanup removes the least expensive and oldest queries first. It stops when approximately 80 percent of max_storage_size_mb is reached.

This matches your screenshot of 79 MB out of 100 MB used (approximately 80 percent)

You can create an XE session to track this cleanup and make sure

CREATE EVENT SESSION [QueryStoreCleanup] ON SERVER 
ADD EVENT qds.query_store_size_retention_cleanup_finished,
ADD EVENT qds.query_store_size_retention_cleanup_started,
ADD EVENT qds.query_store_size_retention_cleanup_update
ADD TARGET package0.event_file
(
    SET filename=N'QueryStoreCleanup'
    , max_file_size = 5
    , max_rollover_files = 1 
)
GO

ALTER EVENT SESSION [QueryStoreCleanup] ON SERVER STATE = START

I've tested it on a query store with max size = 10 MB, and it ran roughly every 15 - 20 minutes.

enter image description here

I've run a bunch of dynamic SQL to get the Query Store to grow and captured the execution and plan count aggregates before and after the cleanup.

enter image description here

All my dynamic SQL was identically expensive, so the oldest plans were eliminated first.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.