Feb 01, 2017

With the unveiling of SQL Server 2016 came a few new features, one of these features is the Query Store. The Query Store helps us to store snapshots of information from the plan cache. The main reason for adding the Query Store is to assist us in troubleshooting performance issues.

By enabling the Query Store in SQL server (deactivated by default), it will automatically start gathering historical information on queries, plans and runtime statistics. We retain the information for future analysis that can help us to see if specific query plan usage has changed or to identify database usage.

The Query Store works at a database level, so you can control how much data it gathers for the chosen database. Even though this feature works at a database level, it is specifically designed for user databases and not for system databases; so you cannot switch it on for the master or tempdb databases.

To start working with the Query Store you have to turn it on, this is achieved by using the ALTER DATABASE SET QUERY STORE ON command or you can use the Database Properties window in SQL Server Management Studio (SSMS). Once enabled, you will see a new node appear under the corresponding database called Query Store. By Expanding the Query Store node you will see the following reports:

• Regressed Queries – Report showing queries that have been declining in performance
• Overall Resource Consumption – Histograms representing resource consumption during a time period
• Top Resource Consuming Queries – Report showing the most expensive queries during a time period
• Tracked Queries – Show historical data for a single query in the Query Store

Another added benefit of working with the Query Store is that you can force a particular query plan to be used based off the historical information that was captured, so even if the optimiser chose a different plan you can override it with by using the Force Plan feature.

The Query Store can be configured to meet your storage and capture requirements better by setting the following options:

• Max Size – The maximum size that the Query Store data may grow. When the Max Size threshold is met your Query store will go into read-only mode.
• Capture Mode
  • All – All Queries
  • Auto – Expensive queries only
  • None – No data Collected
• Cleanup Mode – When turned on, Query store will remove data to reduce the actual size when it starts reaching the max size threshold.

To conclude this blog, I will cover some of the Dynamic Management Views (DMVs) that can be used to expose the data collected by the Query Store. The before mentioned DMVs are:

• sys.query_store_plan – Exposes query plans captured by the Query Store
• sys.query_store_runtime_stats – Exposes performance information gathered by the Query Store.

With all the information in this blog, you will now be able to get started with the Query Store in your own environment and capture performance data to be analysed at a later stage.

For more information, take a look at New Horizons' SQL Server 2016 training courses.