SQL server statistics – table variables

Statistics are very important for the SQL query optimizer and is used for estimating number of records returned by a query. If the difference between estimated number of records and actual number of records are too big, the optimizer may choose a bad plan, with heavy performance loss as a result.

A common problem with statistics are caused by table variables, because SQL server doesn’t have statistics for table variables. As SQL server is using a cost based optimization and statistics are a crucial factor for the cost estimation, there has to be an estimated number of records.

How is that solved? Well, SQL server is using 1 as estimated number of records, even if there are millions of records! Run the following query with “include actual qeuery plan” on and take a look at the estimaded vs actual number of records by hovering the mouse over the scan operator. You can also take a look at the io statistics in the message window. There is a big difference between the logical reads.

DECLARE @orderDetails TABLE(
SalesOrderID INT,
[SalesOrderDetailID] int,
[OrderQty] SMALLINT,
CarrierTrackingNumber NVARCHAR(25)
)

INSERT INTO @orderDetails( SalesOrderID, [SalesOrderDetailID], [OrderQty], [CarrierTrackingNumber] )
SELECT sod.[SalesOrderID], sod.[SalesOrderDetailID], [sod].[OrderQty], sod.[CarrierTrackingNumber]
FROM [Sales].[SalesOrderDetail] AS sod
WHERE sod.[OrderQty]>1

SELECT [od].[SalesOrderID], [od].[SalesOrderDetailID], [od].[OrderQty], [od].[CarrierTrackingNumber]
FROM @orderDetails AS od WHERE [OrderQty]>1

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM [Sales].[SalesOrderHeader] AS soh
INNER JOin @orderDetails od ON [soh].[SalesOrderID]=[od].[SalesOrderID]
WHERE od [OrderQty]>1

SELECT * FROM [Sales].[SalesOrderHeader] AS soh
INNER JOIN [Sales].[SalesOrderDetail] AS od ON [soh].[SalesOrderID]=[od].[SalesOrderID]
WHERE od.[OrderQty]>1

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

The solution: 

Avoid using table variables for temp tables if they are likely to contain more than a few rows. As a rule of thumb, use temporary tables, rather than table variables, for temp tables with more than 100 rows.