Demonstrating Scenarios Where Query Store Does Not Capture Runtime Statistics for Unfinished Queries

3

When Query Store is used to troubleshoot performance issues, it is helpful to understand how query runtime statistics are captured. According to Public document: When a query runs, runtime statistics are sent to Query Store..

I wonder if Query Store still captures execution statistics in certain scenarios. So in this blog post we will test two scenarios: Azure SQL Database:

1. Depending on whether Query Store captures runtime statistics for queries that complete within an uncommitted transaction, the scaling process may cause the transaction to be rolled back.


2. Whether Query Store captures runtime statistics for queries that are blocked and do not complete before a disruptive event (such as a service tier expansion) causes the connection to be lost.

 

Scenario testing

prerequisites

• Ensure that Query Store Capture Mode is set to All. (Make sure you capture all the queries that need to be captured.)

 

 

 

Scenario #1 – After a query completes within an uncommitted transaction, the extension process causes the transaction to be rolled back.

• Executing query with uncommitted transaction

 

Start trading TOP selection (1000) [id],[col1],[col2]
at [dbo].[Table1]
Wait for delay ’02:00′

 

 

 

• Check the runtime statistics for your query after it completes. You can find it in Query Store.

 

SELECT q.query_id ,q.query_text_id ,OBJECT_NAME(q.object_id) AS OBJECT_NAME ,q.query_hash ,qp.plan_id ,qp.query_plan_hash ,qt.query_sql_text ,convert(xml,qp.query_plan) as plan_xml ,qrs.last_execution_time ,qrs .avg_cpu_time FROM sys.query_store_plan qp INNER JOIN sys.query_store_query q ON qp.query_id = q.query_id INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id INNER JOIN sys.query_store_runtime_stats qrs ON qrs.plan_id = p.plan_ID Where is ? query_hash in ( 0xCEE3DC068B8439D0 )

 

 

 

 

• Expand your service tiers.

 

 

• If you check the runtime statistics for the query again after the expansion is complete, the runtime statistics you just ran will be lost.

 

 

I believe this is because the runtime statistics were still cached and not persisted to disk before scaling the service tier. Therefore, when a node changes (expands), any runtime statistics that are not maintained on disk are lost. (no way public document actually mentions this)

 

• Test again and this time manually flush the data to Query Store or wait a little longer for the automatic flush to occur before scaling the database.

 

EXEC sp_query_store_flush_db

 

 

 

This time, runtime statistics are preserved and are not lost after resizing.

 

 

 

Scenario #2 – Queries are blocked or do not complete before the connection is lost due to an outage event (e.g. service tier expansion).

First, test blocking scenarios to ensure that Query Store is not capturing runtime statistics for queries that do not complete due to blocking.

• Before testing, get the current query runtime statistics for the candidate query (0x37F2DD8F0C84C585).

 

SELECT q.query_id ,q.query_text_id ,OBJECT_NAME(q.object_id) AS OBJECT_NAME ,q.query_hash ,qp.plan_id ,qp.query_plan_hash ,qt.query_sql_text ,convert(xml,qp.query_plan) as plan_xml ,qrs.last_execution_time ,qrs .avg_cpu_time ,qrs.last_duration ,qrs.last_cpu_time ,qrs.count_executions ,qrs.execution_type_desc FROM sys.query_store_plan qp INNER JOIN sys.query_store_query q ON qp.query_id = q.query_id INNER JOIN sys.query_store_query_text qt ON q.query_ text_id = qt . query_text_id INNER JOIN sys.query_store_runtime_stats qrs ON qrs.plan_id = qp.plan_id WHERE query_hash in ( 0x37F2DD8F0C84C585 ) order by qrs.last_execution_time asc

 

 

 

 

• Execute the query below to hold the table lock.

 

Start trading TOP selection (1000) [id],[col1],[col2]
at [dbo].[Table1] WITH (TABLOCKX) wait for delay ’05:00′

 

 

• If you run the candidate query (0x37F2DD8F0C84C585) again, you will see that it is blocked.

 

INSERT INTO Table1 values(‘A’, ‘B’)

 

 

• Double-check the runtime statistics for the candidate query (0x37F2DD8F0C84C585). no New runtime statistics are generated or recorded.

 

• The same appears even after manually flushing the Query Store data. (It appears that the query did not complete, so runtime statistics were not generated or collected.)

 

 

• As expected, after scaling, the Query Store runtime statistics for candidate queries appear the same.

 

Second, we test scenarios where queries do not complete due to long execution (no blocking).

• Run an insert query that takes a few minutes to complete.

 

insert target [dbo].[Table3] ([id], [col1], [col2]) select [id], [col1], [col2]
at [dbo].[Table1];

 

 

 

• First, test that Query Store captures this query when it completes. As you can see, runtime statistics can be recorded.

 

SELECT q.query_id ,q.query_text_id ,OBJECT_NAME(q.object_id) AS OBJECT_NAME ,q.query_hash ,qp.plan_id ,qp.query_plan_hash ,qt.query_sql_text ,convert(xml,qp.query_plan) as plan_xml ,qrs.last_execution_time ,qrs .avg_cpu_Time, QRS.LAST_LOGICAL_IO_READS, QRS.AVG_PHYSICAL_IO_READS y q on qp.Query_id = q.query_id inner Join sys.query_store_query_text qt qt on q.query_text_id = qt. query_text_id INNER JOIN system .query_store_runtime_stats qrs ON qrs.plan_id = qp.plan_id qrs.last_execution_time based on query_hash in ( 0x465560FFECC0DF19 ) order

 

 

 

• Now drop the table and run the same insert query again.


• While the query continues to run, check the Query Store runtime statistics for the query. Runtime statistics are not generated.

 

• Expand the database before the insert query completes. (i.e. the insert query is aborted)

 

 

• After resizing, check the runtime statistics again. Runtime statistics are not generated.

 

 

• And the table is empty. (No data was inserted because the insert query did not complete)

 

conclusion

1. When a query completes, corresponding runtime statistics are generated and collected in the Query Store cache. These statistics are flushed to disk after some time, regardless of whether the transaction is uncommitted or rolled back. thatflush_interval_secondsThe parameter specifies the flush interval.


2. Runtime statistics for a query are generated and collected only after the query completes. This means that if the SQL database is shut down or restarted before runtime statistics for a query have been generated or collected, the statistics are lost.

 

 

 

Source link