Stale statistics on a newly created temporary table in a stored procedure can lead to poor performance

When you create a temporary table you expect a new table with no past history (statistics based on past existence), this is not true if you have less than 6 updates to the temporary table. This might lead to poor performance of queries which are sensitive to the content of temporary tables.

I was optimizing SQL Server Performance at one of my customers who provides search functionality on their website. They use stored procedure with temporary table for the search. The performance of the search depended on who searched what in the past, option (recompile) by itself had no effect. Sometimes a simple search led to timeout because of non-optimal plan usage due to this behavior. This is not a plan caching issue rather temporary table statistics caching issue, which was part of the temporary object caching feature that was introduced in SQL Server 2005 and is also present in SQL Server 2008 and SQL Server 2012. In this customer case we implemented a workaround to avoid this issue (see below for example for workarounds).

When temporary tables are cached, the statistics are not newly created rather cached from the past and updated based on automatic update statistics threshold. Caching temporary tables/objects is good for performance, but caching stale statistics from the past is not optimal.

We can work around this issue by disabling temporary table caching by explicitly executing a DDL statement on the temporary table. One possibility is to execute an alter table statement, but this can lead to duplicate constraint name error on concurrent stored procedure execution. The other way to work around this is to create an index.

I think there might be many customers in such a situation without knowing that stale statistics are being cached along with temporary table leading to poor performance.

Ideal solution is to have more aggressive statistics update when the temporary table has less number of rows when temporary table caching is used. I will open a connect item to report this issue.

Meanwhile you can mitigate the issue by creating an index on the temporary table. You can monitor active temporary tables using Windows Server Performance Monitor counter: SQL Server: General Statistics->Active Temp Tables.

The script to understand the issue and the workaround is listed below:

set nocount on

set statistics time off

set statistics io off

drop table tab7

go

create table tab7 (c1 int primary key clustered, c2 int, c3 char(200))

go

create index test on tab7(c2, c1, c3)

go

begin tran

declare @i int

set @i = 1

while @i <= 50000

begin

insert into tab7 values (@i, 1, ‘a’)

set @i = @i + 1

end

commit tran

go

insert into tab7 values (50001, 1, ‘a’)

go

checkpoint

go

drop proc test_slow

go

create proc test_slow @i int

as

begin

declare @j int

create table #temp1 (c1 int primary key)

insert into #temp1 (c1) select @i

select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)

end

go

dbcc dropcleanbuffers

set statistics time on

set statistics io on

go

–high reads as expected for parameter ’1′

exec test_slow 1

go

dbcc dropcleanbuffers

go

–high reads that are not expected for parameter ’2′

exec test_slow 2

go

drop proc test_with_recompile

go

create proc test_with_recompile @i int

as

begin

declare @j int

create table #temp1 (c1 int primary key)

insert into #temp1 (c1) select @i

select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)

option (recompile)

end

go

dbcc dropcleanbuffers

set statistics time on

set statistics io on

go

–high reads as expected for parameter ’1′

exec test_with_recompile 1

go

dbcc dropcleanbuffers

go

–high reads that are not expected for parameter ’2′

–low reads on 3rd execution as expected for parameter ’2′

exec test_with_recompile 2

go

drop proc test_with_alter_table_recompile

go

create proc test_with_alter_table_recompile @i int

as

begin

declare @j int

create table #temp1 (c1 int primary key)

–to avoid caching of temporary tables one can create a constraint

–but this might lead to duplicate constraint name error on concurrent usage

alter table #temp1 add constraint test123 unique(c1)

insert into #temp1 (c1) select @i

select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)

option (recompile)

end

go

dbcc dropcleanbuffers

set statistics time on

set statistics io on

go

–high reads as expected for parameter ’1′

exec test_with_alter_table_recompile 1

go

dbcc dropcleanbuffers

go

–low reads as expected for parameter ’2′

exec test_with_alter_table_recompile 2

go

drop proc test_with_index_recompile

go

create proc test_with_index_recompile @i int

as

begin

declare @j int

create table #temp1 (c1 int primary key)

–to avoid caching of temporary tables one can create an index

create index test on #temp1(c1)

insert into #temp1 (c1) select @i

select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)

option (recompile)

end

go

set statistics time on

set statistics io on

dbcc dropcleanbuffers

go

–high reads as expected for parameter ’1′

exec test_with_index_recompile 1

go

dbcc dropcleanbuffers

go

–low reads as expected for parameter ’2′

exec test_with_index_recompile 2

go

Published 15 August 2012 16:26 by sqlworkshops

Comments

# re: Stale statistics on a newly created temporary table in a stored procedure can lead to poor performance

Coincidentally, I blogged about the same issue in a more detail the day before this post was published:

http://bit.ly/TempTables (sqlblog.com/.../temporary-tables-in-stored-procedures.aspx)

Paul White

SQL Server MVP

15 August 2012 18:08 by PaulWhiteNZ

# re: Stale statistics on a newly created temporary table in a stored procedure can lead to poor performance

Hello Paul,

Reading your article, I think update statistics is a nice solution. In a Microsoft subcontracting engagement I solved this issue with an index over a temporary table with option recompile hint.

Ideal solution would be to have more aggressive statistics update when the temporary table has less number of rows when temporary table caching is used.

15 August 2012 18:56 by sqlworkshops

# re: Stale statistics on a newly created temporary table in a stored procedure can lead to poor performance

UPDATE STATISTICS plus OPTION (RECOMPILE) is a workaround, it is true, but I feel the RECOMPILE should be unnecessary since new statistics ought to trigger that automatically.

The problem with using DDL like CREATE INDEX is that it prevents temporary table caching, which is in general a desirable thing.

Overall, I am not convinced that caching statistics with the temporary table is a desirable feature, since the contents of the table will in general be different on each execution.

I intend to file a Connect item to see if Microsoft regard this as By Design or not, and to see why UPDATE STATISTICS does not cause an automatic statement-level recompilation as I would expect.

Paul

15 August 2012 19:21 by PaulWhiteNZ

# re: Stale statistics on a newly created temporary table in a stored procedure can lead to poor performance

Hello Paul,

Interestingly, in this case, like CREATE INDEX, UPDATE STATISTICS is also treated like a DDL and the table is not cached. If you check the Windows Server Performance Monitor counter: SQL Server: General Statistics->Active Temp Tables that I mentioned in the article, you will notice the counter drops once a UPDATE STATISTICS or a CREATE INDEX is executed. I agree the RECOMPILE hint is unnecessary when statistics are updated.

Temporary table caching is a desirable thing, if only they can implement it correctly.

16 August 2012 12:09 by sqlworkshops

# re: Stale statistics on a newly created temporary table in a stored procedure can lead to poor performance

Hi,

I disagree.  The temporary table is cached with UPDATE STATISTICS.

Paul White

SQL Server MVP

16 August 2012 17:34 by PaulWhiteNZ

# re: Stale statistics on a newly created temporary table in a stored procedure can lead to poor performance

Hello Paul

Yes, you are right in this point.

I differ with your opinion on not caching statistics with the temporary table. By caching one can just update it that is what I mean by aggressively updating the statistics above. If not cached, statistic blobs have to be dropped and re-created. Working with the SQL Server development team in the past I think this is efficient. If not, like temporary table, without caching, will involve physical de-allocations and re-allocations of statistic blobs leading to allocation contention.

17 August 2012 12:04 by sqlworkshops