Various Ways to Enable Query Store

November 22, 2026

Query store is a valuable tool for people managing SQL Server, whether that’s on-prem, in an IaaS scenario or AWS RDS, Azure Managed Instance or Azure SQL DB.

This post will explore some of those methods. If you’re not familiar with Query Store, then go read this post. If you need help getting started with PowerShell, then start here.

T-SQL, of course, is a natural way to enable Query Store in SQL Server. Below is a simple one line script to enable Query Store for a single database. You could create a loop, via cursor to get a list of all databases that don’t have it enabled and then enable it for each one.

T-SQL

A Single Database


ALTER DATABASE MyDB SET QUERY_STORE = ON (MAX_STORAGE_SIZE_MB = 4096);

Looping with a Cursor to build, print, and execute a T-SQL statement


DECLARE @DBName  SYSNAME, @SQL NVARCHAR(MAX);

DECLARE db_cursor CURSOR FAST_FORWARD 

FOR 
SELECT [name]
from sys.databases 
WHERE is_query_store_on = 0
AND database_id > 4
AND [name] NOT LIKE 'SQLLab%';

OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @DBName

WHILE @@FETCH_STATUS = 0
	BEGIN
	
	SET @SQL = CONCAT('ALTER DATABASE  ', QUOTENAME(@DBName),  ' SET QUERY_STORE = ON (MAX_STORAGE_SIZE_MB = 4096);')
	PRINT @SQL
	exec sp_executesql @sql
	FETCH NEXT FROM db_cursor
	INTO @DBName
	END

CLOSE db_cursor
DEALLOCATE db_cursor

PowerShell via DBATools:

PowerShell can be used in similar approaches as T-SQL. When I tested this in my SQL Server 2025 lab that had a contained AG, I realized that the script attempts to enable Query Store on the contained system databases that are part of the contained AG. Those contained system databases can be excluded by name filtering, and maybe other methods as well.

Enabling Query Store on All Databases or a Single One


Set-DbaToolsInsecureConnection –SessionOnly
Set-DbaDbQueryStoreOption -SqlInstance SQL01 -State ReadWrite `
-FlushInterval 900 -CollectionInterval 60 -MaxSize 4096 `
-CaptureMode Auto -CleanupMode Auto -StaleQueryThreshold 30 `
-AllDatabases #(or use –database and provide a list of DB names)

PowerShell with a Central Management Server


$SQLInstances = Get-DbaRegServer -Group 'prod'

Set-DbaToolsInsecureConnection –SessionOnly
Set-DbaDbQueryStoreOption -SqlInstance $SQLInstances -State ReadWrite `
-FlushInterval 900 -CollectionInterval 60 -MaxSize 4096 `
-CaptureMode Auto -CleanupMode Auto -StaleQueryThreshold 30 `
-AllDatabases -WhatIf #(or use –database and provide a list of DB names)

PowerShell with a text file with a list of servers



Set-DbaToolsInsecureConnection –SessionOnly
$SQLInstances = Get-Content -Path "C:\Users\Public\Documents\ServerList.txt"


Set-DbaDbQueryStoreOption -SqlInstance $SQLInstances -State ReadWrite `
-FlushInterval 900 -CollectionInterval 60 -MaxSize 4096 `
-CaptureMode Auto -CleanupMode Auto -StaleQueryThreshold 30 `
-AllDatabases  #(or use –database and provide a list of DB names)

PowerShell to Call Existing T-SQL Script

I really like this option because most people working with data have a lot of existing SQL scripts. Incorporating PowerShell doesn’t have to mean re-writing everything in PowerShell. You can start by use PowerShell to call your existing T-SQL library. This means all the scripting you’ve already done is still very valuable. Over time, if you want to re-write some of those T-SQL scripts in PowerShell, that’s great, but it isn’t required.


<#
Call an existing T-SQL Script
#>

Set-DbaToolsInsecureConnection –SessionOnly

$SqlInstance ='SQL01'

Invoke-DbaQuery -SqlInstance $SQLInstance -File 'C:\Users\lmarkum\Documents\codebank\CursorToEnableQueryStore_AllDatabases.sql'

Conclusion

There are usually a variety of ways to get something done. Pick something familiar, or try something new and pick a method you might be slightly less familiar with. Either way, give these a try in your environment.