Table Variable Deferred Compilation in SQL Server 2019 CTP2- Undocumented Options

At the time of writing and publishing the series of articles about the Table Variable Deferred Compilation feature in SQL Server 2019 CTP2, I did not know about undocumented options and hints related to it, but in the meantime, thanks to Dmitry Pilugin (blog | twitter), I learned how you can play with table variables behavior under CL 150.

You can use the undocumented hint DISABLE_DEFERRED_COMPILATION_TV to disable the feature at the statement level. Let’s execute the query from the previous article The Good, the Bad and the Ugly of Table Variable Deferred Compilation – Part 3, but with the mentioned hint:


DECLARE @t TABLE(id INT PRIMARY KEY, pid INT, c1 CHAR(100));
INSERT INTO @t
SELECT * FROM A
WHERE A.pid = 413032;
SELECT * FROM @t A
INNER JOIN B ON A.id = B.pid
INNER JOIN B C ON C.pid = B.pid
ORDER BY B.C1 DESC OPTION (USE HINT ('DISABLE_DEFERRED_COMPILATION_TV'));

The execution plan is almost the same as the one under the CL 140! You can see that the Estimated Number of Rows for the table variable has value 1 instead of 288, as it had  under all previous compatibility levels.

TVDPart4_0

However, it’s not exactly the same plan; you can see this, when you compare the plan under CL 140 and the plan with CL 150 and the DISABLE_DEFERRED_COMPILATION_TV hint:

TVDPart4_1.png
All estimations and plan operators are the same, the only difference is the value for Memory Grant. This is probably related to the mechanism of memory grants in SQL Server 2019 CTP2, but the main thing here is that the hint works and that you can enforce the old behavior of table variables within CL 150.

By using the hint, you can disable the feature at the statement level, but it is also possible opposite – to disable the feature at the database level and enable it for individual statements only.

To disable the feature, you can use this undocumented(!) option:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

With this option, your code would produce the same execution plan as under CL 140 (with a bit more memory grants, as mentioned previously) without the changes! I think, this is important and hope that this option will be available in the SQL Server 2019 RTM and documented, of course.

To use the feature in a database which disable it as default, you can use undocumented hint FORCE_DEFERRED_COMPILATION_TV.

ALTER DATABASE DB2019 SET COMPATIBILITY_LEVEL = 150;
GO
ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;
GO
DECLARE @t TABLE(id INT PRIMARY KEY);
INSERT INTO @t
SELECT DISTINCT message_id FROM sys.messages;
SELECT * FROM @t OPTION (USE HINT('FORCE_DEFERRED_COMPILATION_TV'));
GO

Here is the execution plan for the query:

TVDPart4_2

As you can see, this hint is valid (the query does not return an error), but unfortunately it does not work; it does not affect the cardinality of the table variable.

Again, things discussed here are undocumented, and are part of a community technology preview version, so we can expect many changes until the SQL Server 2019 RTM version.

I would like to thank Dmitryi Pilugin for point me to these undocumented options and thank you for reading.

Related Articles

About author View all posts

Milos R.

3 CommentsLeave a comment

  • […] EDIT: At the time of writing and publishing this article, I did not know about undocumented options and hints related to Table Variable Deferred Compilation, but in the meantime, thanks to Dmitry Pilugin, I learned how you can play with table variables behavior under CL 150. You can find details about appropriate undocumented options and hints in this article. […]

  • Hi Milos,
    Just have found that the following works (CTP 2.0, CTP 2.1):

    alter database Adventureworks2016CTP3 set compatibility_level = 140;
    go
    declare @t as table (ProductID int);
    insert @t(ProductID) select ProductID from Production.Product where Color is null;
    select
    sum(sod.UnitPrice)
    from
    @t t
    join Sales.SalesOrderDetail sod on t.ProductID = sod.ProductID
    join Sales.SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID
    group by
    soh.TerritoryID
    option (use hint(‘FORCE_DEFERRED_COMPILATION_TV’));
    go

    It seems, that this hint works if CL is lower then 150, forcing deferred compilation behavior.

    • Hi Dmitry,
      Thanks for the comment. Yes, the hint lets us enforce the deferred compilation for table variables under < 150 CL, but only when the DEFERRED_COMPILATION_TV database scope setting is ON. When you set it to OFF, the FORCE_DEFERRED_COMPILATION_TV hint does not enforce the deferred comp, even for CL 150.

Leave a Reply

Your email address will not be published. Required fields are marked *