在一些先决条件下，SQL Server可以缓存临时表（cache Temp Tables）。缓存临时表意味着当你创建反复创建同个临时表时，SQL Server就可以重用它们。这会从整体上大幅度提高你的工作量（性能），因为SQL Server不需要访问内存里的特定页（PFS，GAM，SGAM），经常访问这些页在工作量大的情况下会引起加锁竞争（Latch Contention）。Paul White有一篇很棒的文章详细描述这个情况，可以点此围观下。
临时表缓存的条件之一是不能在存储过程里混合使用DML（Data Manipulation Language 数据操作语言）和DDL（Data Definition Language 数据定义语言）语句。我们来看下面的代码：
1 -- Create a new stored procedure 2 CREATE PROCEDURE PopulateTempTable 3 AS 4 BEGIN 5 -- Create a new temp table 6 CREATE TABLE #TempTable 7 ( 8 Col1 INT IDENTITY(1, 1), 9 Col2 CHAR(4000), 10 Col3 CHAR(4000) 11 ) 12 13 -- Create a unique clustered index on the previous created temp table 14 CREATE UNIQUE CLUSTERED INDEX idx_c1 ON #TempTable(Col1) 15 16 -- Insert 10 dummy records 17 DECLARE @i INT = 0 18 WHILE (@i < 10) 19 BEGIN 20 INSERT INTO #TempTable VALUES ('Woody', 'Tu') 21 SET @i += 1 22 END 23 END 24 GO |
这里你通过DDL语句（CREATE UNIQUE CLUSTERED INDEX ）创建了索引，这就是说你混合使用了DDL和DML语句。因此SQL Server不能缓存你的临时表。你可以从下面例子里的DMV sys.dm_os_performance_counters ，通过跟踪性能计数器Temp Tables Creation Rate 来验证：
1 DECLARE @table_counter_before_test BIGINT; 2 SELECT @table_counter_before_test = cntr_value FROM sys.dm_os_performance_counters 3 WHERE counter_name = 'Temp Tables Creation Rate' 4 5 DECLARE @i INT = 0 6 WHILE (@i < 1000) 7 BEGIN 8 EXEC PopulateTempTable 9 SET @i += 1 10 END 11 12 DECLARE @table_counter_after_test BIGINT; 13 SELECT @table_counter_after_test = cntr_value FROM sys.dm_os_performance_counters 14 WHERE counter_name = 'Temp Tables Creation Rate' 15 16 PRINT 'Temp tables created during the test: ' + CONVERT(VARCHAR(100), @table_counter_after_test - @table_counter_before_test) 17 GO |
