Select Page

Top SQL Server 2016 Features: Query Store

Author: Manguirish Sardesai | | 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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS