Share
Report
55Enable query store –
Query store can be enabled on database by SSMS or query.
Enable query store by SSMS – Right-click on the database (AxDB), select properties, and then select the Query Store page at the bottom. The first setting is “Operation Mode”. By default, this is set to “Off”. To enable Query Store and get it running for a database you change it to “Read Write”.
Enable query store by SQL Statement –
USE master
GO
ALTER DATABASE AxDB SET QUERY_STORE = ON
GO
Query store properties –
- The Data Flush interval is how often the query store data gets written to disk. To optimize for performance, data collected by the query store is asynchronously written to the disk. The frequency at which this asynchronous transfer occurs is configured.
- The Statistics Collection interval determines the size of the time slices that query performance metrics get aggregated into.
- Query Store Capture Mode
o None does not capture new queries.
o All captures all queries.
o Auto captures queries based on resource consumption.
- Data retention - query store fills up and nothing is happening to clear it out then it flips to Read-Only mode and won’t store any more data until space is freed up.
- Stale Query Threshold is how long it keeps data for in days. It being useful to up this significantly it we want to use Query Store to be able to monitor performance over a long period.
Set Query store properties using SQL –
ALTER DATABASE AXDB SET QUERY_STORE
(OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 300, INTERVAL_LENGTH_MINUTES = 10)
GO
All responses (
Answers (
