Top SQL Server 2016 Features: Query Store
Author: Manguirish Sardesai | 4 min read | June 26, 2019
Microsoft SQL Server 2016 includes a new Query Store feature, which provides DBAs with insight into query plan choice and performance.
This feature is a new, simple approach to troubleshooting performance issues with queries by providing a simple way to connect differences in performance to changes in query plans. This feature automatically captures and retains historical performance data, including:
- Queries
- Plans
- Runtime Statistics
Within the Query Store, the data is separated and cataloged by windows so a DBA can quickly review patterns and understand when changes occurred that caused a negative impact on performance. For enterprises with a large volume of performance data, this feature also includes an “ALTER DATABASE SET” option for configuration.
Do You Need the Query Store?
The Query Store feature is not enabled by default for new installations of Microsoft SQL Server 2016. The standard configuration for this feature can be simply activated by opening Object Explorer and selecting “properties.” Next, select the Query Store page and select read-write in the Operation Mode (Requested) box. Some of the most common operational use cases for the Query Store feature include and aren’t limited to:
- Quickly finding and resolving performance issues by the previous query plan
- Fixing queries which have experienced poor performance due to execution plan changes
- Determining how many times a query was executed in a given period of time
- Identifying the top queries by execution time, memory consumption or other variables
- Auditing a query’s plan history
- Learning database resource utilization, such as CPU, I/O and memory
- Understanding a particular query or plan’s wait nature
Maintain Stable Performance in a Dynamic Environment
Maintaining stable performance can be challenging in a dynamic environment. DBAs commonly notice that SQL Server 2016 and prior versions use different plans queries which are executed multiple times and each of these plans may use a different amount of resources. Query Store allows DBAs to understand when performance regressed, and which plan is the best, and then “force” that plan for future executions.
Query Store features can also reveal inconsistent performance issues for a query and identify the plan which is the most optimal within parameters and force this plan to maintain stable performance over time.
Optimizing Performance with the Query Store
The Query Store feature in Microsoft SQL Server 2016 has been compared to a “flight data recorder,” like the all-encompassing data collection tool used in commercial aircraft. The feature offers clear benefits to any organization that wants to maintain optimal and stable query performance by providing insight into performance, plan changes, variables, and giving DBA the capability to force plans by query. To learn how to make the most of this feature, we recommend the blog 5 Best Practices for the Query Store in SQL Server 2016.
