Check SQL Server Query Store Storage Utilization with T-SQL

Problem

The SQL Server Query Store is a wonderful tool to keep track of query performance of a database over time, it provides insight about query execution plans, query text and runtime statistics. Depending on the database and application workload, the Query Store will eventually store more and more data until it reaches the max size configured for the Query Store. The database will continue operating normally, but new query performance data will be not captured because the Query Store will automatically switch to read-only mode when this happens. SQL Server Management Studio (SSMS) provides a graphical representation of the Query Store storage utilization, but this is not a very practical way to get this information for a large SQL Server environment with multiple databases. Below is a screen shot of the Query Store properties using SSMS where we can see the current status for the WideWorldImporters database.

In this tip we will look at how we can capture this information using T-SQL.

Solution

As noticed from the image above, SSMS has a simply pie chart that shows the Query Store Available and Used storage, the goal of this tip is to reproduce the same information using T-SQL so we can customize and monitor this storage utilization in a better way for our databases.

The Query Store has a set of DVMs which provides all the information you see in SSMS, let’s take a look at each of the DMV’s available:

The information in detail of each one of these DMV’s can be found in these Microsoft docs.  If you look at the link, you can see the documentation about these DVM’s is very good. Is not that hard to understand how to pull the information we are looking for, specifically storage utilization.

Queries to get SQL Server Query Store Options and Status

First we need to understand the current Query Store configuration, the storage configuration details can be found using the sys.database_query_store_settings DMV for this purpose.  I will use the WideWorldImporters database for these examples, but this can be used for any database that is using the Query Store.

Let’s run a basic query to get the following basic information:

• Current size in megabytes
• Max storage reserved in megabytes
• Query Store status
• Read only reason (in case it’s in this state)
• Cleanup policy
• Size cleanup mode (OFF \ AUTO)

USE [WideWorldImporters]
GO
SELECT
   current_storage_size_mb,
   max_storage_size_mb,
   actual_state_desc,
   readonly_reason,
   stale_query_threshold_days,
   size_based_cleanup_mode_desc
FROM sys.database_query_store_options;
GO

Here is the output from the query:

This query works just fine, it provides the basic information about the Query Store status for this database and how much space its utilized, but this information is not enough to have a full understanding of the Query Store storage utilization and status.

It is very likely that at some point the Query Store can run out of space, the readonly_reason column returns a “code” (in this case zero) to explain what happened which is not convenient, also there is no real space utilization just the actual and max size of Query Store.

I did some research about the read only reason, and found the description about every single code in these Microsoft docs:

I found this information really valuable, because it is always nice to know for what reason the Query Store stopped tracking queries and went from “READ WRITE” to “READ ONLY”.

The following query includes the read only reason identification using a case, it also has the storage utilization calculation:

USE [WideWorldImporters]
GO
SELECT
   DB_NAME() as "Database Name",
   actual_state_desc as "Actual State",
   FORMAT(current_storage_size_mb, 'N0') as "Current Storage Size (MB)",
   FORMAT(max_storage_size_mb, 'N0') as "Max Storage Size (MB)",
   CAST(current_storage_size_mb AS FLOAT)/CAST(max_storage_size_mb AS FLOAT) "Storage utilization %",
   CASE
      WHEN readonly_reason = 1 THEN 'Database in read-only mode'
      WHEN readonly_reason = 2 THEN 'Database in single-user mode'
      WHEN readonly_reason = 4 THEN 'Database in emergency mode'
      WHEN readonly_reason = 8 THEN 'Database is secondary replica'
      WHEN readonly_reason = 65536 THEN 'Query Store has reached the size limit set by the MAX_STORAGE_SIZE_MB option'
      WHEN readonly_reason = 131072 THEN 'The number of different statements in Query Store has reached the internal memory limit'
      WHEN readonly_reason = 262144 THEN 'Size of in-memory items waiting to be persisted on disk has reached the internal memory limit'
      WHEN readonly_reason = 524288 THEN 'User database has reached disk size limit'
      ELSE 'N\A'
   END as "Read only Reason",
   stale_query_threshold_days as "Cleanup policy",
   size_based_cleanup_mode_desc as "Size based cleanup mode"
FROM sys.database_query_store_options
GO

Here is the output from the query:

This query works really well providing you details about the status, storage utilization and the description of the read only reason in case Query Store goes in that status.

Next Steps

• Create an automated process to collect this information
• Create a notification process to page DBA’s when Query Store runs out of space
• Learn more how to monitor performance using Query Store
• Read more about Automatic Query tuning tips
• Check previous tips about SQL Server 2017 tips