Infrastructure at your Service

Steven Naudet

SQL Server: table variables performance limitations

Doing some performance troubleshooting for one of my customers I identified some issues with very large table variables inside Stored procedures.
Table variables limitations are not well understood by developers although they are now well documented.

Table variable rows estimation

Let’s have a look at an example with my customer context which is SQL Server 2016, so compatibility level 130 at the database level. You can reproduce this demo with the Wide World Importers database.

create table #Orders (
	[OrderID] [int] NOT NULL,
	[CustomerID] [int] NOT NULL,
	[SalespersonPersonID] [int] NOT NULL,
	[PickedByPersonID] [int] NULL,
	[ContactPersonID] [int] NOT NULL,
	[BackorderOrderID] [int] NULL,
	[OrderDate] [date] NOT NULL,
	[ExpectedDeliveryDate] [date] NOT NULL,
	[CustomerPurchaseOrderNumber] [nvarchar](20) NULL,
	[IsUndersupplyBackordered] [bit] NOT NULL,
	[Comments] [nvarchar](max) NULL,
	[DeliveryInstructions] [nvarchar](max) NULL,
	[InternalComments] [nvarchar](max) NULL,
	[PickingCompletedWhen] [datetime2](7) NULL,
	[LastEditedBy] [int] NOT NULL,
	[LastEditedWhen] [datetime2](7) NOT NULL
);
insert into @Orders
	select top 50000 * from Sales.Orders;

select CustomerID, MAX(OrderDate) AS LastOrderDate
from @Orders
group by CustomerID;

This simple query touching all 50000 rows of the table variable get an estimated number of rows of 1.
This is always the case with table variables unless you use some tricks like the option Recompile. As we can see in the plan the query is spilling to TempDB so the performance impact can be significant with a large amount of data.

Estimates are also bad for queries with predicates in the WHERE clause or JOIN as you can see. The estimated number of rows is always 1.

select CustomerID, OrderID, OrderDate 
from @Orders
where CustomerID = 89;

Table variable with compatibility level 150 (SQL Server 2019)

Things were improved with SQL Server 2019 and the introduction of Table variable deferred compilation.
The query optimizer is now aware of the table variable cardinality and can better estimate some queries.

The original query now executed with a 150 compatibility got the correct estimated number of rows, no TempDB spill, and got its execution time (which is very fast already for this demo) divided by two.

Estimation for queries with predicates is still not perfect with SQL Server 2019 because we still don’t get statistics on columns. Based only on table cardinality the optimizer can estimate the number of rows for such query. It estimated 224 instead of 84 actual rows. Not perfect but it can sometimes create a way better plan than the forced “1” value of previous versions.

Temp tables estimation

Rewriting table variables to temporary tables can improve cardinality estimation regardless of the database compatibility level.

Here are the queries using a Temp table:

drop table if exists #Orders;
create table #Orders (
	[OrderID] [int] NOT NULL,
	[CustomerID] [int] NOT NULL,
	[SalespersonPersonID] [int] NOT NULL,
	[PickedByPersonID] [int] NULL,
	[ContactPersonID] [int] NOT NULL,
	[BackorderOrderID] [int] NULL,
	[OrderDate] [date] NOT NULL,
	[ExpectedDeliveryDate] [date] NOT NULL,
	[CustomerPurchaseOrderNumber] [nvarchar](20) NULL,
	[IsUndersupplyBackordered] [bit] NOT NULL,
	[Comments] [nvarchar](max) NULL,
	[DeliveryInstructions] [nvarchar](max) NULL,
	[InternalComments] [nvarchar](max) NULL,
	[PickingCompletedWhen] [datetime2](7) NULL,
	[LastEditedBy] [int] NOT NULL,
	[LastEditedWhen] [datetime2](7) NOT NULL
);

insert into #Orders
	select top 50000 * from Sales.Orders;

select CustomerID, MAX(OrderDate) AS LastOrderDate
from #Orders
group by CustomerID;

Conclusion

Table variables are fine for small data sets. Performance issues can happen with large numbers of rows and queries using JOINs or WHERE clauses.
If rewriting the code is a possibility, using a temporary table will improve the query plan.
Temporary table performances are more predictable with columns statistics.
The query optimizer has been improved for table variables so in some cases you might get faster execution times just by upgrading to SQL Server 2019.

Leave a Reply

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

Steven Naudet
Steven Naudet

Consultant