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;