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

Finding Memory-Rich Queries

Matthew McGiffen wants to find the queries which demand the largest memory grants: I had a server that looked like it had been suffering from memory contention. I wanted to see what queries were being run that had high memory requirements. The problem was that it wasn’t happening right now – I needed to be […]

Read More

Query Store Required Permissions

Andreas Wolter takes us through exactly which permissions are required for Query Store to work: Typically, there are 3 aspects to the work with the Query Store, which can be reflected in roles: 1) Configuration – turning Query Store on and off, clearing the contents, flushing its contents to disk and changing its settings.2) Viewing the […]

Read More

Categories

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