1.1 文件的分配方式及文件空間檢查方法

談到檢查數據庫空間大小，很多讀者會想到一條命令：sp_spaceused。它顯示行數、保留的磁盤空間以及當前數據庫中的表、索引視圖等數據庫對象所使用的磁盤空間，或顯示由整個數據庫保留和使用的磁盤空間。語法定義是：

    sp_spaceused [[ @objname = ] 'objname' ]
    [,[ @updateusage = ] 'updateusage' ]

[ @objname =] 'objname'：請求分析其空間使用信息的表、索引視圖或隊列的名稱。如果未指定objname，則返回整個數據庫的結果。

所以，如果不帶入任何參數，這條語句返回的是當前數據庫的使用空間信息。如果我們在范例數據庫AdventureWorks上面運行，返回的結果可以是：

    database_name     database_size      unallocated space
    ---------------- ------------------ ------------------
    AdventureWorks    178.75 MB          15.41 MB
    reserved          data               index_size        unused
    ---------------- ------------------ ---------------- ------------------
    165216KB         83656KB           76784KB          4776KB

可是這個結果并不能使人非常滿意。首先，從這個結果中，無法直觀地看出每個數據文件和日志文件的使用情況。其次，這個存儲過程是依賴于SQL Server存儲在一些系統視圖里的空間使用統計信息來算出結果的。但是，SQL Server并不保證實時更新空間使用統計信息。尤其是數據庫剛剛發生大的變化之后，sp_spaceused的結果常常不準確。另外這個系統存儲過程的算法主要針對的是普通用戶數據庫。（如果有興趣的話，讀者可以運行“sp_helptext sp_spaceused”得到存儲過程的定義，研究一下它的算法。）對于tempdb數據庫里存儲的一些系統臨時數據對象，是無法用這個存儲過程來統計的。所以，會出現tempdb報告空間用盡，而sp_spaceused卻顯示tempdb還有很多未使用的空間的現象。所以這個結果很有局限性，不是非常可靠。

那么怎么查看才能總是得到準確結果呢？這個要按不同的文件類型來考慮。SQL Server的文件分成數據文件（.mdf，.ndf）和日志文件（.ldf）兩種。對于系統數據庫tempdb，SQL Server的使用又和其他數據庫有所不同。針對這些不同的對象，SQL Server提供了不同的手段來做空間使用分析。下面分別介紹。

1.1.1 數據文件分配

在介紹數據文件空間使用之前，我們先來討論一些關于數據庫空間組織架構的基本概念。了解這些概念，能夠幫助我們理解數據庫的空間分配。

SQL Server數據庫有兩種類型的數據文件。

1.主數據文件。

主數據文件是數據庫的起點，存儲指向數據庫中的其他文件路徑。每個數據庫都有一個主數據文件。主數據文件的推薦文件擴展名是.mdf。

2.輔助數據文件。

除主數據文件以外的所有其他數據文件都是輔助數據文件。某些數據庫可能不含有任何輔助數據文件，而有些數據庫則含有多個輔助數據文件。輔助數據文件的推薦文件擴展名是.ndf。

SQL Server文件又有兩種名字。

logical_file_name（邏輯文件名）

logical_file_name是在所有Transact-SQL語句中引用物理文件時所使用的名稱。也就是文件在SQL Server里的名字。邏輯文件名必須符合SQL Server標識符規則，而且在數據庫中的邏輯文件名中必須是唯一的。

os_file_name（物理文件名）

os_file_name是包括目錄路徑的物理文件名。也就是文件在操作系統里的名字和路徑。它必須符合操作系統文件命名規則。

圖1-1顯示了在默認SQL Server 2005實例上創建的數據庫的邏輯文件名和物理文件名示例。MyDB_Primary這樣的名字，就是邏輯文件名。而以“c:\”開頭的這些名字，是物理文件名。

圖1-1 數據庫的邏輯文件名和物理文件名

用戶插入數據庫里的那些數據記錄在數據文件里是怎么存放的呢？這就要涉及頁（Page）和區（Extent）這兩個概念了。SQL Server中數據存儲的基本單位是頁。為數據庫中的數據文件（.mdf或.ndf）分配的磁盤空間可以從邏輯上劃分成頁（從0到n連續編號）。磁盤I/O操作在頁級執行。也就是說，SQL Server讀取或寫入數據的最小單位就是以8KB為單位的頁。

區是8個物理上連續的頁的集合，用來有效地管理頁。所有頁都存儲在區中。

頁

在SQL Server中，頁的大小為8KB。這意味著SQL Server數據庫中每MB有128頁。每頁的開頭是一個96 B的頁頭，用于存儲有關頁的系統信息。包括頁碼、頁類型、頁的可用空間，以及擁有該頁的對象的分配單元ID。不同類型的數據，存儲在不同類型的頁面里。

表1-1說明了SQL Server數據庫的數據文件中的各種頁類型，以及它們里面存儲的數據類型。

表1-1 數據文件中的頁面類型

在一個數據庫里，絕大多數頁面都是Data或者Text/Image類型。而在一個數據文件的開頭，則分布了像Global Allocation Map、Shared Global Allocation Map、Page Free Space這樣的管理頁面。SQL Server通過這些頁面知道這個數據文件中哪些頁面已經被使用，哪些頁面還沒有被使用。所以這些頁面數量雖少，但卻是將數據庫頁面串聯起來的至關重要的頁面。

在正常數據頁上，數據行緊接著頁的標頭按順序放置。頁的末尾是行偏移量表，對于頁中的每一行，每個行偏移表都包含一個條目。每個條目記錄對應行的第一個字節與頁首的距離。行偏移表中的條目的順序與頁中行的順序相反（見圖1-2）。

圖1-2 數據頁結構

讀到這里，可能讀者會有點好奇，想看看一個頁面到底是長什么樣子的。SQL Server有一個DBCC PAGE命令可以按一定格式翻譯輸出指定的頁面。（在不同的SQL Server版本里，它的輸出格式可能稍有不同。）命令的格式是：

    DBCC PAGE(<db_id>, <file_id>, <page_id>, <format_id>)

比如要看AdventureWorks這個數據庫里面的AdventureWorks_Data數據文件里的第3 230頁。那就要先得到db_id和file_id。

Db_id可以從sp_helpdb的結果中得到。這里是5（見圖1-3）。

圖1-3 sp_helpdb結果

File_id可以從sp_helpfile的結果中得到。這里是1（見圖1-4）。

圖1-4 sp_helpfile結果

Format_id是你指定的輸出格式，有1、2、3三個值。一般來講，3這個輸出格式比較直觀。在運行DBCC PAGE之前，還需要打開跟蹤標志（trace flag）3604。

運行這條命令能夠顯示這個頁面上有些什么內容。先看看Messages里面的文本輸出（見圖1-5）。

圖1-5 DBCC PAGE文本輸出

這里面有很多有意思的信息。比如:

    Metadata: ObjectId = 414624520
    Metadata: IndexId = 2

它告訴我們，這個頁面屬于414624520這個對象，ID為2的索引（index）。運行下面的語句可以看到它是什么（見圖1-6）。

圖1-6 根據ObjectId和IndexId找到表格和索引

原來它是[Production].[WorkOrder]的索引IX_WorkOrder_ScrapReasionID。這個索引建立在ScrapReasonID這個列上。

我們再來看看DBCC PAGE的網格輸出（見圖1-7）。

圖1-7 DBCC PAGE網格輸出

這里顯示出3230這個頁面是索引IX_WorkOrder_ScrapReasionID在第一層子樹上面的一個頁面。它里面包含了索引列（Index Column）（ScrapReasonID）的值。由于它是一個非聚集索引，它還存儲了指向聚集索引的指針（WorkOrderID）。

這里輸出的內容經過了DBCC PAGE的翻譯。如果想看頁面上原有的內容，可以選用2這個參數。當然得到的結果就不這么容易懂了（見圖1-8）。

圖1-8 DBCC PAGE的另一種輸出

大型行支持

在SQL Server中，行不能跨頁，屬于同一行的所有字段的數據都要放在同一個頁面里。頁的最大數據量是8 060 B（8KB）。所以一般數據類型字段所組成的一行，最長加起來不能超過8KB。

但是，這一限制不包括Text/Image類型字段的數據。這些類型字段的數據會被單獨存放在LOB（Large Object）頁面里。

在SQL Server 2005中更進了一步，提供了varchar(max)、nvarchar(max)、varbinary(max)這樣的數據類型，使得varchar、nvarchar、varbinary這樣傳統的數據類型字段，也可以像Text/Image字段一樣突破8KB的限制，方便了用戶的使用。如果包含這一類數據類型字段的行，總長不超過8KB，數據還是會一起存放在普通數據頁面里。如果總長超過了8KB，SQL Server就會把這些字段的數據分開，單獨存放在一種叫Row-Overflow（行溢出）的頁面里。

區

區是管理空間的基本單位。一個區是8個物理上連續的頁（即64KB）。這意味著SQL Server數據庫中每MB有16個區。

為了使空間分配更有效，SQL Server不會將所有區分配給包含少量數據的表。SQL Server有兩種類型的區：

● 統一區，由單個對象所有。區中的所有8頁只能由一個對象使用。

● 混合區，最多可由8個對象共享。區中8頁的每頁可由不同的對象所有。但是一頁總是只能屬于一個對象。

通常從混合區向新表或索引分配頁。當表或索引增長到8頁時，將變成使用統一區進行后續分配。如果對現有表創建索引，并且該表包含的行足以在索引中生成8頁，則對該索引的所有分配都使用統一區進行（見圖1-9）。

圖1-9 統一區和混合區

所以說，表格和索引所有第8個頁面以后的頁面都會分布在統一區內。在這種區里，所有的頁面都屬于同一個對象。這也意味著，在一個大的數據庫里，絕大多數區都會是統一區。一個區里面的所有頁面都屬于一個表或索引。統計區的數目，大致就能知道表格或者索引所占的空間數目。

1.1.2 數據文件空間使用的計算方法

了解一個數據庫空間使用的最簡單方法，就是在Management Studio里，右鍵點擊數據庫名字，選擇“Reports”-“Standard Reports”，默認就有4個Disk Usage的報表。它們能很好地統計出從不同角度分析的數據庫空間使用情況（見圖1-10）。

圖1-10 Management Studio的Disk Usage報表

細心的讀者可能會發現，這里的結果和sp_spaceused給出的稍有不同。嚴格來講，這和統計的單位有關系。比如是準備按照區為單位進行統計呢，還是按照頁面為單位進行統計?由于每個區有8個頁面，這8個頁面不一定都被使用到了。那些沒有使用到的頁面到底算不算呢？如果按照區來統計，這8個頁面就都算是使用了的空間。如果按照頁面來統計，沒有使用的頁面就可以不算。所以這兩種統計的方法得到的值會稍有不同。

按照區統計

SQL Server有一個DBCC命令按照區的使用情況統計數據文件的使用大小。Management Studio的報表在這里就調用了這個命令。DBCC語句運行的結果如圖1-11所示。

    DBCC SHOWFILESTATS
    GO

圖1-11 DBCC SHOWFILESTATS結果

這個命令能直接從GAM和SGAM這樣的系統分配頁面上面讀取區分配信息，直接算出數據庫文件里有多少區已被分配。它的優點是能夠快速準確地計算出一個數據庫數據文件區的總數和已使用過的區的數目。由于SQL Server在絕大多數時間都是按照區為單位來分配新空間的，而系統分配頁上的信息永遠是實時更新的，所以這種統計方法比較準確可靠。在服務器負載很高的情況下也能安全執行，不會增加額外系統負擔。所以要考察數據庫數據文件級的使用情況，它是個比較好的選擇。

按照頁面統計

如果想要知道某個具體的表格或者索引使用了多少空間，就要從頁面這個級別來分析。在這里，可以選擇sp_spaceused或者DBCC SHOWCONTIG（SQL Server 2005中可以使用sys.dm_db_index_physical_stats）中的一個。這兩種方法，各有不同。

Sp_spaceused是根據sys.allocation_units和sys.partitions這兩張管理視圖來計算存儲空間的。有時候（例如刪除索引后、表的空間信息不是當前信息時），這兩張表可能不能及時反映出數據庫的準確信息。可以加入updateusage這個參數，要求SQL Server為這句指令更新管理視圖里的統計信息。可是，這樣做對一個比較大的數據庫來講，有可能是一件消耗資源的工作。當SQL Server非常繁忙的時候，最好不要用這種任務來打攪它。

Sp_spaceused的另一個缺點是一次只能查詢一個對象。我們無法一次對數據庫內所有的對象進行統計。然而可以通過直接查詢sys.dm_db_partition_stats以及相關的管理視圖來達到這個目標。下面就是一個例子（結果如圖1-12所示）：

    SELECT o.name ,
    SUM (p.reserved_page_count) AS reserved_page_count,
    SUM (p.used_page_count) AS used_page_count,
    SUM (
    CASE
    WHEN (p.index_id < 2) THEN (p.in_row_data_page_count +
    p.lob_used_page_count + p.row_overflow_used_page_count)
    ELSE p.lob_used_page_count + p.row_overflow_used_page_count
    END
    ) AS DataPages,
    SUM (
    CASE
    WHEN (p.index_id < 2) THEN row_count
    ELSE 0
    END
    ) AS rowCounts
    FROM sys.dm_db_partition_stats p INNER JOIN sys.objects o
    ON p.object_id = o.object_id
    GROUP BY o.name

圖1-12 sys.dm_db_partition_stats查詢結果

總之，sp_spaceused是一種使用簡單，但是功能比較弱，也不是最準確的方法。不特別推薦用sp_spaceused來檢查數據庫空間使用情況。查系統管理視圖sys.dm_db_partition_stats會來得更直接一些。

DBCC SHOWCONTIG（或者sys.dm_db_index_physical_stats）是檢查數據空間分配情況的另一種方法，也可以說是最精確的方法。它可以告訴你某張表（或索引）用了多少頁面、多少區，甚至頁面上的平均數據量。從這些值可以算出一張表格占用了多少空間。然而，得到這些精確的數據也是要付出代價的。SQL Server從整體性能的角度出發，不可能一直維護這樣底層的統計信息。為了完成這個命令，SQL Server必須要對數據庫進行掃描。而掃描的過程中，SQL Server是要加鎖的。例如sys.dm_db_index_physical_stats，它就有3種掃描模式。要得到的結果越精確，掃描的范圍就越大。

● LIMITED模式運行最快，掃描的頁數最少。對于堆，它將掃描所有頁，但對于索引，則只掃描葉級上面的父級別頁。

● SAMPLED模式將返回基于索引或堆中所有頁的1％樣本的統計信息。如果索引或堆少于10 000頁，則使用DETAILED模式代替SAMPLED。

● DETAILED模式將掃描所有頁并返回所有統計信息。

所以說，這種方式雖然精確，但是在數據庫處于工作高峰時應避免使用。

小結

在這一節里，一共介紹了5種分析數據文件存儲空間的方法。表1-2比較了它們的優缺點和使用特點。

表1-2 5種數據文件存儲空間分析方法比較

如果管理者只要看數據文件的整體使用情況，DBCC SHOWFILESTATS是比較好的選擇。如果要看每個對象的空間使用情況，可以使用動態管理視圖sys.dm_db_partition_stats。如果還想了解每個頁、每個區的使用情況、碎片程度，那DBCC SHOWCONTIG是比較好的選擇。

1.1.3 日志文件

數據庫的另一種文件類型是日志文件。日志文件包含用于恢復數據庫的所有日志信息。每個數據庫必須至少有一個日志文件，當然也可以有多個。日志文件的推薦文件擴展名是.ldf。與數據文件按8KB組織不同，日志文件不是按頁/區來組織的。

SQL Server數據庫引擎在內部將每一物理日志文件分成多個虛擬日志單元。虛擬日志單元沒有固定大小，且一個物理日志文件所包含的虛擬日志單元數不固定。管理員不能配置或設置虛擬日志單元的大小或數量。但SQL Server會嘗試控制虛擬日志單元的數目，把它限制在一個合理的范圍里。例外情況是，日志文件每自動增長一次，會至少增加一個虛擬日志單元。所以，如果一個日志文件經歷了多次小的自動增長，里面的虛擬日志單元數目會比正常的日志文件多很多。這種情況會影響到日志文件管理的效率，甚至造成數據庫啟動要花很長時間。

事務日志是一種回繞的文件。例如，假設有一個數據庫，它包含一個分成5個虛擬日志單元的物理日志文件。當創建數據庫時，邏輯日志文件從物理日志文件的始端開始。新日志記錄被添加到邏輯日志的末端，然后向物理日志的末端擴張。事務日志的外觀與圖1-13所示相似。

圖1-13 事務日志的外觀

當邏輯日志的末端到達物理日志文件的末端時，新的日志記錄將回繞到物理日志文件的始端，繼續向后寫（見圖1-14）。

圖1-14 事務日志循環使用

查看日志文件的使用情況非常簡單。當然，不能用sp_spaceused。要用如下代碼，結果如圖1-15所示。

    DBCC SQLPERF(LOGSPACE)

圖1-15 DBCC SQLPERF(LOGSPACE)結果

圖1-15清楚地顯示了SQL Server上所有數據庫的日志大小，以及使用比率。語句的執行不會對SQL Server帶來負擔，這個語句返回的結果總是準確的。可以隨時在SQL Server上運行這句話。

1.1.4 Tempdb

Tempdb系統數據庫是一個全局資源，可供連接到SQL Server實例的所有用戶使用，在SQL Server 2005中，用于保存下列各項：

用戶對象

用戶對象由用戶顯式創建。這些對象可以位于用戶會話的作用域中，也可位于創建對象所用例程的作用域中。例程可以是存儲過程、觸發器或用戶定義函數。用戶對象可以是下列項之一：

● 用戶定義的表和索引。

● 系統表和索引。

● 全局臨時表和索引。

● 局部臨時表和索引。

● table變量。

● 表值函數中返回的表。

內部對象

內部對象是根據需要由SQL Server數據庫引擎創建的，用于處理SQL Server語句。內部對象可以在語句的作用域中創建和刪除。內部對象可以是下列項之一：

● 用于游標或假脫機操作以及臨時大型對象（LOB）存儲的工作表。

● 用于哈希聯接或哈希聚合操作的工作文件。

● 用于創建或重新生成索引等操作（如果指定了SORT_IN_TEMPDB）的中間排序結果，或者某些GROUP BY、ORDER BY或UNION查詢的中間排序結果。

每個內部對象至少使用9頁：一個IAM頁，一個8頁的區。

版本存儲區

版本存儲區是數據頁的集合，它包含支持使用行版本控制的功能所需的數據行。主要用來支持快照（Snapshot）事務隔離級別，以及SQL Server 2005推出的一些其他提高數據庫并發度的新功能。在SQL Server 2005中，有兩個版本存儲區：公用版本存儲區和聯機索引生成版本存儲區。版本存儲區包含下列項：

● 由使用快照隔離級別或已提交讀隔離級別（基于行版本控制）的數據庫中的數據修改事務生成的行版本。

● 由數據修改事務為實現聯機索引操作、多個活動的結果集（MARS）以及AFTER觸發器等功能而生成的行版本。

在SQL Server 2005以后，tempdb需要的磁盤空間會多于早期版本。因此當升級到SQL Server 2005或SQL Server 2008后，管理員提供的tempdb磁盤空間可能得比以前要大一些，用以容納當前生產工作負荷并滿足使用tempdb的SQL Server功能的額外空間要求。

Tempdb空間使用跟蹤

Tempdb空間使用的一大特點，是只有一部分對象，例如用戶創建的臨時表、table變量等，可以用sys.allocation_units和sys.partitions這樣的管理視圖來管理。許多內部對象和版本存儲在這些管理視圖里沒有體現。所以，sp_spaceused的結果和真實使用情況會有很大差異，tempdb的空間使用是不能用sp_spaceused來跟蹤的。

DBCC SHOWFILESTATS當然還是可以反映數據文件使用的整體情況的。但是這些空間是被誰以什么樣的方式用掉的呢？在SQL Server 2000的時候，這是個很難回答的問題。

SQL Server 2005后，引入了一張新的管理視圖：sys.dm_db_file_space_usage（見表1-3）。這張視圖能反映tempdb在如下幾個大類里的空間使用分布。

表1-3 sys.dm_db_file_space_usage字段列

通過對這個表的監視，就能知道tempdb的空間是被哪一塊對象使用掉的，是用戶對象（user_object_reserved_page_count），還是系統對象（internal_object_reserved_page_count），還是版本存儲區（version_store_reserved_page_count）。在1.1.5節的案例分析里，會對這種方法有所展示。

另一個問題是tempdb初始大小設多少比較好呢？tempdb在生產環境中的適當大小取決于多種因素，所以這個問題無法給出固定的答案。如本主題中前面所述，這些因素包括現有工作負荷以及使用的SQL Server功能。所以每個SQL Server都會不一樣。同一個SQL Server，如果新加入了某個會使用tempdb的功能，其空間使用也會發生變化。建議通過在SQL Server測試環境中執行下列任務來分析現有的工作負荷：

（1）設置tempdb的自動增長。

（2）模擬各個單獨的查詢或工作任務，同時監視tempdb空間使用。

（3）模擬執行一些系統維護操作，例如，重新生成索引，同時監視tempdb空間。

（4）使用前面2和3步中tempdb空間使用值來預測總的工作負荷下，會使用多少空間；并針對計劃的并發度調整此值。例如，如果一個任務會使用10 GB的tempdb空間，而在生產環境里，最多可能會有4個這樣的任務同時運行，那就要至少預留40 GB的空間。

（5）根據第4步得到的值，設置tempdb在生產環境下的初始大小。同時也開啟自動增長。

Tempdb文件個數以及大小設置，不但要滿足用戶任務的需求，還要考慮到性能優化。在11.4節里，會有進一步的討論。

1.1.5 案例：通過腳本監視tempdb空間使用

在實際使用中，有時候會遇到tempdb數據庫在很短一段時間里，莫名其妙地增長了很多的情況。數據庫管理員要能解釋，到底是什么樣的操作導致了SQL Server要這樣大規模地使用tempdb。是一種正常的行為，還是某種異常。

監視SQL Server的行為當然可以借助于SQL Trace來完成。可是管理員并不能預期造成大量使用tempdb的語句會在什么時候運行。這些語句可能一周只做一次。而且開跟蹤畢竟對SQL Server來講是個比較昂貴的動作。（參見第14章的討論）就算沒有性能副作用，如果一直開著SQL Trace，也會產生很大量的跟蹤文件，對硬盤是個比較重的負擔。

那怎么能夠比較“輕量級”地對SQL Server進行監視呢？比較經濟的方法，是以一定的間隔時間運行能夠監視系統運行狀況的DBCC命令、查詢管理視圖（DMV）以及管理函數（DMF）等，把結果輸出到一個文件里。這樣的監視方法相對安全。大部分時候信息量也足夠進行問題定位。所以在一個很繁忙的SQL Server系統上，這是個不錯的選擇。

下面以一個實例，討論一下如何用DBCC命令、管理視圖（DMV）以及管理函數（DMF）來監視是什么語句使用了tempdb。

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

然后我們在Management Studio里做一個連接（連接A），將下面語句輸入。

    select @@spid
    go
    use adventureworks
    go
    select getdate()
    go
    select * into #mySalesOrderDetail
    from Sales.SalesOrderDetail
    -- 創建一個臨時表
    -- 這個操作應該會申請用戶對象頁面
    go
    waitfor delay '0:0:2'
    select getdate()
    go
    drop table #mySalesOrderDetail
    -- 刪除一個臨時表
    -- 這個操作后用戶對象頁面數量應該會下降
    g
    o
    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];
    -- 這里做了一個比較大的聯接
    -- 應該會有系統對象的申請.
    go
    select getdate()
    -- join 語句做完以后系統對象頁面數目應該下降
    go

那用什么腳本可以監視上面的行為呢？下面是一個例子。讀者當然可以根據自己的喜好，修改這個腳本。

    use tempdb
    -- 每隔1秒鐘運行一次，直到用戶手工終止腳本運行
    while 1=1
    begin
    select getdate()
    -- 從文件級看tempdb使用情況
    dbcc showfilestats
    -- Query 1
    -- 返回所有做過空間申請的會話信息
    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 ,
    -- 反映每個會話累計空間申請
    sys.dm_exec_sessions as t3
    -- 每個會話的信息
    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
    -- 返回正在運行并且做過空間申請的會話正在運行的語句
    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

在運行這個腳本的連接（連接B）里（見圖1-16），我們選擇好“Results to File”。先開始運行它，指定輸出文件路徑。然后，我們再運行連接A（見圖1-17）。連接A運行結束后，手工停止連接B的運行。

圖1-16 檢測tempdb使用的腳本（連接B）

圖1-17 測試腳本輸出（連接A）

在連接A的結果中（見圖1-17），可以得到4個時間。圖片上的例子，是：

11:39:36.513—開始創建temp table

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

11:39:40.937—開始查詢

11:39:45.733—查詢結束

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

從連接B生成的文本文件里所有DBCC SHOWFILESTATS的結果（見圖1-18），可以看出tempdb的使用空間有過兩次增長（從23到210，從47到118），中間有一次下降（從210到47）。

圖1-18 Tempdb數據文件使用變化

從連接B生成的文本文件里所有Query 1的結果（見圖1-19），我們可以看到有3段時間，用戶對象和系統對象空間有申請和釋放動作。它們分別是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增長）。

圖1-19 Tempdb使用量隨時間變化

從Query 2的結果（見圖1-20）可以看到連接A在這3個時間段都處于運行狀態。

圖1-20 連接A在這3個時間段都處于運行狀態

根據時間，可以從Query 3的結果（見圖1-21）里找到連接A當時正在運行的語句。例如在11:39:40-11:39:43（internal_objects_kb增長）這段時間里，一直都在運行下面這句話：

圖1-21 連接A當時正在運行的語句

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

讀者可以自己試試，用類似的方法監視自己的SQL Server。