{"id":44942,"date":"2022-09-08T08:00:00","date_gmt":"2022-09-08T15:00:00","guid":{"rendered":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?p=44942"},"modified":"2024-01-31T13:44:22","modified_gmt":"2024-01-31T21:44:22","slug":"query-store-hints-in-sql-server-2022","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2022\/09\/08\/query-store-hints-in-sql-server-2022\/","title":{"rendered":"Query Store hints in SQL Server 2022"},"content":{"rendered":"\n

Part of the SQL Server 2022 blog series<\/a><\/em>.<\/p>\n\n\n\n

Query Store<\/a> 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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

In the previous Query Store on by default<\/a> blog, we covered how Query Store will now be enabled by default for new databases created on SQL Server 2022.  <\/p>\n\n\n\n

As a result, in SQL Server 2022<\/a>, we will be taking advantage of Query Store’s ability to store query-related metadata in order to bring forward several powerful new features. <\/p>\n\n\n\n

The first feature that we will discuss in this series, is the ability to store and persist Query Store hints<\/a> 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.<\/p>\n\n\n\n

In fact, you can even use Query Store hints without having to change your database compatibility level.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Influencing Query plan behavior<\/h2>\n\n\n\n

Before we jump into Query Store hints, we should review the concept of query hints<\/a> 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 <\/strong>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.<\/p>\n\n\n\n

Why change query plan behavior?  <\/h2>\n\n\n\n

Ideally, the Query Optimizer selects an optimal execution plan for a query. This is what it\u2019s designed to do\u2014but this doesn\u2019t 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.<\/p>\n\n\n\n

We may have a stored procedure, for example, where the cardinality of a SELECT <\/strong>can vary wildly causing you to need to use a RECOMPILE <\/strong>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<\/a> 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<\/strong> level.<\/p>\n\n\n\n

In short, there are many reasons you may need to influence plan behavior.<\/p>\n\n\n

\"Code<\/figure>\n\n\n\n

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.  <\/p>\n\n\n\n

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<\/code><\/a>.<\/p>\n\n\n\n

Applying Query hints today<\/h2>\n\n\n\n

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. <\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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\u202fplan guides<\/a>, which were notoriously difficult to use.<\/p>\n\n\n\n

What are Query Store hints?<\/h2>\n\n\n
\"Five<\/figure>\n\n\n\n

Query Store hints provide a direct method for developers and DBAs to shape query plans without changing application code.  <\/p>\n\n\n\n

Query Store hints are a new feature that extends the power of Query Store\u2014but 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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Query Store hints override other hard-coded statement-level hints and plan guides.<\/p>\n\n\n\n

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 <\/strong>hint is not compatible with forced parameterization set at the database level, but this does not cause the query to fail.<\/p>\n\n\n\n

If the database has forced parameterization set, and the RECOMPILE <\/strong>hint is part of the hints string set in Query Store for a query, SQL Server will ignore the RECOMPILE <\/strong>hint and will apply any other hints as they are leveraged.<\/p>\n\n\n

\"Arrows<\/figure>\n\n\n\n

Using Query Store hints<\/h2>\n\n\n\n

The lifecycle for Query Store Hints follows these basic steps:<\/p>\n\n\n\n

    \n
  1. First, the query must be executed.<\/li>\n\n\n\n
  2. 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<\/strong><\/a>.<\/li>\n\n\n\n
  3. The DBA creates a Query Store hint on a query using sp_query_store_set_hints<\/strong><\/a>.<\/li>\n\n\n\n
  4. The query is executed using the Query Store hint.<\/li>\n<\/ol>\n\n\n\n

    To use Query Store hints, do the following:<\/p>\n\n\n\n

      \n
    1. Identify the Query Store query_id<\/strong> of the query statement you wish to modify. You can do this in various ways:\n