Based on my post at bit.ly/TempTables:
When a temporary table in a stored procedure is cached, any statistics created by AutoStats are also cached. This leads to odd effects when a query in the procedure recompiles with statistics cached from a previous execution. In general, the contents of a temporary table change dramatically between executions, so this behaviour is unexpected.
A workaround is to add an explicit UPDATE STATISTICS command to the stored procedure to rebuild the cached statistics. This action successfully replaces the cached statistics, but cached plans within the procedure that reference the temporary table, and found the cached statistics 'interesting', do not recompile as is normally the case.
The demo below shows the two behaviours. With UPDATE STATISTICS commented out, the SELECT plan for the first execution (E) is reused by the second execution (T) resulting in poor performance (and statistics are retained from the E run).
With UPDATE STATISTICS, the statistics are updated to show only (T) values for the second run, but the cached plan (optimized for 'E') does not recompile (reproduces on SQL Server 2005-2012 inclusive):
CREATE PROCEDURE dbo.Demo
SET NOCOUNT ON;
CREATE TABLE #Temp
ProductID integer NOT NULL,
Name nvarchar(50) COLLATE DATABASE_DEFAULT NOT NULL
INSERT INTO #Temp
FROM Production.Product AS p
p.Name LIKE @StartsWith + N'%';
--UPDATE STATISTICS #Temp;
OrderCount = COUNT_BIG(DISTINCT th.ReferenceOrderID)
FROM #Temp AS t
JOIN Production.TransactionHistory AS th ON
th.ProductID = t.ProductID
DBCC SHOW_STATISTICS (N'tempdb..#Temp', Name) WITH STAT_HEADER, HISTOGRAM;
DROP TABLE #Temp;
EXECUTE dbo.Demo @StartsWith = N'E';
EXECUTE dbo.Demo @StartsWith = N'T';
A workaround is to use UPDATE STATISTICS *and* OPTION (RECOMPILE) on the SELECT statement, but this should not be necessary. There does not seem to be much value in caching statistics on the temporary table between executions at all. In addition, new statistics ought to cause the SELECT to recompile without the query hint.