Scalability Improvement with Temporary Table Caching in SQL Server 2005
By : Kasim Wirama, MCDBA
SQL Server 2005 has made interesting improvement on tempdb database. Probably, it will apply as well to SQL Server 2008. Pressure on tempdb database that is caused by read/write contention will be greatly reduced. You can observe with dynamic management view (DMV) sys.dm_os_performance_counter filtered by name column (Temp Tables Creation Rate), column cntr_value.
Let’s see this below example.
CREATE PROCEDURE dbo.test_caching
AS
CREATE TABLE #temp1 (cola int, colb int);
DECLARE @i AS INT;
SET @i = 1;
INSERT INTO #temp1 VALUES (@i , @i + 100);
DROP TABLE #temp1;
The stored procedure above will create temporary table, fill with some value, and drop it. Then I call the stored procedure from another batch as below
DECLARE @i AS INT;
SET @i = 0;
WHILE @i < 10
BEGIN
EXEC dbo.test_caching
SET @i = @i + 1;
END
Before the iteration gets executed, query the DMV to get cntr_value, you might get different value than I do. In my computer, I get the value : 15. Now execute the iteration, and query back the DMV, amazingly I still get only 1 incremented value (i.e. 16), even though I iterate it by more than once (10 times).
Now change the stored procedure to create unique clustered index on cola as below:
ALTER PROCEDURE dbo.test_caching
AS
CREATE TABLE #temp1 (cola int, colb int);
CREATE UNIQUE CLUSTERED INDEX IX_1 ON #temp1(cola);
DECLARE @i AS INT;
SET @i = 1;
INSERT INTO #temp1 VALUES (@i , @i + 100);
DROP TABLE #temp1;
Measure the DMV’s cntr_value, this time the value is 16 (in my computer). Execute the iteration code above. After execution, I get cntr_value is 26, from here I know that temporary table is not cached for each iteration, and it will impact negatively to performance.
To make tempdb cache works, you need to avoid some condition in temporary table creation :
1. Do not create index and constraint explicitly.
2. Do not create temporary index in dynamic SQL.
3. Tempdb cache only works when it is created in database routine.
That’s the trick!