Query Store collects 2 important kinds of data. They are
1) Compile time statistics - Query Plan, Estimated Costs, Estimated number of rows etc..
2) Runtime statistics - Cost of the query, row count, IO cost, memory, Degree of parallelism etc.
How it actually stores is, when plan is loaded to plan cache, its compile time data like query plan, estimated costs are loaded into plan store. Subsequently, as the plan gets reused, the runtime stats like actual cost, memory used, and actual row count are aggregated and stored. Runtime stats are aggregated at a frequency or time period defined by "Statistics Collection Interval" setting explained here. Meaning, if one sets "Statistics Collection Interval" as 10 minutes, then Avg/Min/Max/Std Dev values of runtime stats are grouped for 10 minute intervals and stored in query store. For ex - For each query plan avg running duration, IO, memory for 9:40 to 9:50, 9:50 to 10:00, 10:00 to 10:10 and so on is stored
Runtime stats are recorded asynchronously. Query plans meta data views can be used to view compile time stat and run time stats. They are
1) Compile time statistics - Query Plan, Estimated Costs, Estimated number of rows etc..
2) Runtime statistics - Cost of the query, row count, IO cost, memory, Degree of parallelism etc.
How it actually stores is, when plan is loaded to plan cache, its compile time data like query plan, estimated costs are loaded into plan store. Subsequently, as the plan gets reused, the runtime stats like actual cost, memory used, and actual row count are aggregated and stored. Runtime stats are aggregated at a frequency or time period defined by "Statistics Collection Interval" setting explained here. Meaning, if one sets "Statistics Collection Interval" as 10 minutes, then Avg/Min/Max/Std Dev values of runtime stats are grouped for 10 minute intervals and stored in query store. For ex - For each query plan avg running duration, IO, memory for 9:40 to 9:50, 9:50 to 10:00, 10:00 to 10:10 and so on is stored
Runtime stats are recorded asynchronously. Query plans meta data views can be used to view compile time stat and run time stats. They are
Compile time data / Plan store:
1) Sys.query_store_query_text
2) Sys.query_store_query
3) Sys.query_store_context_settings
4) Sys. query_store_plan
2) Sys.query_store_query
3) Sys.query_store_context_settings
4) Sys. query_store_plan
Run time data:
1) query_store_runtime_stats
2) query_store_runtime_stats_interval
2) query_store_runtime_stats_interval
How to use these effectively, is coming up in next post
