Month: April 2020

Since Query Store objects are stored in the PRIMARY filegroup and that cannot be changed, it is important to understand how big it could get so your filegroup won’t get filled by it. This also means that piecemeal restores will include your Query Store data in the partial-restore sequence. The SSMS UI only provides a very basic summary of Query Store’s data usage: just a number and a pie chart that doesn’t give you any details at all.

So first of all, let’s understand how much space a single query takes.

How much space does a query take?

First, let’s differentiate between the query data itself, and the metrics. The query’s data is stored in the following three tables

Query Data (text & execution plan)

sys.query_store_plan

This table contains some metrics for the plans, along with the plan itself in XML format. That accounts for a total of 549 bytes + the size of the XML plan.

sys.query_store_query

This table contains some metrics for the query, totaling 409 bytes per query.

sys.query_store_query_text

This table contains the actual text for the queries. In addition to the size of the query text stored as a nvarchar, each entry stores 54 extra bytes of information.

Doing some math, one simple query that uses a single plan, takes:

A total of 1012 bytes, and that’s not even counting the size of the query and its plan. For a very simple query such as the one below, that accounts for 90 bytes of text and 4818 bytes of an XML plan.

So that query alone took 5920 bytes, a bit short of 6 KBs. But that only covers the query itself and its plans, but none of the information represented in the graphic report SSMS generates, which is stored in two separate tables

But all this still only gives us “static” figures, those numbers are not gonna change anytime soon unless the object changes (new queries and plans to substitute the existing ones), or there is a change in the execution plan due to changes in indexes, statistics… (adding more plans to Query Store).

Execution metrics

The details of each query (organized based on the plan it uses) are stored in two tables

sys.query_store_runtime_stats

This table contains all execution metrics of each plan, with separate entries for successful, failed (returning an error) or cancelled executions. Each entry requires 653 bytes, but if the same plan is executed 3 times in the same interval (one successful execution, one failed execution, and a cancelled execution), that would mean up to 3*653 = 1959 bytes per interval.

sys.query_store_wait_stats

This table contains wait stats details for each plan, one entry for each wait type’s statistics (if there is any wait at all for the plan). These waits are not 1:1 representative of all the wait stats a query can experience, but an aggregation available in the official documentation ). Still, a single query could experience several of them, and each takes 315 bytes.

Still, there are too many variables and unknowns to estimate how much space a single query or object can take in Query Store, but it is possible to get the size it currently takes to help estimate how much it will take in the future.

Total space used by one object

The following query calculates the size of the objects and statistics stored in Query Store associated to a given object (function or stored procedure)

DECLARE @ObjectName NVARCHAR(262) = '[schema].[procedure]'
DECLARE @query TABLE
(
	query_id BIGINT,
	query_text_id BIGINT
)
DECLARE @QueryCount INT
DECLARE @QueryTextSize INT
DECLARE @PlanCount INT
DECLARE @PlanSize INT
DECLARE @RuntimesCount INT
DECLARE @WaitsCount INT

INSERT INTO @query
SELECT query_id, query_text_id 
FROM sys.query_store_query
WHERE object_id = OBJECT_ID(@OBjectName)
SET @QueryCount = @@ROWCOUNT

SELECT @PlanCount = COUNT(1), @PlanSize = SUM(DATALENGTH(query_plan)) FROM sys.query_store_plan qsp
INNER JOIN @query q
ON qsp.query_id = q.query_id

SELECT @QueryTextSize = SUM(DATALENGTH(query_sql_text))
FROM sys.query_store_query_text qsqt
INNER JOIN @query q
ON qsqt.query_text_id = q.query_text_id


SELECT @RuntimesCount = COUNT(1) FROM sys.query_store_runtime_stats qsrs
INNER JOIN sys.query_store_plan qsp
ON qsrs.plan_id = qsp.plan_id
INNER JOIN @query q
ON qsp.query_id = q.query_id

SELECT @WaitsCount = COUNT(1) FROM sys.query_store_wait_stats qsws
INNER JOIN sys.query_store_plan qsp
ON qsws.plan_id = qsp.plan_id
INNER JOIN @query q
ON qsp.query_id = q.query_id

SELECT 
	@QueryCount AS [QueryCount],
	@QueryTextSize AS [QueryTextSize],
	@PlanCount AS [PlanCount],
	@PlanSize AS [PlanXMLSize],
	(@QueryCount * (409+54)) + @QueryTextSize AS [QueryTotalSpace],
	(@PlanCount * (549)) + @PlanSize AS [PlanTotalSpace],
	(@RuntimesCount * (653)) AS [RuntimeMetricSize],
	(@WaitsCount * (315)) AS [WaitsMetricSize],
	(((@QueryCount * (409+54)) + @QueryTextSize) + ((@PlanCount * (549)) + @PlanSize)) + (@RuntimesCount * (653)) + (@WaitsCount * (315)) AS [TotalSpaceBytes],
	(((@QueryCount * (409+54)) + @QueryTextSize) + ((@PlanCount * (549)) + @PlanSize) + (@RuntimesCount * (653)) + (@WaitsCount * (315))) / 1024.0 AS [TotalSpaceKiloBytes]

Other tables

There are a few more tables that, due to their small size, I haven’t included in the previous calculations

sys.database_query_store_options

Contains not only the parameters for the Query Store configuration, but their current states so you can find if there is any deviation from your settings (such as the it turning into READ_ONLY because it ran out of space), and that take approximately 662 bytes (size varies depending on the length of some of its nvarchar columns).

sys.database_automatic_tuning_options

Despite its name not reflecting its relation with Query Store, this table contains the configuration settings for automatic tuning through Query Store and the current state of them, similar to sys.database_query_store_options. It takes 500 bytes.

sys.dm_db_tuning_recommendations

If automatic tuning is enabled, every query that has a tuning recommendation will have an entry on this table, consisting in the recommendation itself stored in a JSON format along with some more information such as why the recommendation was produced, the current state of the recommendation (applied, reverted…) and the time for each event associated to the query recommendation. Since it contains 2 JSON files with varying sizes, it is not possible to get an estimated size for each entry on this table, or the number of entries it may contain. (It would be possible, given the values the nvarchar columns contain, but since that may change in future releases it would not be an accurate representation).

sys.query_context_settings

Contains information about the semantics affecting the queries execution. Each entry takes 39 bytes and there are very few entries (under 50 in close to 1000 production databases analyzed): with its total size under 2 KBs this is negligible.

sys.query_store_runtime_interval

This table contains the details of the interval themselves, such as the start/end time. Each interval requires an entry and 28 bytes.

So we have the details of the space utilization based on an object, to understand the impact of each query/object in terms of space and why Query Store needs that much space, but what happens when you have thousands of objects, or all your SQL code resides on the application side and your database only receives DDL and DML commands or ad-hoc queries? How can you find out what is taking so much space in your Query Store, to add more details to the initial pie chart?

Query Store space usage detailed

Although I have been referring to them as “tables”, the objects listed above are system views, that rely on a combination of actual system tables that cannot be queried directly, and in-memory tables the SQL engine uses to improve Query Store performance during data capture operations.

As seen below, the object sys.query_store_runtime_stats has some of its data in a persisted internal table (in red), while some data is contained in the in-memory table (in blue)

However, when looking for particular entries (in this example, the first entry), the information we are trying to retrieve has already been persisted to disk and there is no access to in-memory tables required

The query below analyzes space usage from those interval tables’ indexes to get a better understanding on the pie chart

SET NOCOUNT ON
SELECT
    s.name AS [SchemaName],
    t.name AS [TableName],
    i.name AS [IndexName],
    p.rows AS RowCounts,
    (SUM(a.total_pages) * 8)/1024.0 AS TotalSpaceMB,
    (SUM(a.used_pages) * 8)/1024.0 AS UsedSpaceMB
FROM
    sys.internal_tables t WITH (NOLOCK)
INNER JOIN     
    sys.indexes i WITH (NOLOCK)
    ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p WITH (NOLOCK)
    ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a WITH (NOLOCK)
    ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s WITH (NOLOCK)
    ON t.schema_id = s.schema_id
INNER JOIN sys.filegroups f WITH (NOLOCK)
ON f.data_space_id = i.data_space_id
WHERE
    t.NAME LIKE 'plan_persist%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    s.name, t.name, i.name, t.create_date, p.Rows, f.name
ORDER BY
    s.name, t.name, i.name

Playing a bit with the numbers from a real production database (aggregating data on the table rather than per index), we get a more detailed pie chart.

Notes on this post

This post was written and tested on a SQL 2017 CU15 instance.