Compatibility Level And Forced Plans

Erin Stellato has an experiment with Query Store plan forcing:

A question came up recently about plan guides and compatibility mode, and it got me thinking about forced plans in Query Store and compatibility mode.  Imagine you upgraded to SQL Server 2016 and kept the compatibility mode for your database at 110 to use the legacy Cardinality Estimator.  At some point, you have a plan that you force for a specific query, and that works great.  As time goes on, you do testing with the new CE and eventually are ready to make the switch to compatibility mode 130.  When you do that, does the forced plan continue to use compatibility mode 110?  I had a guess at the answer but thought it was worth testing.

There are some interesting results here.

Related Posts

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 […]

Read More

Using Query Store To Diagnose Implicit Conversion Issues

Tom Norman shares a case study of using Query Store to fix a nasty implicit conversion problem: A while ago, we contracted with a third party to start using their software and database with our product.  We put the database in Azure but within a year, the database grew to over 250 gigs and we […]

Read More

Categories

February 2017
MTWTFSS
« Jan Mar »
 12345
6789101112
13141516171819
20212223242526
2728