Author: Onur Kulabas
Date: 06 Dec, 2021

Query Store

Query Store is a new feature introduced in SQL Server 2016. It can be considered as a flight recorder or a black box; which captures the history of executed queries, query execution statistics, execution plans and even compares the old and current plans and force the old ones in the queries. This function helps troubleshoot and identify performance problems caused by query plan changes. SQL Server Query Store capture data are stored on disk.

Query Store is not enabled by default for new SQL Server databases, but enabled by default for new Azure SQL Database databases.

To enable the query store in your database, you should execute this query;

ALTER DATABASE [YourDBName] SET QUERY_STORE = ON

Statistics collection interval is set to 1 hour (60 minutes) by default. We prefer much shorter periods usually, but it can differ on your requirements and environment. you can change this interval by this query;

ALTER DATABASE CURRENT SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 1)

After enabling the Query Store, in SQL Server Management Studio you should refresh the database portion of the Object Explorer pane to get the Query Store section. You can use the SQL Server Management Studio interface functionalities of Query Store (Query Wait Statistics, Regressed Queries etc.) or you can use the Query Store functions such as sys.fn_stmt_sql_handle_from_sql_stmt. A simple example of its usage is as below;

SELECT * FROM sys.databases -- You execute your query
SELECT * FROM sys.fn_stmt_sql_handle_from_sql_stmt('SELECT * FROM sys.databases', NULL) -- You execute the Query Store function by sending the query that you just executed as a parameter

Pinea Blog

Follow us for the latest technology news!

SQL JSON_VALUE
06Dec
Query Store
 

Query Store is a new feature introduced in SQL Server 2016. It can be considered as a flight recorder or a black box...

Continue Reading ...
SQL JSON_VALUE
19Nov
Creating Database Users
without Logins

Contained databases include all database settings and metadata to define the database and has no ...

Continue Reading ...
PM2
30Oct
PM2

PM2 is an advanced process manager for the JavaScript runtime Node.js. You can manage your node packages on...

Continue Reading ...
SQL JSON_VALUE
24Oct
SQL Server
JSON_VALUE Function

JSON_VALUE function can be used to extract scalar value from a given JSON string on SQL Server...

Continue Reading ...
CORS
21Oct
What is
CORS?

Cross-origin resource sharing (CORS) is the mechanism that manages the cross-domain requests, ...

Continue Reading ...