If you’ve ever been to one of my sessions you know that I really like demos. I find they can illustrate how things work extremely well and I use them to compliment an explanation. I’m a very visual learner so it also helps me to understand and then explain a concept. The drawback of demos is that they sometimes fail, partly or entirely. I really work to make mine as infallible as possible, but every so often I run into a problem.
When I was working on Query Store demos, one thing I noticed is that sometimes the data from the runtime stats system view seems to be duplicated. And this happens in my Pluralsight course on Query Store and Automatic Tuning, funny enough. After restoring a database and stepping through a blocking scenario, when I query the runtime and wait stats with the query below, I get the following output:
SELECT [rs].[runtime_stats_interval_id], [rsi].[start_time] AS [IntervalStartTime], [rsi].[end_time] AS [IntervalEndTime], [qsq].[query_id], [qst].[query_sql_text], [qsp].[plan_id], [rs].[count_executions], [rs].[avg_duration], [rs].[avg_logical_io_reads], [ws].[wait_category_desc], [ws].[total_query_wait_time_ms] FROM [sys].[query_store_query] [qsq] JOIN [sys].[query_store_query_text] [qst] ON [qsq].[query_text_id] = [qst].[query_text_id] JOIN [sys].[query_store_plan] [qsp] ON [qsq].[query_id] = [qsp].[query_id] JOIN [sys].[query_store_runtime_stats] [rs] ON [qsp].[plan_id] = [rs].[plan_id] JOIN [sys].[query_store_runtime_stats_interval] [rsi] ON [rs].[runtime_stats_interval_id] = [rsi].[runtime_stats_interval_id] JOIN [sys].[query_store_wait_stats] ws ON [qsp].[plan_id] = [ws].[plan_id] AND [rsi].[runtime_stats_interval_id] = [ws].[runtime_stats_interval_id] WHERE [qst].[query_sql_text] LIKE '%UPDATE%' AND [rs].[execution_type] = 0 ORDER BY [rsi].[start_time] ASC, [ws].[total_query_wait_time_ms] DESC; GO
You can see that there are two rows for the same query_id and plan_id, but the count_executions is different, as are the avg_duration and avg_logical_io_reads values . The data is not truly a duplicate. This behavior occurs because the Query Store data is stored in memory before it is flushed to disk, and when you query the data SQL Server is pulling it from both locations (and doing a UNION ALL) and displaying it in the output. If I waited a bit and ran the query again, the two rows for that interval would probably disappear – most likely because the in memory data had been flushed to disk. You can force the data to be flushed manually using sp_query_store_flush_db:
EXEC sp_query_store_flush_db; GO
After I execute that stored procedure, when I query the runtime and stats data again, the output is only one line:
The Query Store data is held in memory as an optimization – if the runtime stats had to be updated on disk every time a query executed, the amount of I/O could easily overload a high-volume system. Therefore, data is stored in memory and flushed to disk based on the DATA_FLUSH_INTERVAL_SECONDS setting for Query Store, which defaults to 15 minutes. I recommend reading How Query Store Collects Data for more detail (and a nice visual). It’s important to understand that even though I keep writing “in memory” and the documentation states that the sp_query_store_flush_db procedure “flushes the in-memory portion of the Query Store data to disk”, this data does not reside in In-Memory OLTP structures.
Note: You can use Query Store to capture data for Natively Compiled stored procedures that access In-Memory OLTP objects, but you need to enable the collection of execution statistics using sys.sp_xtp_control_proc_exec_stats, as described in Microsoft Docs.
If you’re using Query Store and you’ve run into similar behavior, hopefully this explains what you’ve seen and why, and you know how to address it going forward, if it presents an issue for some reason.
Lastly, just yesterday the Tiger Team released some code you may need to run if you’re using Query Store and you’ve upgraded to SQL Server 2017 CU3. Please take a minute to review in case it applies to you!