五餅二魚工作室

最近因為剛好要年底了，一些朋友和客戶開始規劃資料庫年度的例行處理，不少都是利用虛擬化的環境來做個演練測試，而大部分的也都沒有甚麼狀況，頂多就是忘記處理步驟或者是遺忘相關指令，討論一下就可以解決了，但就是有個朋友遇到的問題比較棘手一點，就花點時間整理一下相關處理步驟。

由於該朋友所管理的單一資料庫差不多有 400GB 的大小，但是在模擬年度相關作業處理的時候，居然會把 Tempdb 搞到掛掉，造成 SQL Server 無法正常啟動。對我來說這個實在不是很常見到的狀況，於是連線查看一下，原來是不知道甚麼樣的原因，造成 SQL Server 的 tempdb 長大到 150GB ，導致磁碟機 C 都被吃光了。知道問題那就容易處理多了，此部分可以利用 MSDN 上的方式，利用最低組態啟動 SQL Server 之後，便可以下指令 ALTER DATABASE tempdb MODIFY FILE 的方式，將 tempdb 指定存放到其他目錄下並且重新指定大小，再將原本的參數取消之後，就可以順利的重新啟動 SQL Server 了。在這裡為了也能讓效能有所改善，在考量資料庫主機有八個核心的狀況下，我們也同時將 tempdb 再多增加 3 個 ndf 檔案，這樣當有需要使用 tempdb 的時候，可以有較佳的效能。

雖然解決了問題，但朋友還是希望能找到問題的根源，以免下次又再發生同樣的狀況，所以我們用一些方法來找可能占用 tempdb 的相關處理：

• 暫時性物件 : 由於部分程式或者是處理的時候，可能會利用 # 或者是 ## 的方式建立一些暫時性的 Table 物件。使用 # 和 ## 則表示這個物件只存留在 Session 來存留的時候， Session 關閉則該物件也會自動刪除；但如果是使用 ## 的話，則這個 Table 除非自己下刪除指令，否則會保留到 SQL Server 關閉。為了查看是否有這類型的物件，我們利用以下的指令來處理：

SELECT
   o.Name table_name, p.used_page_count * 8 used_size, p.reserved_page_count * 8 reserved_size, p.row_count
  FROM sys.dm_db_partition_stats p
  INNER JOIN sys.objects AS o  ON o.object_id = p.object_id
  WHERE o.type_desc = 'USER_TABLE' AND o.is_ms_shipped = 0

如果這個部分如果發覺在系統上會有大量使用這類 temporary 的物件時，其實可以視情況將這些給換成 User-defined Table Variable 來取代會更較為合適。

• 查詢指令：當我們在下一些 SQL 指令的時候，像是又加入 Order By , Group By 之類的語法，SQL Server 必須撈大量的資料放到記憶體中來處理，但如果並沒有足夠記憶體的時候，便會使用 tempdb 的空間來做處理，此時我們會希望來找到這些指令，因此在 SQL Server 2005 之外，我們可以配合兩個 DMV 來做處理：
  • sys.dm_db_session_space_usage
  • sys.dm_db_task_space usage

由於這兩個 DMV 所能抓到的是 Session 還存在的時候，當 SQL 命令還在執行的時候，則這時候可以配合 sys.dm_db_task_space_usage 來取得所使用的 Page，因此我們可以用類似以下的語法來進行處理：

;WITH task_space_usage AS (
    SELECT session_id, request_id, SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages / 128.0 AS [internal object MB space],
       TSU.dealloc_pages / 128.0 AS [internal object dealloc MB space],
       EST.text
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ ON TSU.session_id = ERQ.session_id AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
WHERE EST.text IS NOT NULL
ORDER BY 3 DESC

如果命令已經執行完畢，上面這個指令就無法取得資訊，因此這個時候可以利用 sys.dm_db_session_space_uage，可以參考以下的使用與法來處理：

SELECT 
  session_id, 
  internal_objects_alloc_page_count/128.0 internal_alloc_size, 
  internal_objects_dealloc_page_count/128.0 internal_dealloc_size
FROM sys.dm_db_session_space_usage AS s
WHERE s.session_id <> @@SPID and s.session_id > 50

上述兩個 DMV 可能因為執行的時間差取到不同的資料，因此可以建立幾個監控的 Table，在可能有異常的時段內利用這兩個 DMV 的配合，定時將相關的資料給記錄下來之後(建議可以 3~5分鐘)再做比對，一般來說這種需要花比較長時間的處理，會比較有機會佔用大量的空間。

 

目前透過這些方式，再來配合效能監視器，讓我們可以找到一些特殊處理在短時間內耗用大量的 tempdb，希望如果有類似狀況的朋友，這些方法對你們有幫助。如果想要更清楚知道相關處理，可以前往參考資料所列的網址，裡面也都有許多詳細的說明。 

備註 :

2016/01/13 修正 : 多謝 Rock 的指正，原本寫法 ## 開頭的物件，應該要寫成可以讓其他的 Session 共用讀取才對。不論用 # 或 ## 設定的暫時資料表，當 Session 關閉的時候正常來說物件都會刪除，但因為應用程式可能會採用 Connection Pool，因此變成程式中雖然看起來似乎 Session 關閉，但實際上 Session 並沒有關閉，只是交回由 Connection Pool 管控，因此造成對 SQL Server 來說並沒有真正關閉，使得建立的暫時性物件不會刪除，此部分 Rock 也建議另外一篇文章提供參考 ( 網址 )。

參考資料：

1. 部落客文章 「tempdb 異常修復」
2. MSDN 文章 「tempdb 磁碟空間不足的疑難排解」
3. Technet 文章「Working with tempdb in SQL Server 2005」
4. MSDN Blogs「Monitoring tempdb Transactions and Space usage」
5. MSSQL tips 「Properly Sizing the SQL Server TempDB Database」 
6. MSSQL tips 「SQL Server Tempdb Usage and Bottlenecks tracked with Extended Events」