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.

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
sharepoint ideas

9 Awesome Things You Can Do with SharePoint

This blog post discusses out-of-the-box uses for SharePoint that you can execute in just a few clicks without using any code.

Amol Gharat | May 23, 2017

Tips for Upgrading From SQL 2008 to 2012 or 2014

It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.

Andy McDermid | April 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