Estimated Plans and Forced Plans from Query Store

SQL Server 2016, SQL Server 2017, T-SQL
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: [crayon-5c9c74377a6e8352497534/] For reasons I'll explain in a bit, I'm going to free the procedure cache: [crayon-5c9c74377a6f6356211888/] Then, if I capture an estimated plan for two different values: [crayon-5c9c74377a6fb366732995/] 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.…
Read More