Query Store

Query Store:  This is used for performance troubleshooting and check the optimal execution plan used and if a query is taking longer than expected time.

Description

It is a relatively new feature introduced in SQL 2016 (every SQL Server edition). 

It is basically a SQL Server “flight recorder” or “black box”, capturing a history of executed queries, query runtime execution statistics, execution plans etc. against a specific database.

Query Store is per-database-level feature which means that it can be enabled on every SQL database separately by using SQL Server Management Studio or T-SQL. It is not an instance level setting.

This information helps in identifying performance problems caused by query plan changes and troubleshooting by quickly finding performance differences, even after SQL Server restart or upgrade.

All data that SQL Server Query Store capture are stored on disk.

Here are the common scenarios where the SQL Server Query Store feature can be useful:

 Find the most expensive queries for CPU, I/O, Memory etc.

Get full history of query executions.

Get information about query regressions (a new execution plan generated by query engine is worse than the older one).

Quickly find performance regression and fixing it by forcing the previous query plan whose performances are much better than a newly generated plan

Determine how many times a query was executed in the given range of time.