·
4 min read

Tuning Workload Performance with Query Store

As your database grows, it is not unusual to experience performance problems caused by queries that once performed well, but now perform poorly. Without the ability to see all the changes in the query execution over time, it is difficult to figure out why regressions happen and what you can do to prevent them. Troubleshooting these performance issues can take hours or even days.

That’s exactly what Query Store, a new feature available in Azure SQL Database and SQL Server 2016, aims to improve.

Similar to an airplane’s flight data recorder, Query Store constantly collects information about all queries and greatly simplifies performance forensics by reducing the time to diagnose and resolve issues. Query Store also allows you to force query plans from the history, which makes the process of fixing problems with plan choice regressions extremely easy.

As a result, time to detect and mitigate performance issues is now in the range of minutes.

Typical usage scenarios

Query Store can be used in a wide set of scenarios when tracking and ensuring predictable workload performance is critical. It is equally useful when you need to react to an immediate problem as well as when you want to ensure optimal performance proactively for the long term (after the database upgrade, for other maintenance operations, or upon new application roll-out, for example).

The following diagram depicts typical usage scenarios from the perspective of proactive/reactive mode of operation:

Here is a brief scenario description:

  • Fixing plan regressions: When you have queries whose recent performance is significantly worse because of plan choice changes, you can use Query Store to quickly identify and fix situations with the plan forcing mechanism.
  • Identifying top resource consuming queries: Learn which queries use the most system resources (CPU, memory, IO) and focus all your efforts on optimizing those.
  • A/B testing: Use Query Store to compare workload performance before and after the application or platform change you plan to introduce, and conclude whether impact on performance is acceptable. You can use Query Store in a wider set of performance assessment scenarios such as
    • Testing new application versions
    • Adding new hardware to the server
    • Changing the database compatibility level
    • Creating/modifying indexes
  • Reducing the risk of upgrade: Use database compatibility level and Query Store to avoid plan changes at point of upgrade and record performance baseline with the current version of the Query Optimizer. Leverage Query Store to quickly fix performance of the regressed queries when you decide to move to the Query Optimizer available on the latest SQL Server version.
  • Improving ad-hoc workloads: Use Query Store to identify dominant execution patterns in your workload by analyzing query frequency. If the majority of the queries are ad hoc (executed once or very rarely), significant system resources are spent on the compilation, so you may want to optimize resource utilization by forcing parametrization or applying some other techniques.

For more details, refer to Query Store Usage Scenarios on MSDN.

Getting started with the Query Store

Query Store is a database-scoped feature, so you must first enable it for the databases that you want to monitor. You can enable Query Store very easily from the new UI in SQL Server Management Studio (SSMS) or by running a simple Transact-SQL script:

ALTER DATABASE <database_name> SET QUERY_STORE = ON;

Once you have enabled the Query Store, the next thing to do is to analyze the data and tune “problematic” queries. It will take some time until Query Store collects the data set that accurately represents your workload. Usually, one day is enough even for very complex workloads, but you should adjust the time based on execution patterns in your application. Even so, you can start exploring the data and identifying the queries that need your attention immediately after enabling the feature.
The easiest way to analyze the data is by using a set of built-in views available in the latest SQL Server Management Studio. Navigate to the Query Store sub-folder under the database node in Object Explorer of Management Studio to open troubleshooting views for specific scenarios:

SSMS view

 

Scenario

 

Regressed queries

 

Pinpoint queries for which execution metrics have recently regressed (i.e. changed to worse). Use this view to correlate observed performance problems in your application with the actual queries that need to be fixed or improved.

 

Top resource consuming queries

 

Choose an execution metric of interest and identify queries that have the most extreme values for a provided time interval. Use this view to focus your attention on the most relevant queries that have the biggest impact to database resource consumption.

 

Tracked queries

 

Track the execution of the most important queries in real-time. Typically, you use this view when you have queries with forced plans and you want to make sure that query performance is stable.

 

Overall resource consumption

 

Analyze the total resource consumption for the database for any of the execution metrics. Use this view to identify resource patterns (daily vs. nightly workloads) and optimize overall consumption for your database.

 

For more details, check out How to Start with Performance Troubleshooting on MSDN. Please note, you can write your own Transact-SQL scripts for the custom data analysis. Refer to How Query Store collects the data on MSDN to learn about data structures presented by the Query Store and their relations.

The Performance Auditing and Troubleshooting section in Key Usage Scenarios at MSDN provides more examples on using Query Store views in the custom scripts. The Query Store: A flight data recorder for your database article contains additional scripts for troubleshooting.

If you run Query Store on the on-premises instance of SQL Server, you can download AdventureWorks Database for SQL Server 2016 CTP3 with script samples. Query Store is already enabled on the sample database while SQLServer2016CTP3Samples.zip contains examples showcasing how you can query data collected by the Query Store (follow the instructions in the Query Store folder).

See other posts in the SQL Server 2016 blogging series.

Try SQL Server 2016 RCLearn more