Query Store hints in SQL Server 2022
Part of the SQL Server 2022 blog series.
Query Store is one of the most powerful database-scoped features in SQL Server for troubleshooting performance and improving the stability of your database workloads, and we have continued to make investments in this technology since its release in SQL Server 2016.
Query Store is often described as a “flight recorder” for SQL Server giving SQL Server the ability to store query text, query plans, and query performance history at the database scope for troubleshooting and performance analysis. Query Store also provides a method to force which plans a query will use which is a capability that allows database administrators (DBAs) to reactively choose a healthier plan in the case of a poorly performing query.
The ability to control which plans a query will use makes it much easier to respond to bad query behavior and provides database administrators a safe and comfortable method to upgrade to later versions of SQL Server, change database compatibility levels, and confidently migrate database applications to the cloud.
In the previous Query Store on by default blog, we covered how Query Store will now be enabled by default for new databases created on SQL Server 2022.
As a result, in SQL Server 2022, we will be taking advantage of Query Store’s ability to store query-related metadata in order to bring forward several powerful new features.
The first feature that we will discuss in this series, is the ability to store and persist Query Store hints empowering a new approach to troubleshooting and stabilizing your application’s database performance without ever having to change a single line of your application’s code.
In fact, you can even use Query Store hints without having to change your database compatibility level.
So, not only can we choose healthier plans using Query Store, but we can now specifically apply hints to queries stored in Query Store to address targeted issues or invoke a specific response.
Influencing Query plan behavior
Before we jump into Query Store hints, we should review the concept of query hints which are currently used to influence query plan behavior. Many of us are already leveraging hints in our code for a number of reasons, for example, to force indexes or recompiles to mitigate unstable query plans. Query hints are specified via the OPTION clause and while query hints help provide solutions to performance-related issues they do require altering the query text meaning we have to change our application’s code.
Why change query plan behavior?
Ideally, the Query Optimizer selects an optimal execution plan for a query. This is what it’s designed to do—but this doesn’t always happen. In these cases, a DBA may need to optimize for specific conditions otherwise we get stuck with a bad plan and a bad plan will often lead to bad performance.
We may have a stored procedure, for example, where the cardinality of a SELECT can vary wildly causing you to need to use a RECOMPILE hint. You may need to put a limit on the memory grant size for a bulk insert operation, you could need to limit the maximum degree of parallelism for a report that runs on the same system as your critical online transactional processing environment, or even use a lower database compatibility level for a particular query to prevent having to lower the compatibility level at the database because you are realizing other benefits at the higher db_compat level.
In short, there are many reasons you may need to influence plan behavior.
In the example shown above, we are using a query hint to disallow batch mode and also telling SQL Server to recompile the query on each execution. It is clear that these targeted approaches may be helpful to solve specific issues, but again, it requires changing code.
Most but not all query hints are supported as Query Store hints. The available query hints are documented in sys.sp_query_store_set_hints.
Applying Query hints today
There are many reasons to leverage query hints, but they require making changes to the application queries and this is usually something that cannot be done without application owner/vendor support.
Database administrators may not always be able to make these changes directly to the T-SQL code. This is true for many production environments and is definitely the case for shrink-wrap vendor-based solutions.
There haven’t been many options for DBAs who are looking for a direct and safe way to influence query behavior without changing application code. Previously, they had to rely on plan guides, which were notoriously difficult to use.
What are Query Store hints?
Query Store hints provide a direct method for developers and DBAs to shape query plans without changing application code.
Query Store hints are a new feature that extends the power of Query Store—but this means that Query Store hints does require the Query Store feature to be enabled and that your query and query plan are captured in the Query Store.
Just like plan guides, Query Store hints are persisted and will survive restarts, but Query Store hints are much easier to use than plan guides.
Query Store hints override other hard-coded statement-level hints and plan guides.
Query stability is important, so with Query Store hints, queries will always execute as opposing Query Store hints will be ignored. For example, one thing to know is that the RECOMPILE hint is not compatible with forced parameterization set at the database level, but this does not cause the query to fail.
If the database has forced parameterization set, and the RECOMPILE hint is part of the hints string set in Query Store for a query, SQL Server will ignore the RECOMPILE hint and will apply any other hints as they are leveraged.
Using Query Store hints
The lifecycle for Query Store Hints follows these basic steps:
- First, the query must be executed.
- The query, plan, and execution details are then captured into the Query Store. This is dependent on the current Query Store capture policy, which can be customized using QUERY_CAPTURE_POLICY.
- The DBA creates a Query Store hint on a query using sp_query_store_set_hints.
- The query is executed using the Query Store hint.
To use Query Store hints, do the following:
- Identify the Query Store query_id of the query statement you wish to modify. You can do this in various ways:
- Querying the Query Store catalog views.
- Using SQL Server Management Studio built-in Query Store reports.
- Using Azure portal “Query Performance Insight” for Azure SQL Database.
- Execute
sp_query_store_set_hintswith the query_id and query hint string you wish to apply to the query.
The query hint string can contain one or more query hints as can be seen in the example below:
EXEC sys.sp_query_store_set_hints @query_id = 39,
@query_hints = N'OPTION(RECOMPILE, MAXDOP 1,
USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';
Query Store hints override hard-coded statement level hints and existing plan guide hints.
Database administrators can clear hints using sp_query_store_clear_hints by passing in the @query_id.
If no Query Store hint exists for a specific query_id, a new Query Store hint will be created and if a Query Store hint already exists for a specific query_id, the last value provided will override previously specified values for the associated query.
If a query_id doesn’t exist, an error will be raised as it is required that the query exists in Query Store.
If a query hint contradicts what is possible for query optimization, the hint will not block query execution and the hint will not be applied. In the cases where a hint would cause a query to fail, the hint is ignored, and the latest failure details can be viewed in sys.query_store_query_hints.
You can review the current Query Store hints in sys.query_store_query_hints and review any failures using the query below:
SELECT query_hint_id, query_id, query_hint_text,
last_query_hint_failure_reason, last_query_hint_failure_reason_desc,
query_hint_failure_count, source, source_desc
FROM sys.query_store_query_hints;
GO
When hints are applied, Execution Plan XML attributes will surface in the StmtSimple element of the Execution Plan in XML format. These XML elements are available via the output of the Transact-SQL commands SET STATISTICS XML and SET SHOWPLAN XML.
The QueryStoreStatementHintText will show the actual Query Store hint(s) that have been applied to the query, the QueryStoreStatementHintId will show the unique identifier of a query hint, and the QueryStoreStatementHintSource will surface the source of the Query Store hint (example: “User”).
Next steps
Query Store “on by default” is just one of the many benefits of migrating to SQL Server 2022.
Download the latest release of SQL Server 2022 if you haven’t already done so and check out the SQL Server 2022 Overview and What’s New references. There are many new features and improved functionality being added to this release.
Learn more
For more information and to get started, check out the following reference:
Read What’s New in SQL Server 2022.
