Adding hints to queries with query store

Up until recent, if you wanted to add a hint to a query without making code changes you had to use plan guides.  Plan guides can be tedious to create.  With SQL Server 2022, Azure SQL Database, or Azure Managed Instances you can now add hints to queries via Query Store.

Using sp_query_store_set_hints

The simplest form of adding hints is by calling sys.sp_query_store_set_hints while passing a @query_id and @hints parameter.  The following example applies a MAXDOP 1 hint to query id 543.
exec sys.sp_query_store_set_hints @query_id=543, @hints=N'OPTION(MAXDOP 1)'

Limitations (not all query hints are supported)

Applying hints via sp_query_store_set_hints applies to a limited set of hints at the moment.  One most notable that is not supported is table hints (kind of a bummer).  The following is a list of query hints not supported:

  • OPTIMIZE FOR ( @var = val)

Below is an example of where I attempted to add a table hint:

Removing the hint from a query

You can run the following to remove a hint applied from query store:

exec sys.sp_query_store_clear_hints @query_id

If you have a query that had hints hardcoded into it at development time, this will not remove those hints.  This command will only remove hints that were added via query store.