作為一個支援工程師，會被經常問到象“我的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
select getdate()
select * into #mySalesOrderDetail
from Sales.SalesOrderDetail
-- 建立一個temp table
-- 這個操作應該會申請user objects page
waitfor delay '0:0:2'
drop table #mySalesOrderDetail
-- 删除一個temp table
-- 這個操作後user object page數量應該會下降
select top 100000 * from
[Sales].[SalesOrderDetail]
INNER JOIN [Sales].[SalesOrderHeader]
ON [Sales].[SalesOrderHeader] .[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID];
-- 這裡做了一個比較大的join.
-- 應該會有internal objects的申請.
-- join 語句做完以後internal objects page數目應該下降
那用什麼腳本可用監視上面的行為呢？下面的腳本就可以監視和發現目前的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
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。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)