Dynamics Community Forum Thread Details

Enable 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

ALTER DATABASE AxDB SET QUERY_STORE = ON

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)

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-properties-query-store-page?view=sql-server-2017

Categories:

Performance

On-prem Implementation

Community site session details

Working with Query store - 1

Helpful resources

News and Announcements

Quick Links

November Spotlight Star - Khushbu Rajvi

Forum Structure Changes Coming on 11/8!

Dynamics 365 Community Platform update – Oct 28

Leaderboard > Finance

Featured topics

Product updates