Monday, 5 October 2020

Query Store

SQL Server's Query Store is a database level feature available in every edition. It captures a history of queries run, their query plans, their execution statistics etc, to help performance troubleshooting, by helping to identify regressed query plans, better query plans. Essentially a "black box recorder" for SQL Server.

To turn on Query Store:

USE [master] 

GO 

ALTER DATABASE [DatabaseName] SET QUERY_STORE = ON 

GO 

ALTER DATABASE [DatabaseName] SET QUERY_STORE (OPERATION_MODE = READ_WRITE) 

GO 

You'll then notice a new folder within SSMS under the database you enabled it for:



Resources:



More info on setting up and using Query Store: https://ballardchalmers.com/2019/07/23/query-store-sql-server/

No comments:

Post a Comment

Updating massive amount of rows whilst avoiding blocking

The following SQL is a good means to split an update on a massive table into smaller chunks, whilst reducing blocking. The method is to upda...