Select Page

Top SQL Server 2016 Features: Query Store

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.

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
code

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database

Megan Elphingstone | February 2, 2017

Best RAID For SQL Server | RAID 0, RAID 1, RAID 5, RAID 10

Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.

Eric Russo | June 8, 2015

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