SQL Server資料庫的預存程序中定義的暫存資料表，真的有必要顯式刪除（drop table #tableName）嗎？

標籤：觀察   判斷   條件   計數   注意   rom   索引   tar   link   

 

本文出處：http://www.cnblogs.com/wy123/p/6704619.html 

 

 

問題背景

在寫SQL Server預存程序中，如果預存程序中定義了暫存資料表，
有些人習慣在預存程序結束的時候一個一個顯式地刪除過程中定義的暫存資料表（drop table #tName），有些人又沒有這個習慣，
對於不明真相的群眾或者喜歡思考的人會問，預存程序中定義的暫存資料表，最後要不要主動刪除，為什嗎？
或者說是不是預存程序結束的時候刪除暫存資料表更加規範？
不止一個人問過這個問題了，說實在話，本人之前確實不清楚，只是認為，顯式刪掉或者不刪都行，暫存資料表在當前Session斷開之後會自動釋放
那麼預存程序中定義的暫存資料表，在使用完之後，到底刪還是不刪？顯式刪除與不做刪除有無區別？
本文將對此問題進行一個粗淺的分析，如有不對的地方，還望指出，謝謝。

 

預存程序中暫存資料表的表結構也有緩並且會被重用

那麼到底需不需要顯式刪除,顯式刪除或者是不刪除有什麼區別？
這中間涉及到一個暫存資料表緩衝的知識點，首先看什麼是暫存資料表的緩衝。
緩衝暫存資料表是SQL SERVER 2005以來的一個新特性，
暫存資料表的建立時需要往臨時庫系統資料表中寫入資料（中繼資料，暫存資料表的表結構資訊），跟普通的增刪改操作一樣，這個過程需要一定的資源消耗
在滿足一定條件的情況下（後面說需要滿足的條件是什麼），
每當使用者請求完成之後（當然這個使用者請求的SQL中包含了暫存資料表），暫存資料表的中繼資料將會儲存在臨時庫（tempdb）的系統資料表中
雖然在使用者看來，當前Session建立的暫存資料表，對其他Session事不可見的，在Session斷開或者暫存資料表被刪除（drop）之後，將不可訪問。
但是當新的Session調用同樣的包含了建立暫存資料表的代碼，SQL Server內部會重用之前Session執行時建立過的暫存資料表，而無需再次定義暫存資料表。
這樣的話可以節約一些建立表的步驟所消耗的資源。

 

上面是理論，下面來做個小實驗示範上面的理論，首先來看不同Session之間暫存資料表“重用”的現象。
首先這裡要藉助系統檢視表sys.dm_os_performance_counters 來判斷暫存資料表的建立次數，該系統資料表中計數器的名稱為：Temp Tables Creation Rate。

建立如下預存程序，預存程序中定義了一個暫存資料表，

create procedure Proc_TestTempTableasbegin        create table #t20170413    (        col_1 varchar(100) ,        col_2 varchar(100)    )    insert into #t20170413 values (‘aaa‘,‘bbb‘);    select * from #t20170413    --select * from tempdb.sys.tables where name like ‘#t20170413%‘end

在預存程序建立之後，第一次執行的時候，來觀察一個現象，如下

很明顯，sys.dm_os_performance_counters系統資料表中的Temp Tables Creation Rate計數器加了1，也就是說在執行預存程序中過程中發生了一次暫存資料表的建立動作
然後繼續再次執行上面的代碼

同樣的代碼，這一次sys.dm_os_performance_counters系統資料表中的Temp Tables Creation Rate計數器沒有加1，
為什麼明明是預存程序中定義了暫存資料表，上面執行一次，Temp Tables Creation Rate加1，然後再次執行就不加1了?
這個就是暫存資料表重用的現象（嚴格說是暫存資料表的表結構或者表定義，而不包含資料），
因為第一次執行預存程序的時候建立了暫存資料表，然後再次執行預存程序的時候就重用了第一次的暫存資料表。　　

　　那怎麼證明該預存程序第二次執行的時候重用了第一次建立的暫存資料表？
　　對預存程序稍作修改，預存程序中加一句代碼，查詢臨時庫中該暫存資料表資訊

　　然後執行兩次如下代碼，下面是第二次執行的結果（下面會做解釋為什麼是第二次的執行的結果），
　　在暫存資料表被重用的時候查詢出來當前暫存資料表的資訊，發現暫存資料表建立次數並沒有增加，也就是說暫存資料表被重用了

　　既然說暫存資料表重用了，那麼暫存資料表一定存在於臨時庫的系統資料表中，那麼如何證明這個預存程序的暫存資料表在臨時庫中呢？
　　上面顯示的暫存資料表的Id是-1297292959，那麼這裡就臨時庫中查詢Id = -1297292959的表資訊，發現果然存在這個一張表。
　　臨時庫中的這個表資訊除了名字和modify_date不一樣，modify_date據觀察是暫存資料表被重用的時間，也就是暫存資料表被重用一次就修改一次modify_date
　　其他資訊完全一致，這就是說明，預存程序第一次執行完成之後，它所建立的暫存資料表被緩衝了起來（至於名字不同，後面再解釋），
　　當再次執行該預存程序的時候可以重用第一次執行預存程序時候建立的暫存資料表的表結構。

 

預存程序中顯式刪除暫存資料表，到底有沒有用處？

對上面的預存程序做如下修改，在預存程序結束之前顯式刪除定義的暫存資料表

　　然後再次執行如下的測試代碼，注意是第二次執行的結果（下面會做解釋為什麼是第二次的執行的結果）

　　然後繼續在臨時庫的系統資料表中查詢上述Id的系統，發現暫存資料表依舊存在於系統資料表中，即便是預存程序中顯式刪除（drop table #t20170413）

　　這裡說明，即便在預存程序中顯式調用了刪除暫存資料表的操作，暫存資料表依舊會存在得臨時庫的系統資料表中，也就是說暫存資料表依舊會被緩衝。
　　並不會因為在預存程序中顯式刪除而真正的刪除，暫存資料表對象會緩衝在臨時庫的系統資料表中。
　　之所以Session中查詢到的暫存資料表的名字與系統資料表中查詢到的暫存資料表的名字不同，原因是暫存資料表從建立到緩衝，在內部只是發生了一個對當前Session暫存資料表重新命名的過程。

事實證明：
對於預存程序的暫存資料表，在滿足可快取的前提下（只是表結構，當然不包括暫存資料表的資料），
你刪，或者不刪，他都會緩衝在臨時庫中，並不因為顯式Drop暫存資料表，暫存資料表就會被真正的刪除，這是SQL Server專門為此做的最佳化，你真的不用為刪除暫存資料表而操心或者糾結
這裡回到一開始的問題，預存程序中有沒有必要顯式刪除暫存資料表就有答案了：對於預存程序的建立的暫存資料表，沒必要刪除，對於滿足可快取的暫存資料表對象，想刪也刪不掉！

 

預存程序中定義的暫存資料表，只有滿足一定的條件，才會被緩衝重用

　　上面說了，暫存資料表的重用是要滿足一定條件的，如下條件將會導致暫存資料表無法重用

1，建立暫存資料表的時候存在命名約束（這一點非常操蛋，不僅僅是緩衝問題，曾經遇到過坑，有機會示範）
2，在暫存資料表建立之後執行DDL操作，比如建立索引等，但是這個DDL不包括drop 暫存資料表和truncate暫存資料表
3，動態SQL方式建立的暫存資料表
4，在不同的範圍之內建立的暫存資料表，應該是預存程序調用另外一個預存程序，另外一個預存程序定義的暫存資料表，這一點還沒有具體研究
5，預存程序以WITH RECOMPILE重編譯的方式運行

　　比如在上面的預存程序，在暫存資料表定義之後，建立一個索引，
　　此舉將會造成暫存資料表無法重用，這種情況下，不管你刪或者不刪，預存程序執行完成Session斷開之後，暫存資料表都不會緩衝（在臨時庫中）
　　這一點就不示範了，有興趣的自己測試

 

　　解釋另外一個問題：
　　既然認為無法刪除緩衝的暫存資料表，正常情況下，緩衝的暫存資料表什麼情況下會被刪除？
　　上面說都是第二次啟動並執行，因為在預存程序重建之後（create或者alter），這個預存程序中定義的暫存資料表都會被清理掉
　　只有重建了預存程序，第一次執行之後，緩衝的暫存資料表在第二次執行的時候才能被重用
　　當然這一點也和容易驗證，緩衝暫存資料表之後，然後alter 預存程序，
　　然後根據緩衝暫存資料表的Id去查詢臨時庫中sys .tables的資訊，這個緩衝的表會在1~2秒之後被刪除（個人測實驗證過）
　　另外顯式執行DBCC FREEPROCCACHE，也能刪除緩衝的暫存資料表。
　　其實也不難理解，緩衝的對象是跟執行計畫緩衝綁定的，如果執行計畫本身就不存在了，那麼緩衝的暫存資料表對象也將會被請處理。

 

並發執行的情況下，暫存資料表能否重用？

　　並發線程之間當然不會重用同一個暫存資料表，如果不是這樣的話，SQL Server也不用混江湖了，並發的每個線程會建立自己的暫存資料表。
　　參考如下是在並發情況下，tempdb產生的暫存資料表的情況，每個線程調用預存程序產生的暫存資料表尾碼都是不一樣的。
　　並發調用預存程序的時候，每個線程會產生屬於自己的暫存資料表，重用暫存資料表是發生在當前線程執行完成之後，其他Session重新調用預存程序時候才能重用已緩衝的暫存資料表。
　　鑒於本文不是專門說明暫存資料表的，這裡就不多說了。

　　

 

顯式刪除暫存資料表與否的效能測試

既然上面說了，如果預存程序中定義的暫存資料表滿足暫存資料表被緩衝的條件的情況下，預存程序中是否刪除暫存資料表，暫存資料表都一樣會被緩衝
那麼，如果真的指定了顯式刪除暫存資料表操作，與沒有顯式指定刪除暫存資料表，效能上有沒有差別呢？
抱著以資料說話的態度，分別在預存程序中不刪除與顯式刪除暫存資料表，用SQLQueryStress做了一系列的效能測試
結果如下

不顯式刪除暫存資料表	顯式刪除暫存資料表

測試結果如下， 

　　

　　測試過程部分（不浪費部落格園的圖片伺服器資源了，隨便截了兩張）

　　

　　從測試結果看，確實有一些差異，不過這個差別是非常小的，
　　第一組測試結果5000次調用產生了0.07秒的差距
　　第二組測試結果20000次調用產生了0.35秒的差距，平均到一次差距也就在微妙級，即便是顯式調用刪除，對效能來說是有一點點影響，不過這個影響也是無傷大雅。
　　不過這個內部的原始一定要弄清楚，有沒有必要刪除，以及原因，這個才是原則性的問題！

　   至於暫存資料表資料佔用的空間，也不是說顯式刪除就釋放，不刪除就不釋放，應該是有後台進程來做這個工作的，個人建議不用為這個問題瞎操心。
　　寫預存程序的時候，多寫一點好一點的SQL語句，比糾結這個強多了。

多囉嗦一句：
有些人的觀念是根深蒂固的，對於習慣刪除暫存資料表的人，覺得這麼做“規範”，“專業”，雖然他沒有確切的理由說明顯式刪除暫存資料表的必要性。
但是你要跟他說沒必要刪除暫存資料表，一定會激怒他，好多程式員都是這樣的，你否認他根深蒂固的一個觀點的時候，他是很惱火的。
從生物學上說，這個是屬於“印隨行為”，或許是當初的師傅說的，或者膜拜的對象這麼做了，或者聽高人說過這麼做比較好，然後自己就一直這麼做了並且堅信不疑。
當然，包括我自己在某些時候也有此種行為，思維被曾經的某一些經曆固化，然後一直束縛自己的認知。
不過對於無傷大雅的問題，就隨他去了，沒必要說服他，弄不好他反過來覺得你業餘，希望小夥伴們明辨，好似乎跑題了……

 

總結：　　　

　　本文從預存程序中的暫存資料表是否需要顯式刪除入手，簡單介紹了暫存資料表重用的現象和前提條件，以及有無必要顯式刪除暫存資料表，
　　同時測試了暫存資料表在滿足重用的情況下，暫存資料表顯式刪除與否的效能問題，對於預存程序中定義的暫存資料表，不管是否能否緩衝重用，都不建議顯式刪除。

 

參考連結：https://www.mssqltips.com/sqlservertip/4406/sql-server-temporary-table-caching/
　　　　　http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

 

SQL Server資料庫的預存程序中定義的暫存資料表，真的有必要顯式刪除（drop table #tableName）嗎？