Minor Differences Between Plan Cache And Query Store Plans

Grant Fritchey shows us some minor differences between what the Query Store shows for a particular execution plan versus what exists in the plan cache:

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.

Click through for the full set of differences as well as Grant’s explanation.

Related Posts

Hiding Work: The Nested Loop Operator

Erik Darling explains that the nested loop operator is like a duck: there’s more going on beneath the surface than it lets on: I’m going to talk about my favorite example, because it can cause a lot of confusion, and can hide a lot of the work it’s doing behind what appears to be a […]

Read More

Query Store On Azure SQL DW

Matt Usher announces Query Store is now available to all in Azure SQL Data Warehouse: Since our preview announcement, hundreds of customers have been enabling Query Store to provide insight on query performance. We’re excited to share the general availability of Query Store worldwide for Azure SQL Data Warehouse. Query Store automatically captures a history of queries, plans, and […]

Read More

Categories

November 2018
MTWTFSS
« Oct Dec »
 1234
567891011
12131415161718
19202122232425
2627282930