---
title: "Query Store Hints"
description: "Learn about the Query Store hints feature, which can be used to shape query plans without changing application code."
author: MikeRayMSFT
ms.author: mikeray
ms.reviewer: randolphwest
ms.date: 07/25/2025
ms.service: sql
ms.subservice: performance
ms.topic: concept-article
ms.custom:
  - ignite-2024
helpviewer_keywords:
  - "Query Store hints"
dev_langs:
  - "TSQL"
monikerRange: "=azuresqldb-current || =azuresqldb-mi-current || >=sql-server-ver16 || >=sql-server-linux-ver16 || =fabric"
---

# Query Store hints

[!INCLUDE [SQL Server 2022 Azure SQL Database Azure SQL Managed Instance FabricSQLDB](../../includes/applies-to-version/sqlserver2022-asdb-asmi-fabricsqldb.md)]

This article outlines how to apply query hints using the Query Store. Query Store hints provide an easy-to-use method for shaping query plans without changing application code.

- For more information on configuring and administering with the Query Store, see [Monitor performance by using the Query Store](monitoring-performance-by-using-the-query-store.md).
- For information on discovering actionable information and tune performance with the Query Store, see [Tune performance with the Query Store](tune-performance-with-the-query-store.md).
- For information about operating the Query Store in Azure [!INCLUDE [ssSDS](../../includes/sssds-md.md)], see [Operating the Query Store in Azure SQL Database](best-practice-with-the-query-store.md#Insight).

> [!CAUTION]  
> Because the SQL Server Query Optimizer typically selects the best execution plan for a query, we recommend only using hints as a last resort for experienced developers and database administrators. For more information, see [Query hints](../../t-sql/queries/hints-transact-sql-query.md).

Watch this video for an overview of Query Store hints:

> [!VIDEO https://channel9.msdn.com/Shows/Data-Exposed/Query-Store-Hints-in-Azure-SQL-Database/player?WT.mc_id=dataexposed-c9-niner]

## Overview

Ideally, the Query Optimizer selects an optimal execution plan for a query.

If an optimal plan isn't selected, a developer or database administrator (DBA) might wish to manually optimize for specific conditions. Query hints are specified via the `OPTION` clause and can be used to affect query execution behavior. While query hints help provide localized solutions to various performance-related issues, they do require a rewrite of the original query text. Database administrators and developers might not always be able to make changes directly to [!INCLUDE [tsql](../../includes/tsql-md.md)] code to add a query hint. The [!INCLUDE [tsql](../../includes/tsql-md.md)] might be hard-coded into an application or automatically generated by the application. Previously, a developer might have to rely on [plan guides](plan-guides.md), which can be complex to use.

Query Store hints solve this problem by letting you inject a query hint into a query without modifying the query [!INCLUDE [tsql](../../includes/tsql-md.md)] text directly. For information on which query hints can be applied, see [Supported query hints](../system-stored-procedures/sys-sp-query-store-set-hints-transact-sql.md#supported-query-hints).

## When to use Query Store hints

As the name suggests, this feature extends and depends on the [Query Store](monitoring-performance-by-using-the-query-store.md). Query Store enables the capturing of queries, execution plans, and associated runtime statistics. Query Store greatly simplifies the overall performance tuning customer experience. [!INCLUDE [sssql16-md](../../includes/sssql16-md.md)] first introduced Query Store, and now it's enabled by default in [!INCLUDE [sssql22-md](../../includes/sssql22-md.md)], [!INCLUDE [ssazuremi-md](../../includes/ssazuremi-md.md)], [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)], and [!INCLUDE [fabric-sqldb](../../includes/fabric-sqldb.md)].

:::image type="complex" source="media/query-store-hints.png" alt-text="The workflow for Query Store Hints.":::
      First the query is executed, then captured by Query Store. Then the DBA creates a Query Store hint on a query. Thereafter, the query is executed using the Query Store hint.
:::image-end:::

Examples where Query Store hints can help with query-level performance issues:

- Recompile a query on each execution.
- Cap the memory grant size for a bulk insert operation.
- Limit the maximum degree of parallelism when updating statistics.
- Use a Hash join instead of a Nested Loops join.
- Use [compatibility level](../databases/view-or-change-the-compatibility-level-of-a-database.md) 110 for a specific query while keeping everything else in the database at compatibility level 150.
- Disable row goal optimization for a `SELECT TOP` query.

To use Query Store hints:

1. 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 (Transact-SQL)](../system-catalog-views/query-store-catalog-views-transact-sql.md).
   - Using [!INCLUDE [ssManStudioFull](../../includes/ssmanstudiofull-md.md)] built-in Query Store reports.
   - Using Azure portal Query Performance Insight for [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)].

1. Execute `sys.sp_query_store_set_hints` with the `query_id` and query hint string you wish to apply to the query. This string can contain one or more query hints. For complete information, see [sys.sp_query_store_set_hints](../system-stored-procedures/sys-sp-query-store-set-hints-transact-sql.md).

Once created, Query Store hints are persisted and survive restarts and failovers. Query Store hints override hard-coded statement-level hints and existing plan guide hints.

If a query hint contradicts what is possible for query optimization, query execution isn't blocked and the hint isn't 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](../system-catalog-views/sys-query-store-query-hints-transact-sql.md).

### Before you use Query Store hints

Consider the following before you start using Query Store hints.

- Complete [statistics maintenance](../statistics/statistics.md#UpdateStatistics) and [index maintenance](../indexes/reorganize-and-rebuild-indexes.md) (if necessary) before evaluating queries for potential new Query Store hints. Statistics maintenance, and to a lesser degree index maintenance might resolve the problem that requires a query hint otherwise.
- Before using Query Store hints, test your application database on the latest [compatibility level](../../t-sql/statements/alter-database-transact-sql-compatibility-level.md) to see if that solves the problem that requires a query hint.
  - For example, Parameter Sensitive Plan (PSP) optimization was introduced in [!INCLUDE [sssql22-md](../../includes/sssql22-md.md)] under compatibility level 160. It uses multiple active plans per query to address nonuniform data distributions. If your environment can't use the latest compatibility level, Query Store hints using the `RECOMPILE` hint can be used on any supporting compatibility level.
- Query Store hints override the [!INCLUDE [ssDE](../../includes/ssde-md.md)] default query plan behavior. You should only use Query Store hints when it's necessary to address performance related issues.
- You should reevaluate Query Store hints, statement level hints, plan guides, and Query Store forced plans any time data volume and distribution changes and during database migrations projects. Changes in data volume and distribution might cause Query Store hints to generate suboptimal execution plans.

## Query Store hints system stored procedures

To create or update hints, use [sys.sp_query_store_set_hints](../system-stored-procedures/sys-sp-query-store-set-hints-transact-sql.md). Hints are specified in a valid string format `N'OPTION (...)'`.

- When you create a Query Store hint, if no Query Store hint exists for a specific `query_id`, a new Query Store hint is created.
- When you create or update a Query Store hint, if a Query Store hint already exists for a specific `query_id`, the last value provided overrides previously specified values for the associated query.
- If a `query_id` doesn't exist, an error is raised.

For a complete list of hints that are supported as a Query Store hint, see [sys.sp_query_store_set_hints](../system-stored-procedures/sys-sp-query-store-set-hints-transact-sql.md).

To remove hints associated with a `query_id`, use [sys.sp_query_store_clear_hints](../system-stored-procedures/sys-sp-query-store-clear-hints-transact-sql.md).

> [!TIP]  
> You might need to set or clear hints for all `query_id` values matching a query hash.
>
> [dbo.sp_query_store_modify_hints_by_query_hash](https://github.com/microsoft/sql-server-samples/blob/master/samples/features/query-store/sp_query_store_modify_hints_by_query_hash.sql) is a sample stored procedure that calls the `sys.sp_query_store_set_hints` or `sys.sp_query_store_clear_hints` system stored procedure in a loop to accomplish this.

## Execution Plan XML attributes

When hints are applied, the following result set appears in the `StmtSimple` element of the [Execution plan](execution-plans.md) in [XML format](save-an-execution-plan-in-xml-format.md):

| Attribute | Description |
| --- | --- |
| `QueryStoreStatementHintText` | Actual Query Store hints applied to the query |
| `QueryStoreStatementHintId` | Unique identifier of a query hint |
| `QueryStoreStatementHintSource` | Source of Query Store hint (for example, `User`) |

> [!NOTE]  
> These XML elements are available via the output of the [!INCLUDE [tsql](../../includes/tsql-md.md)] commands [SET STATISTICS XML](../../t-sql/statements/set-statistics-xml-transact-sql.md) and [SET SHOWPLAN_XML](../../t-sql/statements/set-showplan-xml-transact-sql.md).

## Query Store hints and feature interoperability

- Query Store hints override other hard-coded statement level hints and plan guides.
- Except for the `ABORT_QUERY_EXECUTION` hint, queries with Query Store hints always execute. Opposing Query Store hints are ignored that would otherwise cause an error.
- If Query Store hints contradict, the [!INCLUDE [ssDE](../../includes/ssde-md.md)] doesn't block query execution, and Query Store hint isn't applied.
- Query Store hints aren't supported for statements that qualify for simple parameterization.
- The `RECOMPILE` hint isn't compatible with forced parameterization set at the database level. If a database has forced parameterization set, and the `RECOMPILE` hint is part of the Query Store hints for a query, the [!INCLUDE [ssDE](../../includes/ssde-md.md)] ignores the `RECOMPILE` hint and applies any other hints if they're specified.
  - The [!INCLUDE [ssDE](../../includes/ssde-md.md)] issues a warning (error code 12461) stating that the `RECOMPILE` hint was ignored.
  - For more information about forced parameterization use case considerations, see [Guidelines for Using Forced Parameterization](../query-processing-architecture-guide.md#forced-parameterization).
- Manually created Query Store hints are exempt from Query Store cleanup. The hint and the query aren't cleaned up by the automatic retention capture policy.
  - Queries can be [manually removed by users](../system-stored-procedures/sp-query-store-remove-query-transact-sql.md). That also removes the associated Query Store hint.
  - Query Store hints automatically generated by the [CE Feedback](intelligent-query-processing-details.md#cardinality-estimation-ce-feedback) are subject to clean up by the automatic retention of the capture policy.
  - [DOP feedback](intelligent-query-processing-degree-parallelism-feedback.md) and [memory grant feedback](intelligent-query-processing-details.md#memory-grant-feedback) shape query behavior without using Query Store hints. When queries are cleaned up by the automatic retention capture policy, DOP feedback and memory grant feedback data is also cleaned up.
  - If you create the same Query Store hint that CE feedback implemented manually, the query with the hint is no longer subject to clean up by the automatic retention capture policy.

### Query Store hints and secondary replicas

Query Store hints have no effect on secondary replicas unless Query Store for secondary replicas is enabled. For more information, see [Query Store for readable secondaries](query-store-for-secondary-replicas.md).

- In [!INCLUDE [sssql19-md](../../includes/sssql22-md.md)] and earlier versions, Query Store hints can be applied on the primary replica only.
- In [!INCLUDE [sssql22-md](../../includes/sssql22-md.md)] and later versions, when Query Store for secondary replicas is enabled, Query Store hints can be applied on secondary replicas in availability groups.
- You can add a Query Store hint to be in effect only on a specific replica group when you have Query Store for secondary replicas enabled. To do this, use the `@replica_group_id` parameter when calling [sys.sp_query_store_set_query_hints](../system-stored-procedures/sys-sp-query-store-set-hints-transact-sql.md). Conversely, you can remove a Query Store hint from a specific replica group using [sys.sp_query_store_clear_query_hints](../system-stored-procedures/sys-sp-query-store-clear-hints-transact-sql.md).
- Find the available replica groups by querying [sys.query_store_replicas](../system-catalog-views/sys-query-store-replicas.md).
- Find plans forced on secondary replicas with [sys.query_store_plan_forcing_locations](../system-catalog-views/sys-query-store-plan-forcing-locations-transact-sql.md).

## Examples

### A. Query Store hints demo

The following walk-through of Query Store hints in Azure SQL Database uses an imported database via a BACPAC file (`.bacpac`). Learn how to import a new database to an Azure SQL Database server, see [Quickstart: Import a bacpac file to a database in Azure SQL Database or Azure SQL Managed Instance](/azure/azure-sql/database/database-import).

:::code language="tsql" source="../../../sql-server-samples/samples/features/query-store/query_store_hints_demo.sql":::

### B. Identify a query in Query Store

The following example queries [sys.query_store_query_text](../system-catalog-views/sys-query-store-query-text-transact-sql.md) and [sys.query_store_query](../system-catalog-views/sys-query-store-query-transact-sql.md) to return the `query_id` for an executed query text fragment.

In this demo, the query we're attempting to tune is in the `SalesLT` sample database:

```sql
SELECT * FROM SalesLT.Address as A
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;
```

Query Store doesn't immediately reflect query data to its system views.

Identify the query in the Query Store system catalog views:

```sql
SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%PostalCode =%'
  AND query_sql_text not like N'%query_store%';
GO
```

In the following samples, the previous query example in the `SalesLT` database was identified as `query_id` 39.

Once identified, apply the hint to enforce a maximum memory grant size in percent of configured memory limit to the `query_id`:

```sql
EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';
```

You can also apply query hints with the following syntax, for example the option to force the [legacy cardinality estimator](cardinality-estimation-sql-server.md):

```sql
EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
```

You can apply multiple query hints with a comma-separated list:

```sql
EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';
```

Review the Query Store hint in place for `query_id` 39:

```sql
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
WHERE query_id = 39;
```

Finally, remove the hint from `query_id` 39, using [sp_query_store_clear_hints](../system-stored-procedures/sys-sp-query-store-clear-hints-transact-sql.md).

```sql
EXEC sys.sp_query_store_clear_hints @query_id = 39;
```

## Related content

- [sys.query_store_query_hints (Transact-SQL)](../system-catalog-views/sys-query-store-query-hints-transact-sql.md)
- [sys.sp_query_store_set_hints (Transact-SQL)](../system-stored-procedures/sys-sp-query-store-set-hints-transact-sql.md)
- [sys.sp_query_store_clear_hints (Transact-SQL)](../system-stored-procedures/sys-sp-query-store-clear-hints-transact-sql.md)
- [Save an Execution Plan in XML Format](save-an-execution-plan-in-xml-format.md)
- [Display and save execution plans](display-and-save-execution-plans.md)
- [Query hints (Transact-SQL)](../../t-sql/queries/hints-transact-sql-query.md)
- [Best practices for monitoring workloads with Query Store](best-practice-with-the-query-store.md)
- [Query Store hints best practices](query-store-hints-best-practices.md)
- [Monitor performance by using the Query Store](monitoring-performance-by-using-the-query-store.md)
- [Configure the max degree of parallelism (MAXDOP) in Azure SQL Database](/azure/azure-sql/database/configure-max-degree-of-parallelism)