原文: 監測誰用了SQL Server的Tempdb空間

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

Tempdb ?系統數據庫是一個全局資源，供連接到?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。

監測誰用了SQL Server的Tempdb空間