如何監測誰用了SQL Server的Tempdb空間？

摘自：http://blogs.msdn.com/b/apgcdsd/archive/2011/02/11/sql-server-tempdb.aspx

系統資料庫是一個全域資源，供串連到 SQL Server 執行個體的所有使用者使用。在現在的SQL Server裡，其使用頻率可能會超過使用者的想象。如果Tempdb空間耗盡，許多操作將不能完成。

作為一個支援工程師，會被經常問到象“我的Tempdb為什麼這麼大？”“是誰把我的Tempdb空間用完的？”在SQL 2000的時候，這個問題很難回答。好在SQL 2005以後，引入了一張新的管理檢視：sys.dm_db_file_space_usage。通過查詢這張視圖，能瞭解tempdb的空間使用方式，能知道tempdb的空間是被哪一塊對象使用掉的，是使用者物件(user_object_reserved_page_count欄位)，還是系統對象（internal_object_reserved_page_count欄位），還是版本儲存區（version_store_reserved_page_count欄位）。

在討論Tempdb空間使用之前，我們先簡單介紹一下通常什麼操作會大量使用Tempdb。在SQL 2005和SQL 2008裡，使用Tempdb空間的遠遠不止是暫存資料表。常見的使用對象有：

使用者物件(user_object_reserved_page_count)

使用者物件由使用者顯式建立。這些對象可以位於使用者會話的範圍中，也可位於建立對象所用常式的範圍中。 可以是預存程序、觸發器或使用者定義函數。 使用者物件可以是下列項之一：

• 使用者定義的表和索引
• 系統資料表和索引
• 全域暫存資料表和索引
• 局部暫存資料表和索引
• table 變數
• 資料表值函式中返回的表

內部對象（internal_object_reserved_page_count）

內部對象是根據需要由 SQL Server 資料庫引擎建立的，用於處理 SQL Server 語句。 內部對象可以在語句的範圍中建立和刪除。 內部對象可以是下列項之一：

• 用於遊標。
• 用於雜湊聯結或雜湊彙總操作的查詢。
• 某些 GROUP BY、ORDER BY 或 UNION 查詢的中間排序結果。

版本儲存（version_store_reserved_page_count）

版本儲存區主要用來支援Snapshot交易隔離等級，以及SQL 2005以後推出的一些其他提高資料庫並發度的新功能。

由此可見，光從使用者發過來的語句本身，是很難判斷這個串連的操作是否會使用Tempdb的。一個典型的例子，就是某些查詢。如果表格上有良好的索引做支援，SQL Server不需要做雜湊聯結(Hash Join)，那這個查詢就不會用Tempdb。反之，如果表格很大，又沒有好的索引，那Tempdb使用量就可能不小。

tempdb空間使用的一大特點，是只有一部分對象，例如使用者建立的暫存資料表、table變數等，可以用sys.allocation_units 和 sys.partitions這樣的管理檢視來管理。許多內部對象和版本儲存在這些管理檢視裡沒有體現。所以，sp_spaceused的結果和真實使用會有很大差異，tempdb的空間使用是不能用sp_spaceused來跟蹤的。必須藉助sys.dm_db_file_space_usage這樣的管理檢視和管理函數，才能看到全貌。

 

下面以一個執行個體，討論一下如何用DBCC命令、管理檢視(DMV)以及管理函數(DMF)來監視是什麼語句正在使用tempdb。

為了使結果簡單，我們在測試之前先把SQL Server重起一次。

然後我們在Management Studio裡做一個串連（串連A），將下面語句輸入。這些語句會使用tempdb的空間。

 

select @@spid

go

use adventureworks

go

select getdate()

go

select * into #mySalesOrderDetail

from Sales.SalesOrderDetail

-- 建立一個temp table

-- 這個操作應該會申請user objects page

go

waitfor delay '0:0:2'

select getdate()

go

drop table #mySalesOrderDetail

-- 刪除一個temp table

-- 這個操作後user object page數量應該會下降

go

waitfor delay '0:0:2'

select getdate()

go

select top 100000 * from

[Sales].[SalesOrderDetail]

INNER JOIN [Sales].[SalesOrderHeader]

ON [Sales].[SalesOrderHeader] .[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID];

-- 這裡做了一個比較大的join.

-- 應該會有internal objects的申請.

go

select getdate()

-- join 語句做完以後internal objects page數目應該下降

go

 

那用什麼指令碼可用監視上面的行為呢？下面的指令碼就可以監視和發現當前的Tempdb使用者。這個指令碼需要在使用tempdb的語句開始運行之前開始。（讀者當然可以根據自己的喜好，修改這個指令碼。）

指令碼首先用“dbcc showfilestats”語句查詢當前tempdb的總體使用量。再查詢sys.dm_db_file_space_usage視圖，得到Tempdb裡當前總共有多少使用者物件、內部對象、以及版本儲存。然後查詢sys.dm_db_session_space_usage和sys.dm_exec_sessions，找到當前使用Tempdb的所有串連。最後通過sys.dm_exec_sql_text，找到這些串連正在啟動並執行語句。

 

use tempdb                                                               

-- 每隔1秒鐘運行一次，直到使用者手工終止指令碼運行

while 1=1                                                               

begin                                                                   

select getdate()                                                        

-- 從檔案級看tempdb使用方式

dbcc showfilestats                                                      

-- Query 1

-- 返回所有做過空間申請的session資訊

Select 'Tempdb' as DB, getdate() as Time,                                                        

    SUM (user_object_reserved_page_count)*8 as user_objects_kb,         

    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb, 

    SUM (version_store_reserved_page_count)*8  as version_store_kb,     

    SUM (unallocated_extent_page_count)*8 as freespace_kb               

From sys.dm_db_file_space_usage                                         

Where database_id = 2                                                    

-- Query 2

-- 這個管理檢視能夠反映當時tempdb空間的總體分配

SELECT t1.session_id,                                                   

t1.internal_objects_alloc_page_count,  t1.user_objects_alloc_page_count,

t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,

t3.*

from sys.dm_db_session_space_usage  t1 ,                               

-- 反映每個session累計空間申請

sys.dm_exec_sessions as t3

-- 每個session的資訊

where

t1.session_id = t3.session_id

and (t1.internal_objects_alloc_page_count>0

or t1.user_objects_alloc_page_count >0

or t1.internal_objects_dealloc_page_count>0

or t1.user_objects_dealloc_page_count>0)

-- Query 3

-- 返回正在運行並且做過空間申請的session正在啟動並執行語句

SELECT t1.session_id,                                                    

st.text                                                        

from sys.dm_db_session_space_usage as t1,                               

sys.dm_exec_requests as t4                                              

CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st                   

 where  t1.session_id = t4.session_id                                       

   and t1.session_id >50                                                

and (t1.internal_objects_alloc_page_count>0

or t1.user_objects_alloc_page_count >0

or t1.internal_objects_dealloc_page_count>0

or t1.user_objects_dealloc_page_count>0)                                              

waitfor delay '0:0:1'                                                    

end                          

  

 

圖1

 

在運行這個指令碼的串連（串連B）裡（圖1），我們選擇好“Result to File”。先開始運行它，指定輸出檔案路徑。然後，我們再運行串連A（圖2）。串連A運行結束後，手工停止串連B的運行。

  

 

圖2

 

在串連A的結果中（），可以得到四個時間。圖片上的例子，是：

11:39:36.513     -- 開始建立temp table

11:39:38.920 – 開始刪除temp table

11:39:40.937 – 開始查詢

11:39:45.733 – 查詢結束

 

 串連B產生的是一個文字檔。利用一些有“列出所有包含某個特定字串”行功能的編輯器工具，可以把每個命令結果挑出來。

 從串連B產生的文字檔裡所有dbcc showfilestats的結果（圖3），可以看出tempdb的使用空間有過兩次增長（從23到210，從47到118），中間有一次下降（從210到47）。

 

 

圖3

  

從串連B產生的文字檔裡所有Query 1的結果（圖3），我們可以看到有三段時間，user object和internal object空間有申請和釋放動作。它們分別是11:39:36 – 11:39:37 (user_objects_kb增長)，11:39:40 – 11:39:41 (user_objects_kb下降)，11:39:40 – 11:39:43（internal_objects_kb增長）。

 

 

圖4

  

從Query 2的結果（圖4）可以看到Connection A在這三個時間段都處於運行狀態。

  

 

圖5

  

根據時間，可以從Query 3的結果（圖5）裡找到Connection A當時正在啟動並執行語句。例如在11:39:40 – 11:39:43（internal_objects_kb增長）這段時間裡，一直都在運行下面這句話：

 

 

圖6

  

從上面的結果可以看出，串連A的語句中，用tempdb最多的時間點在11：39：41和11：39：42之間，串連正在做圖6裡面的那條查詢語句。SQL Server需要空間存放一些內部對象，來完成Inner Join。