Enable or Disable Query Store in SQL Server

Check the Query Store is enabled or disabled for the databases in SQL Server

Query Store helps in identifying performance caused by query plan changes.
It captures a history of queries, plans, and runtime statistics, and retains these for your review.

Check whether the Query Store is enabled or disabled for the database:

select name,database_id,is_query_store_on from sys.databases

--Check the query store different options:
select * from  sys.database_query_store_options

Enable the Query Store in SQL Server:

USE [master]
GO
ALTER DATABASE [AdventureWorks2017] SET QUERY_STORE = ON
GO
ALTER DATABASE [AdventureWorks2017] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
GO


OR 

ALTER DATABASE [database_name]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60
    );

Disable the Query Store in SQL Server

USE [master]
GO
ALTER DATABASE [AdventureWorks2017] SET QUERY_STORE = OFF

OR 
-- Force fully disable:
ALTER DATABASE [DBName] SET QUERY_STORE = OFF (FORCED)

Manually clear the query store cache data:

ALTER DATABASE [DBName] SET QUERY_STORE CLEAR; 

Check query from query store:

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id;

Script for enabling or disabling all the databases for Query Store:

-- Enable all the databases in the server:

-- Read write mode
SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);'
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
AND is_query_store_on = 0;

--Read only mode:
SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = ON (OPERATION_MODE = READ_ONLY);'
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
AND is_query_store_on = 0;

--Disable all the databases:
SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = OFF;'
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
AND is_query_store_on = 1;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.