Enable and use the Query Store

How to enable and use the Query Store to diagnose performance problems.

The Query Store is a SQL Server feature, available since the 2016 version, that keeps and aggregates query performance information.

TL;DR

Enable the Query Store on all your databases. It is an essential tool for diagnosing performance problems, and its impact on performance is negligible. Leaving it off means giving up the information you need to understand and fix execution plan regressions.

Enable the Query Store

You can enable the Query Store, or check that it is already enabled, from the database properties.

To open the database properties window, right-click the database in SQL Server Management Studio (SSMS) and choose Properties, as shown below.

Opening the database properties in SSMS

In the window that opens, click Query Store in the left-hand menu.

The Query Store must be enabled (Read-Write), and the capture mode must be Auto, as shown below.

Query Store settings in the database properties

Enable the Query Store with a script

This query shows which databases have the Query Store enabled on your instance:

List databases with Query Store enabled

See the file

-----------------------------------------------------------------
-- check is the query store is enabled on some databases.
--
-- rudi@babaluga.com, go ahead license
-----------------------------------------------------------------

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT 
	d.name,
	d.database_id,
	d.create_date,
	d.state_desc as [state]
FROM sys.databases d
WHERE d.is_query_store_on = 1
OPTION (RECOMPILE, MAXDOP 1);

In the Query Store properties, also check the following settings:

actual_state — must be READ_WRITE;
query_capture_mode — must be AUTO;
max_storage_size_mb — should be at least 1000, so that the Query Store has enough dedicated space to hold a history long enough to be useful for diagnosis.

To enable the Query Store on a database with T-SQL, use the following query:

Activate Query Store

See the file

-----------------------------------------------------------------------------------
-- Activate the Query Store on a database and apply recommended settings
-- (change db_name)

-- To specify the database name, use the "Specify Values for Template Parameters"
-- Navigate to Query-> Specify Values for Template Parameters.
-- Or use keyboard shortcut key Ctrl+Shift+M. 
--
-- rudi@babaluga.com, go ahead license
-----------------------------------------------------------------------------------

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

ALTER DATABASE [db_name] SET QUERY_STORE = ON
(
	OPERATION_MODE = READ_WRITE, 

	-- Data retention period (30 days by default, 60 for more history)
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),

    -- Flush to disk (900 sec = 15 min by default)
    DATA_FLUSH_INTERVAL_SECONDS = 900,

   -- Maximum storage size for Query Store
   -- Default 2019+ = 1000 MB, but 2000-5000 MB recommended in production
 	MAX_STORAGE_SIZE_MB = 2000, 

    -- Stats aggregation interval (60 min by default, 30 for more granularity)
    INTERVAL_LENGTH_MINUTES = 60,

   -- Automatic cleanup when close to the limit
    SIZE_BASED_CLEANUP_MODE = AUTO,
 
    -- Capture mode: AUTO filters trivial queries
    -- ALL = capture everything (debug), NONE = pause, CUSTOM = fine rules (2019+)
	QUERY_CAPTURE_MODE = AUTO
)
GO

Does the Query Store affect performance?

A common worry is that enabling the Query Store degrades server performance. That worry is unfounded. The impact is negligible in the vast majority of cases, and the information the Query Store provides is indispensable when diagnosing and fixing performance problems.

Microsoft enables it by default

The most convincing argument: since SQL Server 2022, the Query Store is enabled by default on all new databases. It has also been enabled by default on Azure SQL Database and Azure SQL Managed Instance for several years, across millions of production databases. Microsoft would not have made that decision if the Query Store were a performance problem.

An asynchronous architecture designed to stay out of the way

The Query Store was designed from the start to minimise its impact:

In-memory first: execution statistics are collected in memory, not on disk.
Asynchronous writes to disk: the in-memory data is written to disk by a background process. Query execution is never blocked waiting for the Query Store to write.
Configurable flush interval: by default, statistics are written to disk every 15 minutes (DATA_FLUSH_INTERVAL_SECONDS = 900), which reduces I/O further.

3 to 5 % overhead in the worst case

Microsoft puts the average overhead at 3 to 5 %. In practice, on the usual workloads built from stored procedures and parameterised queries, the impact is often imperceptible. Only very heavy workloads with a large volume of non-parameterised ad hoc queries can see a more marked impact, and the AUTO capture mode (the default) or CUSTOM (available since SQL Server 2019) mitigates that.

An automatic safety mechanism

If the Query Store reaches its storage limit (MAX_STORAGE_SIZE_MB), it switches automatically to READ_ONLY. It stops collecting new data, and from then on has no impact on performance at all. Queries keep running normally. This is a safety valve: the Query Store degrades gracefully rather than affecting the workload.

Good practice for minimal impact

Use the AUTO or CUSTOM capture mode — never ALL in production.
Set MAX_STORAGE_SIZE_MB to 1000 MB at least.
Keep SIZE_BASED_CLEANUP_MODE = AUTO for automatic cleanup.
Keep your SQL Server instance current with the latest cumulative updates.

In short

Leaving the Query Store off means giving up information that is essential to diagnosing performance problems. Its impact on performance is negligible, and it is your best ally for understanding and fixing execution plan regressions. Enable it on all your databases.

If you do not have the Query Store (SQL Server 2014 and earlier)

You can instead capture the most expensive and most resource-hungry queries with an Extended Events session:

Capture long-running queries

See the file

-------------------------------------------------------------------------------------------------------------------
-- create a session to trace long running queries based on 
-- elapsed execution time.
--
-- rudi@babaluga.com, go ahead license

/*
- To set the min execution time for a query to appear in the session, change these two lines below:
	[package0].[greater_than_uint64]([duration],(1000000))

  Set the value in microseconds. Current value is 1000000, which is 1 second, or 1000 milliseconds.

- Change STARTUP_STATE=ON to STARTUP_STATE=OFF if you don't want the session to be restarded when SQL Server restarts

- The sessions will write trace files in the log directory, you can find where it is in your system by 
  using the following query:
	SELECT SERVERPROPERTY('ErrorLogFileName')

  The files will be named long_running_queries*.xel
  There will be a maximum of 5 files, sizing 200 Mb max each. If you want less space, or need more space, 
  change the number of Mb in the following line :
	max_file_size=(200)
*/
-------------------------------------------------------------------------------------------------------------------


CREATE EVENT SESSION [long_running_queries] 
ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(
		sqlserver.client_app_name,
		sqlserver.client_hostname,
		sqlserver.database_name,
		sqlserver.username)
    WHERE (
		[package0].[greater_than_uint64]([duration],(1000000)) -- = 1000 milliseconds, or 1 second.
		AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'telegraf'))), -- example of session to exclude

ADD EVENT sqlserver.sql_batch_completed(
    ACTION(
		sqlserver.client_app_name,
		sqlserver.client_hostname,
		sqlserver.database_name,
		sqlserver.username)
    WHERE (
		[package0].[greater_than_uint64]([duration],(1000000)) -- = 1000 milliseconds, or 1 second.
		AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'telegraf')))  -- example of session to exclude
ADD TARGET package0.event_file(
	SET filename=N'long_running_queries',
		max_file_size=(200) -- in megabytes
	)
WITH (
        MAX_MEMORY=4096 KB,
        EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY=30 SECONDS,
        MAX_EVENT_SIZE=0 KB,
        MEMORY_PARTITION_MODE=NONE,
        TRACK_CAUSALITY=OFF,
        STARTUP_STATE=ON -- restart the session when SQL Server restarts
)
GO

-- start the session
ALTER EVENT SESSION [long_running_queries] ON SERVER STATE=START;
-- stop the session
/*
ALTER EVENT SESSION [long_running_queries] ON SERVER STATE=STOP;
*/

Going further

DataFrogs session videos about the Query Store (in French).
- Nicolas Souquet – Tout savoir sur le Query Store
- Steven Naudet

SQL Server Expertise Need help with SQL Server? Contact me