Best practices to enable SQL Server Query Store

What is the Query Store?

The SQL Server Query Store is a feature introduced in SQL Server 2016. It provides you with insight on query plan choice and performance against a specific database.

Why should I enable it?

This information helps in identifying performance issues even after SQL Server restart or upgrade. All data that SQL Server Query Store capture are stored on disk.

How do I turn on a query store?

To enable it using SQL Server Management Studio (SSMS):

  1. Right-click a database, and then click Properties.
  2. In the Database Properties dialog box, select the Query Store
  3. In the Operation Mode (Requested) box, select Read Write.

Notes: SQL Server Query Store requires at least version 16 of Management Studio.

Best practices indicate that you should set QUERY_CAPTURE_MODE to AUTO and MAX_STORAGE_SIZE_MB to 1GB to thought 10GB at the absolute max (Adjust CLEANUP_POLICY to keep less data, depending on your workload).

Also, it’s a good idea to enable the trace flags 7752 and 7745.

Alternatively, you can enable it using the ALTER DATABASE statement (TSQL).

  1. Change the [XXXX] to your database name:
ALTER DATABASE [XXXX] 
SET QUERY_STORE = ON   
    (  
      OPERATION_MODE = READ_WRITE ,
      MAX_STORAGE_SIZE_MB = 1024,
      QUERY_CAPTURE_MODE = AUTO
    );

More information:

Microsoft – Best practice with the query store
Microsoft – Monitoring performance by using the query store

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.