SQL server statistics – table variables

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

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.