Estimated Plans and Forced Plans from Query Store

While all plans are estimated plans, there is still a difference between capturing an estimated plan and looking at a plan from the cache or from query store. Or is there?

A question came up during a recent presentation; what happens to capturing an estimated plan when you’re forcing plans?

Let’s find out. The answer is interesting.

Estimated Plans

Here’s my stored procedure that I’ll be using with AdventureWorks2017:

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;

For reasons I’ll explain in a bit, I’m going to free the procedure cache:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Then, if I capture an estimated plan for two different values:

EXEC dbo.ProductTransactionHistoryByReference @ReferenceOrderID = 41798;

EXEC dbo.ProductTransactionHistoryByReference @ReferenceOrderID = 1255;

I end up with two different execution plans:

Click to embiggen

This is because the different values have different data distribution within my statistics and parameter sniffing leads to difference in the plans.

Plan Forcing

If I execute each example query, clearing the cache between each, I have both available in the Query Store. I choose to force a plan, let’s say the first one. In this case, I’ll use the GUI:

Now, I go right back over and capture the estimated plans again, I’ll still see the same thing as I saw above. In short, plan forcing doesn’t affect capturing estimated plans… unless….

Let’s execute one of the two scripts for the stored procedure. It doesn’t matter which one. Because of plan forcing, you’ll end up with the top plan being stored in cache. Now, let’s try capturing the estimated plan again. No matter which set of parameters you use, you should see this plan (or whichever one you forced):

But… why? Well, the trick is in whether or not the plan is in cache. If we look at the properties of the SELECT operator in the plan above, a few points immediately stand out:

First, you can tell that this is still an estimated plan because the Runtime parameter value is missing. However, at the bottom of the properties is the Use plan property which is only there in forced plans, showing this plan was forced. But why, when we’re not executing the query, are we seeing the forced plan? Well, the other property of interest here is the RetrievedFromCache property, which in this case, is true. What if we free the procedure cache again and then recapture the estimated plan:

This is the plan for the second value, not the forced plan, even though plan forcing is still in effect. I haven’t change that. All I’ve changed is where the estimated plan comes from. Let’s look at the properties of the SELECT operator here:

Notice two things, first, no Use plan at the bottom. Second, and more important, RetrievedFromCache is set to false.

What is happening is that the optimizer is smart. It knows that if a query is not in cache, it has to do the compile in order to show you an estimated plan. However, if the plan is in cache, it can just retrieve that plan from cache and show that to you instead of “wasting” time compiling a new one. That does mean, in this case though, because we forced a plan, we’re seeing that plan instead. Now, if we disabled plan forcing and put the other plan into cache by executing the procedure with the other parameter value, you’d see that second plan as well, just no evidence of plan forcing since it would be turned off.

Conclusion

What we’re seeing here is not an artifact of plan forcing. We’re seeing an artifact of how estimated plans are generated. If the plan is already in cache, it doesn’t generate a new one, but uses the one that is there. This is another example of how you can see differences between estimated and actual plans.

---

If you want to learn a lot more about execution plans, Query Store, plan forcing and more, I still have two opportunities this year for my all day seminar on query tuning tools:

I’ll be at my local event, SQLSaturday Boston, on September 21st. You can go here to register.

Another event in Europe will be at SQLSaturday Munich on October 26, 2018. Go here now to join the class.