Forced Plans and Compatibility Mode

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.

Setup

I restored a copy of WideWorldImporters to my SQL 2016 SP1 instance and set the compatibility mode to 110:

USE [master];
GO

RESTORE DATABASE [WideWorldImporters]
FROM  DISK = N'C:\Backups\WideWorldImporters-Full.bak'
WITH  FILE = 1,
MOVE N'WWI_Primary' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_InMemory_Data_1',
NOUNLOAD,
REPLACE,
STATS = 5;
GO

ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 110
GO

Then I enabled Query Store and cleared out any old data that might exist (remember that WideWorldImporters is a sample database so who knows what might exist in the Query Store views):

USE [master];
GO

ALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON
GO

ALTER DATABASE [WideWorldImporters] SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
INTERVAL_LENGTH_MINUTES = 60,
MAX_STORAGE_SIZE_MB = 512,
QUERY_CAPTURE_MODE = ALL,
SIZE_BASED_CLEANUP_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200);
GO

ALTER DATABASE [WideWorldImporters] SET QUERY_STORE CLEAR;
GO

Next I’ll create a stored procedure to use for testing, and then I’ll run it twice with the RECOMPILE option, as this will generate two different plans.

USE [WideWorldImporters];
GO

DROP PROCEDURE IF EXISTS [Sales].[usp_GetFullProductInfo];
GO

CREATE PROCEDURE [Sales].[usp_GetFullProductInfo]
@StockItemID INT
AS
SELECT
[o].[CustomerID],
[o].[OrderDate],
[ol].[StockItemID],
[ol].[Quantity],
[ol].[UnitPrice]
FROM [Sales].[Orders] [o]
JOIN [Sales].[OrderLines] [ol] on [o].[OrderID] = [ol].[OrderID]
WHERE [StockItemID] = @StockItemID
ORDER BY [o].[OrderDate] DESC;
GO

EXEC [Sales].[usp_GetFullProductInfo] 220  WITH RECOMPILE;
GO

EXEC [Sales].[usp_GetFullProductInfo] 105  WITH RECOMPILE;
GO

Forcing a plan

We’ll start by looking at the two different plans in Query Store.  You can do this through the UI, or by using TSQL.  I’ll use both, just for fun, and we’ll start with TSQL.

SELECT
[q].[query_id],
[q].[object_id],
[o].[name],
[p].[compatibility_level],
[qt].[query_sql_text],
[p].[plan_id],
TRY_CONVERT(XML,[p].[query_plan]) AS [QueryPlan]
FROM [sys].[query_store_query] [q]
JOIN [sys].[query_store_query_text] [qt]
ON [q].[query_text_id] = [qt].[query_text_id]
JOIN [sys].[query_store_plan] [p]
ON [q].[query_id] = [p].[query_id]
JOIN [sys].[objects] [o]
ON [q].[object_id] = [o].[object_id]
WHERE [q].[object_id] = OBJECT_ID(N'Sales.usp_GetFullProductInfo');
GO

 

 

You can see in the output that there are two different plans (plan_id 3 and plan_id 4)for this stored procedure query.  I can click on the XML link to see each plan, and then compare them, or I can do this from within Query Store.  It’s easier within Query Store, I just need to know the query_id (3).  Within Management Studio, expand the WideWorldImporters database, expand Query Store, then double-click on Tracked Queries and enter the query_id in the Tracking Query box.

 

 

You’ll see that there are two plans, and to compare them you click on both plans in the plan id window (hold down the CTRL key to get them both) and then select Compare Plans.

 

 

In looking at the plans, you see that the shapes are similar, but Plan 3 has a Nested Loop, while Plan 4 Merge Join that’s fed by a Sort.  For this example, we’ll decide that the Nested Loop plan is “better” for this query, so that’s the one we will force.

However, before we make that change, let’s see if we get a different plan with compatibility mode 130.

USE [master];
GO

ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 130;
GO

USE [WideWorldImporters];
GO

EXEC [Sales].[usp_GetFullProductInfo] 105  WITH RECOMPILE;
GO

Check Query Store again…

SELECT
[q].[query_id],
[q].[object_id],
[o].[name],
[p].[compatibility_level],
[qt].[query_sql_text],
[p].[plan_id],
TRY_CONVERT(XML,[p].[query_plan]) AS [QueryPlan]
FROM [sys].[query_store_query] [q]
JOIN [sys].[query_store_query_text] [qt]
ON [q].[query_text_id] = [qt].[query_text_id]
JOIN [sys].[query_store_plan] [p]
ON [q].[query_id] = [p].[query_id]
JOIN [sys].[objects] [o]
ON [q].[object_id] = [o].[object_id]
WHERE [q].[object_id] = OBJECT_ID(N'Sales.usp_GetFullProductInfo');
GO

We DO have a different plan!  If we look at the plan, we see that the shape is still similar, but now we have a Hash Match with a Filter operator and a Clustered Index Scan.

 

Now we want to force that Nested Loop plan.  First, change the compatibility mode back to 110:

USE [master];
GO

ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 110;
GO

Next, force the plan that has the Nested Loop, and we can do this in the UI, or with TSQL.  In the UI just go back to the Tracked Queries window, select the plan, and then Force Plan.  To force the plan with TSQL, you need to know the query_id and plan_id:

USE [WideWorldImporters];
GO

EXEC sp_query_store_force_plan @query_id = 3, @plan_id = 3;
GO

Now the plan is forced.  If we enable the actual execution plan and re-run our stored procedure without the RECOMPILE on it (because why would you use RECOMPILE on a query with a forced plan?) we see that the Nested Loop plan is used:

EXEC [Sales].[usp_GetFullProductInfo] 105;
GO

And here’s the big test…  Change compatibility mode to 130 again, free procedure cache just for fun (this does not matter – when a plan is forced, it doesn’t matter if the plan exists in cache or not), and then run the stored procedure and check the plan:

USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 130;
GO

USE [WideWorldImporters];
GO

EXEC [Sales].[usp_GetFullProductInfo] 105  WITH RECOMPILE;
GO

Surprised?  The Nested Loop plan is still used.  This is expected!  It does not matter if the compatibility mode for the database is different than the compatibility mode for the plan.  The forced plan is what’s used.

Summary

In this example, even when the compatibility mode for the database changed, the forced plan was still used.  Thus, forced plans are not tied to compatibility mode.  This is a good thing.  If you’ve upgraded to SQL Server 2016 and you are working to fix query performance issues related to the new cardinality estimator, forcing plans can be incredibly helpful in stabilizing performance without changing code to include trace flags or hints.  However, do not assume that a forced plan will always be used.  If you look at the Best Practice with the Query Store guidelines, there’s a section titled “Check the Status of Forced Plans Regularly.”  Within that section is this note:

However, as with plan hints and plan guides, forcing a plan is not a guarantee that it will be used in future executions.

Therefore, while you force a plan because you want it to be used – to make query performance more stable – SQL Server does not guarantee it will always be used.  There are cases when it cannot, and should not, be used, hence the recommendation to check the status of forced plans in sys.query_store_plan.