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:…