What is a SQL Server Query Store and how do you enable it?

This is a new feature in SQL Server 2016 that provides with an insight into query plan choice and performance. As the query plan changes it helps you find the performance differences caused by the change.

Query store is automatic in that Query Store captures a history of queries, plans and runtime statistics for later review. It separates information chronologically by time windows so that you can view database usage patterns and monitor query plan changes that has taken place.

You need to enable the database to use this feature which an be done using SQL Server Management Studio as well as T-SQL Alter Database Set Option.

How do you turn it on using SQL Server Management Studio?

Since it is database related right click the database node after launching the SQL Server Management Studio and connecting to the server instance. The next image shows the result of right clicking the AdventureWorks 2014 database in SQL Server 2014.

QS_00.png

Click Properties to open the next window as shown.

QS_01

The Query Store Property has the following items:
General:
    Operation mode(actual)       default Off(disabled)
    Operation mode (Requested)   default Off but can be Read only or Read/write
Monitoring
    Data Flush Interval (Minutes)
    Statistics Collection Interval
Query Store Retention
    Max Size(MB)
    Query Store Capture Mode
    Size-based cleanup Mode
    Stale query Threshold(Days)

The previous window also shows the Current Disk Usage for database as well as Query Store.
As it is not enabled, the Query Store used is 0.0. You also have the option to purge the data.

You enable the Query Store feature by changing the Operation Mode(requested) from OFF to ON as shown.

QS_02

After it is enabled, a Query Store is added to the database as shown:

QS_03a.png