Battle of the Query Hints in Query Store

I recently presented a session on the Query Store at Data Saturday Rhineland and the question came up: If there’s already a query hint on a query, what happens when you try to force a similar query hint?

Yeah, OK, that is a weird one. I don’t know the answer, but I’m about to find out.

Setting up the Battle

I’ve got this simple procedure I use a lot to illustrate bad parameter sniffing. In AdventureWorks, this query can produce up to five different plans, depending on the values called. Most of the time, it’s one of two plans, which I’ll get to in a minute. Here’s the query:

CREATE OR ALTER PROC dbo.ProductTransactionHistoryByReference
(@ReferenceOrderID INT)
AS
BEGIN
    SELECT p.Name,
           p.ProductNumber,
           th.ReferenceOrderID
    FROM Production.Product AS p
        JOIN Production.TransactionHistory AS th
            ON th.ProductID = p.ProductID
    WHERE th.ReferenceOrderID = @ReferenceOrderID;
END;
GO

If I execute this query with the value 41615:

EXEC dbo.ProductTransactionHistoryByReference @ReferenceOrderID = 41615;

I get this execution plan. I’m not going into details on the plan. Just note the join type, Loops Join:

Now, I’m going to remove the plan from cache and execute the query using a different parameter value:

--get the plan handle
DECLARE @plan_handle VARBINARY(64);
SELECT @plan_handle = deps.plan_handle
FROM sys.dm_exec_procedure_stats AS deps
WHERE deps.object_id = OBJECT_ID('dbo.ProductTransactionHistoryByReference');

--remove the plan from cache using the plan handle
DBCC FREEPROCCACHE(@plan_handle);

--execute the query
EXEC dbo.ProductTransactionHistoryByReference @ReferenceOrderID = 53463;

Which results in the following execution plan:

Again, we’re not exploring the plan in detail, just note the Merge Join is now in use. Same query, two plans, a classic example of parameter sniffing in action. Cool right?

No, boring. However, let’s create a new procedure for the test, and, we’re going to apply a query hint to ensure that we always end up with the Merge Join plan:

CREATE OR ALTER PROC dbo.QueryHintTest
(@ReferenceOrderID INT)
AS
BEGIN
    SELECT p.Name,
           p.ProductNumber,
           th.ReferenceOrderID
    FROM Production.Product AS p
        JOIN Production.TransactionHistory AS th
            ON th.ProductID = p.ProductID
    WHERE th.ReferenceOrderID = @ReferenceOrderID
	OPTION (OPTIMIZE FOR (@ReferenceOrderID = 53463));
END;
GO

Now, any execution of this query results in the plan with the Merge Join. You can see the action in the properties showing the compile value:

Can we change this behavior using query hint forcing in Query Store?

Forcing A Query Hint, the Battle Begins

To force a query hint, we first have to get the query_id value from Query Store:

SELECT qsq.query_id
FROM sys.query_store_query AS qsq
WHERE qsq.object_id = OBJECT_ID('dbo.QueryHintTest');

With that value in hand, I can try forcing a query hint:

EXEC dbo.sp_query_store_set_hints 1512, N'OPTION(OPTIMIZE FOR UNKNOWN)';

Yeah, it’s that easy to force a query hint. I didn’t get an error, so, in theory, it has forced that hint. What does the plan look like when I execute the query using the 41615 value?

Yeah, I know, you’re already glancing down, but before we get there, can we validate that, at least in theory, this query hint is being forced? Sure thing, just pull the data from Query Store:

SELECT qsqh.query_hint_id,
       qsqh.query_id,
       qsqh.query_hint_text,
       qsqh.source_desc
FROM sys.query_store_query_hints AS qsqh;

The results are here:

As you can see, my query thinks at least that it has a new hint applied through Query Store. Let’s discuss in detail what’s going to happen next….

GET ON WITH IT!!

Ok. Here’s the plan after executing with the 41615 value, sheesh:

That is not a Merge Join. HA!! Query Store wins the battle. This plan was generated based on the second query hint, OPTIMIZE FOR UNKNOWN, not the original hint, OPTIMIZE FOR (@ReferenceOrderID = 53463). While the plan shape is very similar to the original plan, if you note the estimated cost values, they’re different. This is a third, different, plan, resulting from the forced hint.

Now… what do we see in the plan properties?

The things to note here are the lack of a compile time value. Why? Because it’s optimized for an unknown value. It’s going to use the averages from the statistics, not look at a particular value. Second, we do have the added properties showing the information about how I chose to force a query hint.

This is so cool. However, unlike when I forced a plan over the top of a plan guide, there’s not evidence of the prior hint. It’s been cleaned out because of this override in behavior.

Conclusion

Neat.

Will we always see the Query Store forced hint instead of whatever hints we may have written to the query? Great question. I don’t know for certain. However, David Pless (of Disney Karaoke fame, he knows what I mean) of Microsoft says, yeah, the Query Store hints override those within the query.

That brings up one huge point worth mentioning. If you have two (2) or more hints on a query, then you override one of them with Query Store, what happens to the second one? It goes away. So, if you need both hints, better make sure you put into the Query Store forced hint as well.

This also means, wildly disparate hints could be used, radically changing behavior, without changing code at all. Useful or dangerous? I’d say both.

I really enjoy putting together posts like this, so please, keep those questions coming.