sys.query_store_query_hints (Transact-SQL)
Applies to: 
SQL Server 2022 (16.x)
Azure SQL Database
Azure SQL Managed Instance
Returns query hints from Query Store hints.
| Column name | Data type | Description |
|---|---|---|
| query_hint_id | bigint | Unique identifier of a query hint. |
| query_id | bigint | Unique identifier of a query in the Query Store. Foreign key to sys.query_store_query.query_id.) |
| query_hint_text | nvarchar(MAX) | Hint definition in form of N'OPTION (…) |
| last_query_hint_failure_reason | int | Error code returned when if applying hints failed. Will include the message_id of the error message. |
| last_query_hint_failure_reason_desc | nvarchar(128) | Will include the error description of the error message. |
| query_hint_failure_count | bigint | Number of times that the query hint application has failed since the query hint was created or last modified. |
| source | int | Source of Query Store hint: user source is zero and system-generated is non-zero. |
| source_desc | nvarchar(128) | Description of source of Query Store hint. |
| comment | nvarchar(max) | Internal use only. |
| query_hint_scope | tinyint | Determines the scope at which the hint will be applied, as per the replica_group_id column in sys.query_store_replicas. |
Remarks
Query Store hints are created by sys.sp_query_store_set_hints (Transact-SQL) and removed by sys.sp_query_store_clear_hints.
Permissions
Requires the VIEW DATABASE STATE permission.
Examples
View Query Store hints
The following example returns existing Query Store hints for query_id 39:
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;