Sql-Server
為什麼在創建釋放 tempdb 空間的儲存過程結束時截斷臨時表會更快？
SQL Server 記憶體在儲存過程中創建的臨時表，並且僅在過程結束並隨後執行時重命名它們。我的問題與 tempdb 空間何時釋放有關。我讀過該表在程序結束時被截斷。我在評論中讀到這是在每個會話的基礎上處理的，並且在 MSDN上看到了一個關於是否需要清理的問題。但是如果它從來沒有被同一個會話執行兩次呢？
我還聽說有一個後台垃圾收集程序，一旦表超出範圍，就會釋放該空間。
在創建臨時表的儲存過程結束時截斷臨時表似乎會導致表在 tempdb 中用於釋放數據的空間比不使用 truncate 語句時釋放得更快，儘管預期相反。為什麼？
使用或不使用這樣的截斷語句對性能有何影響？使用 SNAPSHOT 隔離時，tempdb 經常受到壓力，我認為盡快從大型臨時表中釋放 tempdb 中使用的空間可以防止 tempdb 不必要的增長。這種潛在的空間節省會以性能為代價嗎？
這是一些重現問題的程式碼（主要來自@TheGameiswar，有一些更改）：
SET NOCOUNT ON; GO ALTER PROC usp_test AS BEGIN IF object_id('tempdb..#temp') IS NOT NULL DROP TABLE #temp SELECT * INTO #temp FROM [dbo].[Event_28] -- This is a table with 15313 rows, using 35648 KB according to sp_spaceused --SELECT SUM(user_object_reserved_page_count) AS [user object pages used] -- ,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space in MB] -- ,getdate() AS BeforeTruncate --FROM tempdb.sys.dm_db_file_space_usage; -- TRUNCATE TABLE #temp --SELECT SUM(user_object_reserved_page_count) AS [user object pages used] -- ,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space in MB] -- ,getdate() AS AfterTruncate --FROM tempdb.sys.dm_db_file_space_usage; END GO SELECT SUM(user_object_reserved_page_count) AS [user object pages used] ,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space in MB] ,getdate() AS 'before' FROM tempdb.sys.dm_db_file_space_usage; EXEC usp_test GO SELECT SUM(user_object_reserved_page_count) AS [user object pages used] ,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space in MB] ,getdate() AS 'final' FROM tempdb.sys.dm_db_file_space_usage; GO 40註釋的行在某些執行中被註釋掉，而在其他執行中未註釋。當
TRUNCATE被註釋掉時，tempdb.sys.dm_db_file_space_usage查詢結果（多 4472 頁和大 34.9375 MB）與執行過程之前的結果匹配需要 2.25 到 4.5 秒。在未註釋行（包括TRUNCATE）的情況下，只需要大約 0.11 - 0.9 秒。這些結果來自實時系統，在此實驗期間源表中有一些小數據增長。註釋掉程式碼的範例輸出（從第一個到最後一個“最終”條目的 2.69 秒）：
user object pages used user object space in MB before ---------------------- --------------------------------------- ----------------------- 1536 12.000000 2017-10-04 21:03:42.197 Beginning execution loop user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:42.423 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:42.533 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:42.643 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:42.883 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:42.990 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:43.100 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:43.450 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:43.650 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:43.767 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:43.993 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:44.103 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:44.213 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:44.437 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:44.553 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:44.663 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:44.887 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6000 46.875000 2017-10-04 21:03:45.003 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 1536 12.000000 2017-10-04 21:03:45.113未註釋程式碼的範例結果（從第一個到最後一個“最終”條目的 0.11 秒）：
user object pages used user object space in MB before ---------------------- --------------------------------------- ----------------------- 1536 12.000000 2017-10-04 21:07:39.807 user object pages used user object space in MB BeforeTruncate ---------------------- --------------------------------------- ----------------------- 6016 47.000000 2017-10-04 21:07:39.923 user object pages used user object space in MB AfterTruncate ---------------------- --------------------------------------- ----------------------- 6016 47.000000 2017-10-04 21:07:39.923 Beginning execution loop user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 6016 47.000000 2017-10-04 21:07:40.160 user object pages used user object space in MB final ---------------------- --------------------------------------- ----------------------- 1536 12.000000 2017-10-04 21:07:40.270
在創建臨時表的儲存過程結束時截斷臨時表似乎會導致表在 tempdb 中用於釋放數據的空間比不使用 truncate 語句時釋放得更快，儘管預期相反。為什麼？
如果臨時表足夠大（超過 128 個盤區），物理頁釋放將被延遲，並由後台系統任務執行。無論是否使用顯式，這都是正確的
TRUNCATE TABLE。唯一的區別是一個微小的實現細節。一個明確的
TRUNCATE TABLE碰巧創建了一個計時器比臨時表清理創建的（否則相同的）延遲刪除任務更短的任務：
這是偶然的還是設計的，這是任何人的猜測。它當然可以隨時更改，因為這種詳細程度超出了支持的產品表面積。
如果您使用（大部分）未記錄的跟踪標誌全域禁用延遲刪除：
DBCC TRACEON (671, -1);…在這兩種情況下，釋放是同步執行的，您不會看到時間上的差異。
使用或不使用這樣的截斷語句對性能有何影響？使用 SNAPSHOT 隔離時，tempdb 經常受到壓力，我認為盡快從大型臨時表中釋放 tempdb 中使用的空間可以防止 tempdb 不必要的增長。這種潛在的空間節省會以性能為代價嗎？
我嚴重懷疑這無論如何都會產生很大的不同。如果tempdb的大小適合您的工作負載的峰值需求，那麼延遲下降是在一秒還是三秒後發生都無關緊要。做同樣的工作；這只是時間上的微小差異。
另一方面：如果您覺得
TRUNCATE TABLE在儲存過程結束時使用臨時表更舒服，那就去吧。我不知道這樣做有什麼特別的缺點。
