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