Lesson Learned #386: Can I log when Query Data Store (QDS) fails to capture a query?
Published Jun 28 2023 02:46 PM 1,877 Views

In a recent service request, a customer asked if there was a way to identify when QDS fails to capture a query due to various reasons, such as high workload. Here's what I learned from this experience.

 

The customer reported that they have the capture mode setting as "ALL," but it appears that some queries are not being captured.

 

In this situation, I suggested using an Extended Event that could capture an event when there is a failure in saving the data. Upon inspecting the objects (sys.dm_xe_objects) and filtering by '%query_store', I came across some very interesting events that could help in collecting the necessary data.

 

By running the query select * from sys.dm_xe_objects where name like '%query_store%', I discovered several intriguing events to work with. Here's an example of an Extended Event session that captures the event when a query fails to be captured in the Query Data Store:

 

Jose_Manuel_Jurado_0-1687988225555.png

 

CREATE EVENT SESSION [QDS_Review] ON DATABASE 
ADD EVENT sqlserver.query_store_failed_to_capture_query(
ACTION(package0.callstack_rva,sqlserver.num_response_rows,sqlserver.request_id,sqlserver.sql_text))
WITH (STARTUP_STATE=OFF)
GO

 

Version history
Last update:
‎Jun 28 2023 02:46 PM
Updated by: