Query Store and Recompile

Azure, SQL Server 2016, TSQL
One of the many advantages of SQL Cruise is the ability to have enough time during a presentation to be able to answer questions from the people there in great detail. One question came up while I was showing the new functionality of Query Store (available soon in SQL Server 2016, available right now in Azure SQL Database). What happens to plan forcing when you have OPTION RECOMPILE on a query? Great question. I have a favorite procedure I use to illustrate the functionality of parameter sniffing: [crayon-5a6c7c4a14a2d327733132/] If this procedure is called with the value of 'Mentor' you get an execution plan that looks like this: If you remove that plan from cache and then call the procedure with the value of 'London' then the plan looks like this:…
Read More