When QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140 does not follow CL 140 rules

Post0In this short post, I will demonstrate (for me) an unexpected behavior of one query hint.

In August 2018, Microsoft introduced a new query hint QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n to let you force another query optimizer behavior at a query level. The query will be processed as if was compiled with the compatibility level given in the hint, according to the rules associated with it. This is very handy whenever you see a regression in a latest compat level and you cannot identify particular feature responsible for it.

Consider the following code that will be executed in a database that runs under the latest compatibility level.

ALTER DATABASE AdventureWorksDW2019 SET COMPATIBILITY_LEVEL = 150;
GO
USE AdventureWorksDW2019;
GO
SELECT COUNT(*), MAX(UnitsIn) FROM dbo.FactProductInventory;
SELECT COUNT(*), MAX(UnitsIn) FROM dbo.FactProductInventory
OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'));

As you might see from the execution plans, the first execution is done with CL 150, while the second plan is generated with CL 140 rules:

Post1

However, when you use the same hint for a query that uses table variables, it does not force the old cardinality for table variables (1), it rather uses a new deferred compilation behavior, even if you specify the QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140 hint. Here is a sample code:

ALTER DATABASE AdventureWorks2019 SET COMPATIBILITY_LEVEL = 150;
GO
DECLARE @T AS TABLE (ProductID INT);
INSERT INTO @T SELECT ProductID FROM Production.Product WHERE ProductLine IS NOT NULL;


SELECT * FROM @T t
INNER JOIN Sales.SalesOrderDetail od on t.ProductID = od.ProductID
INNER JOIN Sales.SalesOrderHeader h on h.SalesOrderID = od.SalesOrderID
ORDER BY od.UnitPrice DESC
OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140'));

And here its execution plan:

Post2

The plan is created under CL 140, but the estimation number of rows for the table variable is not 1 but the actual one. Even if you would specify the FORCE_LEGACY_CARDINALITY_ESTIMATION hint, the query will be deferred compiled and behavior of table variable would be the same.

Table variable deferred compilation respects settings at the database scope and ignores all hints except one. If you want to see cardinality of 1 for table variables in your queries in a database under CL 150, you have to use the DISABLE_DEFERRED_COMPILATION_TV hint.

Thanks for reading.

Trackbacks

  1. […] Milos Radivojevic shows that table-valued parameters do not care about your QUERY_OPTIMIZER_COMPATIB…: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: