Best practices to enable SQL Server Query Store for a database

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 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

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

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