SQL Server 2016 Query Store, User Data or Metadata?

One of the newest features for SQL Server is the Query Store, let’s explore it to know a bit more about it It’s been a month since the SQL Server community has their new toy, SQL Server 2016, which to my surprise and considering not everyone has yet adopted SQL 2014, it’s absolutely packed with new features.

One of this features is the Query Store which promises will help troubleshooting performance issues by spotting queries which can be misbehaving due to something called «plan regression».

But even though this is a SQL Server feature and can be accessed using DMO’s, there are some characteristics that makes me think we should treat it as User Data instead of metadata and I will give 5 reasons why

 
1. You can optionally enable or disable

By default the query store is disabled but it can be enabled for the [model] database, although that’s something I wouldn’t recommend. But there is a way a brand new database have this feature enabled if we want to.

USE model
GO

ALTER DATABASE model SET QUERY_STORE = ON
GO

SELECT *
FROM sys.database_query_store_options  
GO

You can see there is nothing returned by the query although this setting will actually be part of new databases. To double check, let’s create a database without any options which would give us a database exactly as [model] is

USE master
GO

CREATE DATABASE QS_on
GO

USE QS_on
GO

SELECT *
FROM sys.database_query_store_options  
GO

There is our Query Store enabled, in case we want to do it afterwards, the way of enabling it in its simplest way as we have seen already would be

ALTER DATABASE [dbname] SET QUERY_STORE = ON
GO

 
2. You can define how big it can grow

There are some other comparable DMO’s which hold statistical information like [sys].[dm_db_index_usage_stats], [sys].[dm_exec_procedure_stats] or [sys].[dm_exec_query_stats] but there is no way a DBA can decide how much information they can hold.

This is possible for the query store by using one of the options provided

ALTER DATABASE [dbname] SET QUERY_STORE = ON (MAX_STORAGE_SIZE_MB = number)
GO

It’s good to remind that once we have reached the limit, the Query Store silently will be set to READ_ONLY and new information will not be collected, so keep an eye on it!

 
3. You can delete the data in a simple way

Some others DMV’s allow us to remove their data using DBCC commands, but usually that requires elevated privileges most DBA’s do not want to grant lightly.

For cleaning sys.dm_exec_procedure_stats and sys.dm_exec_query_plan we can use DBCC FREEPROCCACHE

For cleaning sys.dm_os_latch_stats and sys.dm_os_wait_stats we can use DBCC SQLPERF

Unfortunately for clearing sys.dm_db_index_usage_stats we don’t have any specific statement, although we can detach and attach, SET READ_ONLY and READ_WRITE or for all of them just restart our server.

For the Query Store we have this ALTER DATABASE option which seems more appropriate than DBCC commands

ALTER DATABASE [dbname] SET QUERY_STORE CLEAR
GO

 
4. Query Store data is backed up and restored along the rest of user data

Other metadata like the DMV’s shown above is not backed up together with the user data, but this is not the case for the Query Store data.

Let’s create some data and run some queries

USE QS_on
GO

CREATE TABLE dbo.t(
c1 INT IDENTITY PRIMARY KEY
)
GO

INSERT INTO dbo.t DEFAULT VALUES 
GO 10

SELECT * FROM dbo.t WHERE c1 = 1
SELECT * FROM dbo.t
GO 5

SELECT q.query_id, qt.query_sql_text, q.last_execution_time, qr.count_executions
	FROM sys.query_store_query AS q
		LEFT JOIN sys.query_store_query_text AS qt
			ON qt.query_text_id = q.query_text_id
		LEFT JOIN sys.query_store_plan AS qp
			ON qp.query_id = q.query_id
		CROSS APPLY (SELECT TOP 1 count_executions FROM sys.query_store_runtime_stats WHERE plan_id = qp.plan_id ORDER BY runtime_stats_id DESC) AS qr
GO

SELECT OBJECT_NAME(object_id), * 
	FROM sys.dm_db_index_usage_stats 
	WHERE database_id = DB_ID()
GO

We can see our 10 INSERT and 5 of each SELECT, scan and seeks. If now we take a backup and restore the database, only the metadata which belongs to the Query Store will be back when restoring the data, this might be extremely useful as we can do troubleshooting in a different server than production but looking at production execution plans and stats.

If now we back up and restore our database, only the Query Store metadata will come back, let’s see it

USE master
GO

BACKUP DATABASE QS_on TO DISK = 'QS_on.bak' WITH INIT
GO

RESTORE DATABASE QS_on FROM DISK = 'QS_on.bak' WITH RECOVERY, REPLACE
GO

USE QS_on
GO

SELECT q.query_id, qt.query_sql_text, q.last_execution_time, qr.count_executions
	FROM sys.query_store_query AS q
		LEFT JOIN sys.query_store_query_text AS qt
			ON qt.query_text_id = q.query_text_id
		LEFT JOIN sys.query_store_plan AS qp
			ON qp.query_id = q.query_id
		CROSS APPLY (SELECT TOP 1 count_executions FROM sys.query_store_runtime_stats WHERE plan_id = qp.plan_id ORDER BY runtime_stats_id DESC) AS qr
GO

SELECT OBJECT_NAME(object_id), * 
	FROM sys.dm_db_index_usage_stats 
	WHERE database_id = DB_ID()
GO

 
5. Query Store become READ_ONLY when the database goes READ_ONLY

This point is important, because other metadata will keep working when the database goes READ_ONLY, but the query store won’t.

You can find more information about this behaviour in a recent post by Kendra Little (b|t) where she shows that in order to get the new Query Store the database must be read-write, which I encourage you to read it.

To demonstrate it, let’s clear the Query Store and set the database to READ_ONLY before running a few queries

ALTER DATABASE QS_on SET QUERY_STORE CLEAR 
GO

ALTER DATABASE QS_on SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO

SELECT * FROM dbo.t WHERE c1 = 1
SELECT * FROM dbo.t
GO 5


SELECT q.query_id, qt.query_sql_text, q.last_execution_time, qr.count_executions
	FROM sys.query_store_query AS q
		LEFT JOIN sys.query_store_query_text AS qt
			ON qt.query_text_id = q.query_text_id
		LEFT JOIN sys.query_store_plan AS qp
			ON qp.query_id = q.query_id
		CROSS APPLY (SELECT TOP 1 count_executions FROM sys.query_store_runtime_stats WHERE plan_id = qp.plan_id ORDER BY runtime_stats_id DESC) AS qr
GO

SELECT OBJECT_NAME(object_id), * 
	FROM sys.dm_db_index_usage_stats 
	WHERE database_id = DB_ID()
GO

So you can see how the counters for [dm_db_index_usage_stats] keep counting while the Query Store freezes.

 
Conclusion

Although it is documented and classified as metadata, the Query Store information behaves like user data in many aspects, which is something to take into account to provision enough resources to keep it working.

I’m already working on a new post where I explain how to overcome the limitation and have the Query Store working for «READ_ONLY» databases, so stay tuned!

Thanks for reading!