Select Page

5 Best Practices for the Query Store in SQL Server 2016

Manguirish Sardesai | | February 8, 2019

The Query Store feature in SQL Server 2016 makes the troubleshooting process dramatically simpler. Often referred to as a metaphorical “flight recorder” for SQL Server, the Query Store automatically keeps track of information such as query history, query execution plans, and runtime execution statistics.

Like any complex tool, the Query Store must be used capably and correctly in order to realize the greatest benefit. In this article, we’ll discuss 5 best practices for using the Query Store in SQL Server 2016.

1. Install the Latest SSMS

SQL Server Management Studio (SSMS) is a software application for configuring, managing, and administering SQL Server. The latest versions of SSMS include a set of user interfaces for configuring the Query Store, as well as for consuming the data that you’ve collected about your workloads.

2. Keep the Query Store Adjusted to Your Workload

The default configuration of the Query Store should be enough to get you started. To get the most out of the tool, however, you’ll have to do some tweaking and performance tuning.

Some of the parameters that you might want to modify are:

  • Max Size (MB): The maximum limit that the Query Store will take inside your database, with a default value of 100 megabytes. You may wish to increase this limit if you generate many different queries or you want to preserve query history for a longer period of time.
  • Data Flush Interval (seconds): The interval between saving the statistics that the Query Store has collected to disk, with a default value of 900 seconds (15 minutes).
  • Stale Query Threshold (days): The interval during which persisted runtime statistics and inactive queries are retained, with a default value of 30 days.

3. Make Sure the Query Store is Collecting Data Continuously

It’s not uncommon for the Query Store to silently switch to read-only mode, often because the Max Size quota was exceeded. You need to monitor the Query Store at regular intervals to verify that it is still operating and collecting data.

Use the query below to ensure that the Query Store is still operating:

USE [QueryStoreDB];

GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,

max_storage_size_mb, readonly_reason, interval_length_minutes,

stale_query_threshold_days, size_based_cleanup_mode_desc,

query_capture_mode_desc

FROM sys.database_query_store_options;

If the Query Store has silently changed states, the actual_state_desc and desired_state_desc parameters will be different.

4. Avoid Non-Parameterized Queries

Not only are non-parameterized queries susceptible to SQL injection attacks, they also create inefficiencies. Because the Query Store cannot reuse cached plans, the Query Optimizer must compile queries for each unique query text, which can rapidly deplete the Max Size quota.

Queries should be parameterized whenever possible, such as by wrapping them inside a stored procedure or sp_executesql. In addition, the best practice is to enable the “Optimize for Ad Hoc Workloads” option if you have many single-use ad hoc batches with different query plans inside your workload.

5. Perform Regular Checkups on Forced Plans

Plan forcing can help improve the performance and predictability of critical queries. However, it may fail when the database schema changes such that an object referenced by the plan is altered or dropped.

The query below allows you to check on the status of your forced plans:

USE [QueryStoreDB];

GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,

force_failure_count, last_force_failure_reason_desc

FROM sys.query_store_plan AS p

JOIN sys.query_store_query AS q on p.query_id = q.query_id

WHERE is_forced_plan = 1;

Is it time for an upgrade?

SQL Server 2016 is no longer the latest version. To unlock even more SQL server capabilities and features, consider making the move to SQL Server 2017. Download our SQL Server 2017 white paper to learn more.

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
Oracle DBA Skills

8 Things Every Beginner Oracle DBA Should Know

A checklist of eight critical skills and areas an entry-level Oracle DBA should be familiar with to succeed on the job.

Patrick Gates | November 30, 2016

Shortcut to Fixing a Corrupted Oracle Inventory

Oracle Central Inventory files are essential to installing and patching software, ensuring it’s operating correctly and is critical to database stability and availability. The process for fixing a corrupted file can be found here.

Chad Cleveland | February 23, 2016

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