SQL Server 2019 Intelligent Query Processing: Table variable deferred compilation

In our last post, we have seen one of the feature of Intelligent Query processing(Memory Grant Feedback feature). In this post, lets focus on the Table variable deferred compilation.

With SQL Server 2017 and below versions, SQL Server always estimates one row for table variable as the table variable data gets inserted during the run time, so optimizer doesn’t know how many values it can expect coming out of the table variable. Due to this bad estimation, performance of the queries is effected.

Let’s see this behavior with an example:

Place the database in 140 compatibility mode (SQL Server 2017) and run below query using a table variable enabling the actual execution plan

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE @IDinfo TABLE
([ID] INT, 
 [Person_id] [int]
);
INSERT INTO @IDinfo
       SELECT [ID], 
            [Person_id]  
       FROM test;
SELECT top 100 *
FROM @IDinfo F1
     JOIN test F2 ON F1.[ID] = F2.[ID];

Check the execution plan and see the estimated number of rows shows as 1 but actual number of rows were 100.

In versions later than SQL Server 2012 SP2, we have a trace flag 2453 which we can turn on so the table variable gets recompiled when the number of rows changes. Let’s run the same query by turning on the trace flag. This can be done at the query level or you can turn on the trace flag at the server level by using the command DBCC TRACEON(2453,-1).


DBCC TRACEON(2453);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE @IDinfo TABLE
([ID] INT, 
 [Person_id] [int]
);
INSERT INTO @IDinfo
       SELECT [ID], 
            [Person_id]  
       FROM test;
SELECT top 100 *
FROM @IDinfo F1
     JOIN test F2 ON F1.[ID] = F2.[ID];

Check the execution plan and see the estimated number of rows shows as 100 and actual number of rows were 100 which is 100% accurate estimate.

We can also recompile the query by using the OPTION(RECOMPILE) in the statement which will recompile the statement at every execution.

SQL Server 2019 Feature: Table variable deferred compilation

As we have seen in the previous examples, SQL Server estimates one row for table variables SQL Server 2017 and earlier versions of SQL Server. In SQL Server 2019, a new feature called Table variable deferred compilation compilation of the table variable statement is deferred until the first execution. This will help the optimizer to estimate the accurate number of rows which will help improve the query performance. This feature is available in SQL Server 2019 databases with compatibility level 150.

Change the database compatibility mode to 150 and run the same query one more time and check the execution plan

SQL Server will be able to estimate the number of rows perfectly.

Summary: With this new feature Table variable deferred compilation in SQL Server 2019, we don’t have a need to use trace flag 2453 or use OPTION(RECOMPILE) to specifically recompile the statements using the table variable. If you have queries with table variables that regularly gets populated with relatively same number of rows, this feature will get the better estimates with out we changing our code. However, if the number of rows that populate the table variable changes greatly and if the queries doesn’t cause the high compilation times, then considering this new feature along with OPTION(RECOMPILE) is helpful.

Thanks for reading!

3 thoughts on “SQL Server 2019 Intelligent Query Processing: Table variable deferred compilation

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