Query Store Internals: Indexes on the system views
Foreword
The Query Store is a great new feature available starting in SQL Server 2016. I started using it a few months ago, and learned a lot about it from Erin Stellato (blog | twitter)! I got a chance to speak with her at SQL Saturday Cleveland, where we discussed the indexes in this post. Thank you Erin for all your help with this post and understanding the Query Store! To anyone reading this post who wants to learn more, I highly recommend Erin’s blog posts and Pluralsight courses on Query Store.
Structure of the Query Store
The easiest way to see what’s stored in the Query Store is to query the catalog views. Behind the scenes of catalog views, data is stored in system tables . There are many benefits to this, starting with the fact that your performance data will survive a reboot. If we want to see these tables, we can search for them by looking for clustered indexes or heaps, that store this data. But when we query for these tables or their indexes, nothing shows up:
(Query taken from Microsoft’s documentation on sys.indexes)
SELECT i.name AS index_name
,COL_NAME(ic.object_id,ic.column_id) AS column_name
,ic.index_column_id
,ic.key_ordinal
,ic.is_included_column
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.name like ‘%query_store%’
order by i.name
Another approach
Let’s try looking for just one view. Here’s a query store catalog view-:
sys.query_store_runtime_stats (and more from Microsoft)
Same query, different keyword:
SELECT i.name AS index_name
,COL_NAME(ic.object_id,ic.column_id) AS column_name
,ic.index_column_id
,ic.key_ordinal
,ic.is_included_column
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.name like ‘%runtime%’
order by i.name
Now we’re talking!
It looks like internally Query Store is referred to as plan_persist. That makes sense, thinking about how the Query Store persists query plans to your database’s storage. Let’s take a look at those catalog views vs their clustered and nonclustered indexes. I’ve modified the query a bit at this point, to group together the key columns.
SELECT i.name AS index_name ,
CASE when i.index_id = 1 THEN ‘Clustered’
WHEN i.index_id = 0 THEN ‘Heap’
ELSE ‘Nonclustered’ END as TypeOfIndex,
STUFF((
SELECT ‘,’ + COL_NAME(ic.object_id,ic.column_id)
FROM sys.index_columns as ic
WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id
and is_included_column = 0 FOR XML PATH(”)),1,1,”)
AS [KeyColumns]
FROM sys.indexes AS i
WHERE i.name like ‘%plan_persist%’
Results:
| Catalog View Name | Index Name | Key Columns | Type of Index |
| sys.query_store_query_text (Transact-SQL) | plan_persist_query_text_cidx | query_text_id | Clustered |
| sys.query_store_query (Transact-SQL) | plan_persist_query_cidx | query_id | Clustered |
| sys.query_store_plan (Transact-SQL) | plan_persist_plan_cidx | plan_id | Clustered |
| sys.query_store_runtime_stats (Transact-SQL) | plan_persist_runtime_stats_cidx | plan_id,runtime_stats_interval_id,execution_type | Clustered |
| sys.query_store_runtime_stats_interval (Transact-SQL) | plan_persist_runtime_stats_interval_cidx | runtime_stats_interval_id | Clustered |
| sys.query_context_settings (Transact-SQL) | plan_persist_context_settings_cidx | context_settings_id | Clustered |
| Undocumented | plan_persist_query_hints_cidx | query_hint_id | Clustered |
| Undocumented | plan_persist_query_template_parameterization_cidx | query_template_id | Clustered |
| sys.query_store_wait_stats (Transact-SQL) | plan_persist_wait_stats_cidx | runtime_stats_interval_id,plan_id,wait_category,execution_type | Clustered |
| plan_persist_query_text_idx1 | statement_sql_handle | Nonclustered | |
| plan_persist_query_idx1 | query_text_id,context_settings_id | Nonclustered | |
| plan_persist_plan_idx1 | query_id | Nonclustered | |
| plan_persist_runtime_stats_idx1 | runtime_stats_id | Nonclustered | |
| plan_persist_runtime_stats_interval_idx1 | end_time | Nonclustered | |
| plan_persist_query_hints_idx1 | query_hint_id | Nonclustered | |
| plan_persist_query_template_parameterization_idx1 | query_template_hash | Nonclustered | |
| plan_persist_wait_stats_idx1 | wait_stats_id | Nonclustered |
Knowing this, we can query the Query Store using predicates supported by indexes! We can also see the Query Store indexes if we request actual Execution Plans.
Here’s my query:
select * from sys.query_store_query
where query_id = 5
Here’s the execution plan:
And here’s the predicate that was applied:
Looking at this execution plan, the object is “plan_presist_query_cidx”, which is the index from above. This shows that Query Store uses these “plan persist” indexes, as the indexes behind the catalog views.
So what’s the point?
Well, Query Store size is user defined, so we can control how much space is consumed by the data in total. As our Query Store gets bigger, queries against the Query Store tables will start running slower unless we use the indexes properly. It’s important to know these indexes if you’re going to be querying Query Store regularly.
Final note (don’t do this in production)
As a cool side note, it’s possible to query the system tables directly if you’re connected to a SQL Server using the Dedicated Admin Connection (DAC). Please use only use the DAC on a test or local server for this example:
The connection prefix, “ADMIN:” shows that I’m connected with the DAC.