Introduction to Query Store covered here.
Now for the second part on “How to Configure?”
Query Store is configured at the database level. Right Click on the Database-> Properties ->QueryStore
* Stale Query Threshold: Purges the plans older than the specified date. Helps in query store not using up the total storage limit set by "Query Store Retention"
* Size Based Cleanup: One can set to "Auto" / "Off". Recommended to set to "Auto". Once set to "Auto", query store automatically triggers the cleanup process if the query store's storage usage is close to its limit set by "Query Store Retention". If set to "off", once the query store's usage reaches its limit, query store stops recording the data and slips into "Read Only" Operational mode.
Now for the second part on “How to Configure?”
Query Store is configured at the database level. Right Click on the Database-> Properties ->QueryStore
* Operation Mode: To turn the query store on set "Operation Mode" to "Read Write". At "Read Write" mode, query store starts to record the query plans
* Query Store Retention: One can set a max size to the data collected by query store. Size consumed includes the query plans recorded size and all the other statistics size recorded by query store. Size specified in MB.
* Stale Query Threshold: Purges the plans older than the specified date. Helps in query store not using up the total storage limit set by "Query Store Retention"
* Size Based Cleanup: One can set to "Auto" / "Off". Recommended to set to "Auto". Once set to "Auto", query store automatically triggers the cleanup process if the query store's storage usage is close to its limit set by "Query Store Retention". If set to "off", once the query store's usage reaches its limit, query store stops recording the data and slips into "Read Only" Operational mode.
* Statistics Collection Interval: Indicates the frequency at which query store runtime statistics are collected. Statistics collected are run time statistics of query plans like number of executions, cost per executions, row count etc. Shorter Statistics collection interval provided will help the statistics collection to be lot more granular but at the cost of additional storage. Shorter Collection intervals doesn't have adverse impact on performance and will have only additional storage cost.
* Data Flush Interval: Indicates the frequency at which the query store statistics and plans are flushed to disk.
Query Store Capture Mode: "All" captures all the query plans generated
We will cover how query store collects and stores the data in the next post
