Window Shopping the Query Store with XEvents

TSQL Tuesday

The weather is starting to warm and Spring is upon us. It feels like a great time to take a stroll outside in the warming sun and maybe do a little window shopping on our way to the March (2020) blog party called TSQLTuesday.

This party, that was started by Adam Machanic, has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

This month, the blog party is all about couponing and super shopping at the Query Store. Tracy Boggiano (b | t) insists that everybody could save a grundle of money by couponing and shopping at this ultra sleek new store. Ok, maybe you can’t actually shop there but you can pick and choose your execution plans from this store just like you could pick and choose your favorite brand of spaghetti noodles from a store shelf. You can find the invite from Tracy – here.

I will not be delving into how to use the Query Store (QDS). Nor will I be exploring whether or not QDS is the right tool for you. What I am here to sell you on though is two methods to help you become better acquainted with the inner workings of QDS – via Extended Events (XE).

Some Basics

One of my favorite questions to ask during some of presentations on XE is “What was the first version of SQL Server to have Query Store?” You can imagine the wide array of answers but what is interesting is how often the correct answer is always missed. I hear lots of answers for 2012, some answers for 2017 and somewhere in between for 2016. But never does the correct answer pop up.

Right now, I hope you are scratching your head at that last statement. You see, the question is somewhat of a trick question. The first version of SQL Server that has QDS is SQL Server 2014. However, the first version where you can actually use it is SQL Server 2016. This fun fact is visible when we start exploring SQL Server from the realm of XE. Let’s take a look.

The following is a screenshot showing some data that I have been tracking on XE across all editions of SQL Server ever since it was incorporated into SQL Server. Don’t trust my word for it, there is a query later that can help you see this for yourself on a SQL Server 2014 instance

The result of the query is far too large for this article, so I just grabbed a snippet of the result and marked each version of SQL Server where various events can be seen. Some events were deprecated after SQL Server 2014 (in blue) as noted in the image, and some that existed in SQL Server 2014 still exist in SQL Server 2019 (in green).

Here is a query (as promised) that can be executed on SQL Server to capture the events related to QDS.

SELECT xo.name AS ObjectName, xo.object_type AS ObjType, xo.description,LEFT(xo.name, CHARINDEX('_',xo.name,(CHARINDEX('_',xo.name,1)+1))-1) AS FeatureAKA
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xp.name = 'qds'
AND xo.object_type = 'event'
UNION ALL
SELECT xo.name AS ObjectName, xo.object_type AS ObjType, xo.description,'query_store' AS FeatureAKA
FROM sys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
WHERE xp.name = 'qds'
AND xo.object_type = 'map';

If you are curious to see other sources showing that this feature was planned to be released in 2014, you can read it from Brent Ozar – here.

Looking Deeper

Referring back to the previous query, you may have noticed that I am filtering the events down to those that are provided by the “qds” package. The “qds” package is the provider for the Query Store (short name) or Query Data Store (long name). It’s not just events that are provided by that package, there are maps as well. Interestingly enough, there is more to this little package than just the QDS. Take a look at these results from a SQL Server 2019 instance (similar results can be found when querying SQL Server 2017 as well).

See those events in the blue box? Those are all related to the Automatic Tuning feature. Yes they are related to QDS and that is why these events are provided by the same package as QDS.

Sandbox

Let’s go ahead and enable QDS so we can take this to the next level.

USE [master]
GO
ALTER DATABASE [WhatsYourDBFlavorofTheDay] SET QUERY_STORE = ON
GO
ALTER DATABASE [WhatsYourDBFlavorofTheDay] SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 5))
GO

Once we have QDS enabled (you are not restricted to the settings I chose to use – try something different if you want), let’s go ahead and also gather some additional information that will be helpful for creating some XE sessions.

SELECT SCHEMA_NAME(ao.schema_id) AS SchemaName, ao.name AS ObjName, ao.type_desc
FROM sys.all_objects ao
WHERE ao.name LIKE '%plan_persist%';

That query will provide a list of tables that are related to QDS. It will also provide the proof of concept (basically) for creating an XE session. Knowing that the query will return just those objects for which I am interested, I am ready to go ahead and apply the same query pattern to my XE session.

But what is this XE session? Well, I am somebody who is curious and I want to know precisely what is being done when I do something like this.

ALTER DATABASE [DBA] SET QUERY_STORE CLEAR ALL;
GO

That statement has the same effect as clicking the button from the Database properties page for the Query Store.

Since I really want to know what is happening when the QDS is cleared, I have this little XE Session to show me exactly that.

USE master;
GO
-- Create the Event Session
IF EXISTS
(
SELECT
  *
FROM  sys.server_event_sessions
WHERE name = 'QdsQueries'
)
DROP EVENT SESSION QdsQueries ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION [QdsQueries]
ON SERVER
    ADD EVENT sqlserver.sp_statement_completed
    (SET collect_object_name = (1)
     ACTION
     (
         sqlserver.client_app_name,
         sqlserver.database_id,
         sqlserver.query_hash,
         sqlserver.query_plan_hash,
         sqlserver.session_id,
 sqlserver.database_name,
         package0.event_sequence
     )
     WHERE ([sqlserver].[database_name] = N'WhatsYourDBFlavorofTheDay'
               AND [package0].[equal_boolean]([sqlserver].[is_system], (0))
   AND [statement] LIKE '%plan_persist%'
           )
    )
    ADD TARGET package0.ring_buffer
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,
    STARTUP_STATE = OFF
);
GO
ALTER EVENT SESSION QdsQueries ON SERVER
STATE = START;
GO

Never-mind the fact that I am creating a folder on disk despite the fact that I am using the ring_buffer in this session. I do that with most all of my sessions just in case I want to add an event_file target at some future point. A couple of key points for this session: 1) I have scoped the results to be pertinent just to the WhatsYourDBFlavorofTheDay database; and 2) the results are further filtered to just be for any objects that match “plan_persist” with some wildcards. If I have that session running, and I clear the QDS cache, I will see results similar to the following.

When the cache is cleared for QDS, it is a very quick process. Looking at the results of this session, we can see why that is – the underlying tables are all TRUNCATED. This may be a bit of a heavy handed approach given the truncate does reseed the tables. Just be aware of that fact. Personally, I am ok with the TRUNCATE and it is nice to know what is happening behind the scenes.

That is a bit of a fun look at a somewhat trivial piece of the QDS. Something more technical comes from trying to monitor the QDS for plan removal, cleanup processing, and similar tasks. How and when does some of that work/occur? Let’s get a more comprehensive XE session to peer into those kinds of internals.

USE master;
GO
-- Create the Event Session
IF EXISTS
(
SELECT
  *
FROM  sys.server_event_sessions
WHERE name = 'QdsCleanupMonitor'
)
DROP EVENT SESSION QdsCleanupMonitor ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION [QdsCleanupMonitor]
ON SERVER
    ADD EVENT qds.query_store_db_cleanup__finished
    (ACTION
     (
         package0.last_error,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.sql_text,
  package0.callstack,
         sqlserver.query_hash,
         sqlserver.query_plan_hash,
         sqlserver.tsql_stack,
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.username
 )
    ),
    ADD EVENT qds.query_store_db_cleanup__started
    (ACTION
     (
         package0.last_error,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.sql_text,
 package0.callstack,
         sqlserver.query_hash,
         sqlserver.query_plan_hash,
         sqlserver.tsql_stack,
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.username
     )
    ),
    ADD EVENT qds.query_store_execution_runtime_info_discarded
    (ACTION
     (
         package0.last_error,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.sql_text,
 package0.callstack,
         sqlserver.query_hash,
         sqlserver.query_plan_hash,
         sqlserver.tsql_stack,
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.username
     )
    ),
    ADD EVENT qds.query_store_flush_failed
    (ACTION
     (
         package0.last_error,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.sql_text,
 package0.callstack,
         sqlserver.query_hash,
         sqlserver.query_plan_hash,
         sqlserver.tsql_stack,
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.username
     )
    ),
    ADD EVENT qds.query_store_plan_removal
    (ACTION
     (
         package0.last_error,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.sql_text,
 package0.callstack,
         sqlserver.query_hash,
         sqlserver.query_plan_hash,
         sqlserver.tsql_stack,
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.username
     )
    ),
    ADD EVENT qds.query_store_size_retention_cleanup_finished
    (ACTION
     (
         package0.last_error,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.sql_text,
 package0.callstack,
         sqlserver.query_hash,
         sqlserver.query_plan_hash,
         sqlserver.tsql_stack,
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.username
     )
    ),
    ADD EVENT qds.query_store_size_retention_cleanup_skipped
    (ACTION
     (
         package0.last_error,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.sql_text,
 package0.callstack,
         sqlserver.query_hash,
         sqlserver.query_plan_hash,
         sqlserver.tsql_stack,
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.username
     )
    ),
    ADD EVENT qds.query_store_size_retention_cleanup_started
    (ACTION
     (
         package0.last_error,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.sql_text,
 package0.callstack,
         sqlserver.query_hash,
         sqlserver.query_plan_hash,
         sqlserver.tsql_stack,
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.username
     )
    ),
    ADD EVENT qds.query_store_size_retention_cleanup_update
    (ACTION
     (
         package0.last_error,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.sql_text,
 package0.callstack,
         sqlserver.query_hash,
         sqlserver.query_plan_hash,
         sqlserver.tsql_stack,
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.username
     )
    ),
    ADD EVENT qds.query_store_size_retention_query_deleted
    (ACTION
     (
         package0.last_error,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.context_info,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.plan_handle,
         sqlserver.server_instance_name,
         sqlserver.sql_text,
 package0.callstack,
         sqlserver.query_hash,
         sqlserver.query_plan_hash,
         sqlserver.tsql_stack,
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.session_nt_username,
         sqlserver.sql_text,
         sqlserver.username
     )
    )
WITH
(
    MAX_MEMORY = 40096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,
    STARTUP_STATE = OFF
);
GO
ALTER EVENT SESSION QdsCleanupMonitor ON SERVER
STATE = START;
GO

That is a big session script! Here is basically what triggers all of those events in this session to fire.

And with that session running and after waiting a period of time, here is a sample of what some of that session data may look like.

Your results could vary quite substantially. That said, this session could help you to better understand how cleanup operations are occurring within QDS.

Wrapping it Up

This article has just shared multiple tools to help you become more acquainted with the Query Store! This acquaintance is coming via an extremely powerful tool called Extended Events. Through the use of these two sessions and two additional scripts, this article demonstrates how to become more familiar with the internals for QDS.

Feel free to explore some of the other TSQL Tuesday posts I have written.

If you are in need of a little tune-up for your XE skills, I recommend reading a bit on Extended Events to get up to date. For some “back to basics” related articles, feel free to read here.

Puzzles and Daily Trivia May 14, 2019
The Curiously Large CASE Statement March 20, 2019
The Gift of the SPN December 10, 2019
Event Files on Linux July 9, 2019
Automating like an Enterprise DBA January 8, 2019