在一些先決條件下,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
當你運作這個代碼時,SQL Server需要建立1000個單獨的臨時表,這個從SSMS的輸出視窗就可以看到。
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIyZuBnLwQTNyITMykjM0QDOwAzMvwlNwUTMwIzLchDNzATN38CX1EDMyc2bsJ2Lc12bj5ycn9Gbi52YuAzcldWYtl2Lc9CX6MHc0RHaiojIsJye.png)
通過PRIMARY KEY限制來強制UNIQUE CLUSTERED INDEX就很容易克服這個問題。在這個方式下,你沒有混合使用DDL和DML語句,SQL Server最後也能緩存你的臨時表。
1 ALTER PROCEDURE PopulateTempTable
2 AS
3 BEGIN
4 -- Create a new temp table
5 CREATE TABLE #TempTable
6 (
7 Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index
8 Col2 CHAR(4000),
9 Col3 CHAR(4000)
10 )
11
12 -- Insert 10 dummy records
13 DECLARE @i INT = 0
14 WHILE (@i < 10)
15 BEGIN
16 INSERT INTO #TempTable VALUES ('Woody', 'Tu')
17 SET @i += 1
18 END
19 END
20 GO
當你重新執行剛才用來跟蹤相關計數器的代碼,可以看到SQL Server值建立了一次臨時表并重用它了:
這個結論也意味着,當你建立額外的非聚集索引(Non-Clustered Indexes)時,SQL Server也不能緩存臨時表,因為在你的存儲過程裡,你又一次混合使用DDL和DML語句。
但在SQL Server 2014裡,你就可以克服這個限制,因為現在你可以在CREATE TABLE語句行裡建立索引。來看下面的代碼:
1 ALTER PROCEDURE PopulateTempTable
2 AS
3 BEGIN
4 -- Create a new temp table
5 CREATE TABLE #TempTable
6 (
7 Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index
8 Col2 CHAR(100) INDEX idx_Col2,
9 Col3 CHAR(100) INDEX idx_Col3
10 )
11
12 -- Insert 10 dummy records
13 DECLARE @i INT = 0
14 WHILE (@i < 10)
15 BEGIN
16 INSERT INTO #TempTable VALUES ('Woody', 'Tu')
17 SET @i += 1
18 END
19 END
20 GO
如你所見,我在建立臨時表本身的時候,就在臨時表上直接建立2個額外的非聚集索引。又一次我們沒有混合使用DDL和DML語句,SQL Server又一次可以緩存并重用你的臨時表。
在SQL Server 2014裡,在臨時表上定義行内定義索引,避開混合使用DML和DDL語句,讓臨時表隻建立一次并重用,是一個很棒的功能!
這個新功能怎樣?歡迎在下面評論裡告訴我。
參考文章:
https://www.sqlpassion.at/archive/2013/06/27/improved-temp-table-caching-in-sql-server-2014/
注:此文章為
WoodyTu學習MS SQL技術,收集整理相關文檔撰寫,歡迎轉載,請在文章頁面明顯位置給出此文連結!
若您覺得這篇文章還不錯請點選下右下角的推薦,有了您的支援才能激發作者更大的寫作熱情,非常感謝!