Query Store in SQL Server 2016

The Query Store is one of the biggest enhancements to SQL Server 2016 database engine that will complete the performance tuning toolbox of database administrators and simplifies identification of performance outliers, manages execution plan regression, and allows for easier upgrades between versions of SQL Server. The query store also acts as a flight data recorder for the database, capturing query run-time statistics and providing a dashboard to sort queries by resource consumption.

The query store gives unprecedented insight into the operations of a database and helps to troubleshoot query performance by capturing a range of information about query usage, CPU, memory consumption, I/O and execution time, and retaining every Execution Plan for analysis.

Query Store Architecture

As per the Query Store Architecture, Query Store is the combination of two stores known as Plan Store and Runtime Stats Store and these stores are in-memory objects that hold query information as given below.

What does happen? when a query submitted in Query Store enabled database, first of all, the compiled query Execution Plan is written to the Query Store in Plan Store section and the runtime information of the query is recorded in the Runtime Stats Store section inside the in-memory stores.

After a specific interval these information inside the in-memory stores is asynchronously written to disk to harden the information. The Query Store writes the Execution Plans and runtime statistics into the primary file group of the database that we queried against.

Enabling the Query Store

This feature is disabled by default in the local databases. The Query Store requires at least SQL Server 2016 version of Management Studio and to enable it, we need to follow the following instructions in SQL Server Management Studio (SSMS) –

1. Open Object Explorer and connect to the database engine,
2. Navigate to the database, for which we want to enable Query Store,
3. Right-click the database, selects Properties, and then clicks Query Store in the Database Properties dialog box as given below-

In the Operation Mode (Requested) value from Off to Read Only or Read Write. By selecting Read Write, we enable Query Store to record the run-time information necessary to make better decisions about queries.

After enabling our Query Store, we can see the Query Store within our database as given below-

Besides setting all the options through the Query Store GUI, we can also configure all of these options through T-SQL.

---Enabling Query Store ALTER DATABASE Demo SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE , CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 5 ) , DATA_FLUSH_INTERVAL_SECONDS = 2000 , MAX_STORAGE_SIZE_MB = 10 , INTERVAL_LENGTH_MINUTES = 10 );

Understanding Query Store Components
Conclusion

The Query Store helps us to get insights on query and overall performance of a database and greatly simplifies performance troubleshooting by helping us quickly find performance differences, even after a server restart or upgrade as it persists this information inside internal tables of the database and captures query text as well as the query execution plan. It helps us to identify queries that have a high runtime variance by doing comparison over time.