In the previous post we saw number of queries to analyze data captured by Query Store. In this post, we will present the data model representation of catalog views provided by SQL Server database.
Note that these are NOT DMVs…these are system catalog views under sys. schema. This is indicated by the names themselves too i.e. they are not prefixed by dm_. You may pay attention to the cardinalities too as mentioned in the diagram.
To easily remember their names, take it like –
- For each single query i.e. query_store_query_text (cardinality: one)
- All the executions are captured in query_store_query_query (cardinality: many)
- Then for each execution, query plans are captured in query_store_plan (again there can be multiple plans for a query so cardinality: many)
- Thereafter, some stats are captured in two different tables – wait_stats and runtime_stats (cardinality: many)
- Runtime stats interval is stored in another table (cardinality: one)