How to enable SQL Server Query Store: Best Practices

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.

By the way, this check is a part of our SQL Server Health Check service.

How do I turn on the Query store?

To enable it using SQL Server Management Studio (SSMS), follow the steps below:

  1. Firstly, 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.

Shows the options for the query store in the SQL Server using the SSMS.

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

Above all, 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).

In addition, 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:
  3.     (  
  5.       MAX_STORAGE_SIZE_MB = 1024,
  7.     );

More information:

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

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

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