On occasion I’ll run into a production performance problem where hints were added to a query and the hint is having a negative effect on performance.  In some cases this is the result of something buried in vendor code.  In other cases this is related to custom developed code where it is not easy to quickly deploy a fix that involves removing a hint.

Using plan guides

You can use a plan guide to add a hint without changing the query.  Similarly, you can use a plan guide to remove an existing hint from a query.  This might sound strange, but we’re using a hint to remove a hint.  Plan guides are typically difficult to get correct due to them needing to be exact.  There is a shortcut for creating them that I find to be fairly effective.  This is done by using sp_create_plan_guide_from_handle to create a “model” plan guide, that can subsequently be modified to apply the desired hint.  I won’t go too much further into it since I have a post detailing it here.

Example query

We will use the following query as an example.  Running this query with the hint causes a full scan on the clustered index.  While this hint may have been beneficial in other environments, it seems to be having a negative impact in the ours.

SELECT * from CustomerAccount ca WHERE CustomerID = @0 AND NOT EXISTS (SELECT 1 FROM SalesLT.Customer c WITH(INDEX(PK_Customer_CustomerID), FORCESCAN) WHERE c.CustomerID = ca.CustomerID)

The hint to remove the hint

We would like to remove the hint applied to the SalesLT.Customer table.  We will need to use a query hint that specifies an empty table hint in order to do this.  The hint for this would be:

@hints=N'OPTION(TABLE HINT(c))'

Pulling this all together results in the following plan guide:
EXEC sp_create_plan_guide @name = N'[remove table hint]',
@stmt = N'select * from CustomerAccount ca where CustomerID = @0 and not exists ( select 1 from SalesLT . Customer c with ( index ( PK_Customer_CustomerID ) , FORCESCAN ) where c . CustomerID = ca . CustomerID )',
@type = N'SQL', @module_or_batch = N'select * from CustomerAccount ca where CustomerID = @0 and not exists ( select 1 from SalesLT . Customer c with ( index ( PK_Customer_CustomerID ) , FORCESCAN ) where c . CustomerID = ca . CustomerID )',
@params = N'@0 int',
@hints = N'OPTION(TABLE HINT(c))'

Why not use Query Store to do this?

Query store can only apply hints in Azure SQL Database, Azure Managed Instances, and SQL Server 2022.  This is a fairly new feature, and as such it does not support all types of hints.  In particular it does not support applying table hints.