5 Best Practices for the Query Store in SQL Server 2016

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.