Query Store and Plan Cache Plans Compared

Query Store plans and the plans in cache are identical, right? There won’t be differences because the plan that is in cache is the plan that was used to execute the query. Similarly, the plan that is in the Query Store is the plan that was used to execute the query as well. Therefore, they will be the same. However, some small differences actually can show up.

Differences Between Plans

In order to compare the two plans, first, we need a query. Here’s a stored procedure that I’m going to use to generate a plan that will be in cache and in the query store:

CREATE   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

EXEC dbo.ProductTransactionHistoryByReference
    @referenceorderid = 53465;

Nothing to it really. What I’m going to do is execute the query. That will load it into the cache and into query store. Then, I’m going to flush the cache and re-execute the query. Now, we’ll use this to pull the two plans out of their respective storage locations:

SELECT CAST(qsp.query_plan AS XML)
FROM sys.query_store_query AS qsq
    JOIN sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
WHERE qsq.object_id = OBJECT_ID('dbo.ProductTransactionHistoryByReference')
ORDER BY qsp.last_execution_time DESC;

SELECT deqp.query_plan
FROM sys.dm_exec_procedure_stats AS deps
    CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
WHERE deps.object_id = OBJECT_ID('dbo.ProductTransactionHistoryByReference');

With the two plans available, I’m going to use the Showplan Comparison utility in SQL Server Management Studio (17 or greater). Here are the two resulting plans:

You can click on that to make it bigger if needed. However, you can see from the highlighted regions that these plans are identical… except that first operator is not highlighted. Let’s look at the properties of that operator:

As you can see, while the structure of the plans are identical, not everything is. The Compile values are different (although sometimes, they’ll be the same, that one is kind of luck of the draw to a degree) because they were compiled at different times with varying load on the system, so certainly that will be reflected. However, the other differences are also interesting. Which of the plans was retrieved from cache for example and, more importantly, the statement for the plans. The one on the left is the plan from the Query Store. It was not retrieved from cache and, the statement is for the query, not the stored procedure. Meanwhile, the plan on the right is from cache and, it’s based on the plan handle from the stored procedure, so it reflects that in the Statement value.

There are other differences in the plan as well. If you look at the Filter operator, the predicate values have some different probe definitions. These are simply variations caused by the optimization process. They don’t register as full blown differences in the plan comparison, hence the pink outline covers all the operators. However, they are still different values.

Conclusion

What’s all this mean? Is the implication that you can’t trust one or both of these plans to accurately reflect reality? No. Not at all. Instead, the key takeaway is that you may see some small variations between a plan pulled out of cache and a plan pulled out of the query store. The answer as to why is really quite simple. Each plan represents a precise moment in time. Given tiny differences, you may see a little more or less CPU time and other variable factors change.

This should not be any cause for concern at all. Instead, if you do see differences, you simply need to determine if these are the types of variation caused by a simple difference such as a little extra CPU time. If they are differences such as row estimates, you may be seeing a changing slice of time, or you may be seeing changing plans. You’ll need to drill down to determine which is which.