Query Store – Part 2 - Configuration Settings

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

* 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

Tweet