原文: 監(jiān)測(cè)誰(shuí)用了SQL Server的Tempdb空間

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

Tempdb ?系統(tǒng)數(shù)據(jù)庫(kù)是一個(gè)全局資源，供連接到?SQL Server?實(shí)例的所有用戶使用。在現(xiàn)在的SQL Server里，其使用頻率可能會(huì)超過(guò)用戶的想象。如果Tempdb空間耗盡，許多操作將不能完成。

作為一個(gè)支持工程師，會(huì)被經(jīng)常問(wèn)到象“我的Tempdb為什么這么大？”“是誰(shuí)把我的Tempdb空間用完的？”在SQL 2000的時(shí)候，這個(gè)問(wèn)題很難回答。好在SQL 2005以后，引入了一張新的管理視圖：sys.dm_db_file_space_usage。通過(guò)查詢這張視圖，能了解tempdb的空間使用情況，能知道tempdb的空間是被哪一塊對(duì)象使用掉的，是用戶對(duì)象(user_object_reserved_page_count字段)，還是系統(tǒng)對(duì)象（internal_object_reserved_page_count字段），還是版本存儲(chǔ)區(qū)（version_store_reserved_page_count字段）。

在討論Tempdb空間使用之前，我們先簡(jiǎn)單介紹一下通常什么操作會(huì)大量使用Tempdb。在SQL 2005和SQL 2008里，使用Tempdb空間的遠(yuǎn)遠(yuǎn)不止是臨時(shí)表。常見的使用對(duì)象有：

用戶對(duì)象 (user_object_reserved_page_count)

用戶對(duì)象由用戶顯式創(chuàng)建。這些對(duì)象可以位于用戶會(huì)話的作用域中，也可位于創(chuàng)建對(duì)象所用例程的作用域中。?可以是存儲(chǔ)過(guò)程、觸發(fā)器或用戶定義函數(shù)。?用戶對(duì)象可以是下列項(xiàng)之一：

• 用戶定義的表和索引
• 系統(tǒng)表和索引
• 全局臨時(shí)表和索引
• 局部臨時(shí)表和索引
• table?變量
• 表值函數(shù)中返回的表

內(nèi)部對(duì)象 （internal_object_reserved_page_count）

內(nèi)部對(duì)象是根據(jù)需要由?SQL Server?數(shù)據(jù)庫(kù)引擎創(chuàng)建的，用于處理?SQL Server?語(yǔ)句。?內(nèi)部對(duì)象可以在語(yǔ)句的作用域中創(chuàng)建和刪除。?內(nèi)部對(duì)象可以是下列項(xiàng)之一：

• 用于游標(biāo)。
• 用于哈希聯(lián)接或哈希聚合操作的查詢。
• 某些?GROUP BY、ORDER BY?或?UNION?查詢的中間排序結(jié)果。

版本存儲(chǔ) （version_store_reserved_page_count）

版本存儲(chǔ)區(qū)主要用來(lái)支持Snapshot事務(wù)隔離級(jí)別，以及SQL 2005以后推出的一些其他提高數(shù)據(jù)庫(kù)并發(fā)度的新功能。

由此可見， 光從用戶發(fā)過(guò)來(lái)的語(yǔ)句本身，是很難判斷這個(gè)連接的操作是否會(huì)使用Tempdb的。 一個(gè)典型的例子，就是某些查詢。如果表格上有良好的索引做支持，SQL Server不需要做哈希聯(lián)接(Hash Join)，那這個(gè)查詢就不會(huì)用Tempdb。反之，如果表格很大，又沒(méi)有好的索引，那Tempdb使用量就可能不小。

tempdb空間使用的一大特點(diǎn)，是只有一部分對(duì)象，例如用戶創(chuàng)建的臨時(shí)表、table變量等，可以用sys.allocation_units和?sys.partitions這樣的管理視圖來(lái)管理。許多內(nèi)部對(duì)象和版本存儲(chǔ)在這些管理視圖里沒(méi)有體現(xiàn)。所以，sp_spaceused的結(jié)果和真實(shí)使用會(huì)有很大差異， tempdb的空間使用是不能用sp_spaceused來(lái)跟蹤的。必須借助sys.dm_db_file_space_usage這樣的管理視圖和管理函數(shù)，才能看到全貌。

?

下面以一個(gè)實(shí)例，討論一下如何用DBCC命令、管理視圖(DMV)以及管理函數(shù)(DMF)來(lái)監(jiān)視是什么語(yǔ)句正在使用tempdb。

為了使結(jié)果簡(jiǎn)單，我們?cè)跍y(cè)試之前先把SQL Server重起一次。

然后我們?cè)贛anagement Studio里做一個(gè)連接（連接A），將下面語(yǔ)句輸入。這些語(yǔ)句會(huì)使用tempdb的空間。

?

select @@spid

go

use adventureworks

go

select getdate()

go

select * into #mySalesOrderDetail

from Sales.SalesOrderDetail

--?創(chuàng)建一個(gè)temp table

--?這個(gè)操作應(yīng)該會(huì)申請(qǐng)user objects page

go

waitfor delay '0:0:2'

select getdate()

go

drop table #mySalesOrderDetail

--?刪除一個(gè)temp table

--?這個(gè)操作后user object page數(shù)量應(yīng)該會(huì)下降

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];

--?這里做了一個(gè)比較大的join.

--?應(yīng)該會(huì)有internal objects的申請(qǐng).

go

select getdate()

-- join?語(yǔ)句做完以后internal objects page數(shù)目應(yīng)該下降

go

?

那用什么腳本可用監(jiān)視上面的行為呢？ 下面的腳本就可以監(jiān)視和發(fā)現(xiàn)當(dāng)前的Tempdb使用者。 這個(gè)腳本需要在使用tempdb的語(yǔ)句開始運(yùn)行之前開始。（讀者當(dāng)然可以根據(jù)自己的喜好，修改這個(gè)腳本。）

腳本首先用“dbcc showfilestats”語(yǔ)句查詢當(dāng)前tempdb的總體使用量。再查詢sys.dm_db_file_space_usage視圖，得到Tempdb里當(dāng)前總共有多少用戶對(duì)象、內(nèi)部對(duì)象、以及版本存儲(chǔ)。然后查詢sys.dm_db_session_space_usage和sys.dm_exec_sessions，找到當(dāng)前使用Tempdb的所有連接。最后通過(guò)sys.dm_exec_sql_text，找到這些連接正在運(yùn)行的語(yǔ)句。

?

use tempdb???????????????????????????????????????????????????????????????

--?每隔1秒鐘運(yùn)行一次，直到用戶手工終止腳本運(yùn)行

while 1=1???????????????????????????????????????????????????????????????

begin???????????????????????????????????????????????????????????????????

select getdate()????????????????????????????????????????????????????????

--?從文件級(jí)看tempdb使用情況

dbcc showfilestats??????????????????????????????????????????????????????

-- Query 1

--?返回所有做過(guò)空間申請(qǐng)的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

--?這個(gè)管理視圖能夠反映當(dāng)時(shí)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 ,???????????????????????????????

--?反映每個(gè)session累計(jì)空間申請(qǐng)

sys.dm_exec_sessions as t3

--?每個(gè)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

--?返回正在運(yùn)行并且做過(guò)空間申請(qǐng)的session正在運(yùn)行的語(yǔ)句

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

?

在運(yùn)行這個(gè)腳本的連接（連接B）里（圖1），我們選擇好“Result to File”。先開始運(yùn)行它，指定輸出文件路徑。然后，我們?cè)龠\(yùn)行連接A（圖2）。連接A運(yùn)行結(jié)束后，手工停止連接B的運(yùn)行。

??

圖2

?

在連接A的結(jié)果中（），可以得到四個(gè)時(shí)間。圖片上的例子，是：

11:39:36.513?????--?開始創(chuàng)建temp table

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

11:39:40.937 –?開始查詢

11:39:45.733 –?查詢結(jié)束

?

?連接B生成的是一個(gè)文本文件。利用一些有“列出所有包含某個(gè)特定字符串”行功能的編輯器工具，可以把每個(gè)命令結(jié)果挑出來(lái)。

?從連接B生成的文本文件里所有dbcc showfilestats的結(jié)果（圖3），可以看出tempdb的使用空間有過(guò)兩次增長(zhǎng)（從23到210，從47到118），中間有一次下降（從210到47）。

?

圖3

??

從連接B生成的文本文件里所有Query 1的結(jié)果（圖3），我們可以看到有三段時(shí)間，user object和internal object空間有申請(qǐng)和釋放動(dòng)作。它們分別是11:39:36 – 11:39:37 (user_objects_kb增長(zhǎng))，11:39:40 – 11:39:41 (user_objects_kb下降)，11:39:40 – 11:39:43（internal_objects_kb增長(zhǎng)）。

?

圖4

??

從Query 2的結(jié)果（圖4）可以看到Connection A在這三個(gè)時(shí)間段都處于運(yùn)行狀態(tài)。

??

圖5

??

根據(jù)時(shí)間，可以從Query 3的結(jié)果（圖5）里找到Connection A當(dāng)時(shí)正在運(yùn)行的語(yǔ)句。例如在11:39:40 – 11:39:43（internal_objects_kb增長(zhǎng)）這段時(shí)間里，一直都在運(yùn)行下面這句話：

?

圖6

??

從上面的結(jié)果可以看出，連接A的語(yǔ)句中，用tempdb最多的時(shí)間點(diǎn)在11：39：41和11：39：42之間，連接正在做圖6里面的那條查詢語(yǔ)句。SQL Server需要空間存放一些內(nèi)部對(duì)象，來(lái)完成Inner Join。

監(jiān)測(cè)誰(shuí)用了SQL Server的Tempdb空間