利用DBCC PAGE檢視SQL Server中的表和索引資料

DBCC PAGE 引數DBCC PAGE
(
['database name'|database id], -- can be the actual name or id of the database
file number, -- the file number where the page is found
page number, -- the page number within the file
print option = [0|1|2|3] -- display option; each option provides differing levels of information
)

　　首先，讓我們來建立一個示例資料庫和表，這將有利於我們描述透過DBCC PAGE你看到了什麼。

　　USEMASTER　　GO　　CREATEDATABASEMSSQLTIPS　　GO　　USEMSSQLTIPS　　GO　　CREATETABLEDBO.EMPLOYEE　　(　　EMPLOYEEIDINTIDENTITY(1,1),　　FIRSTNAMEVARCHAR(50)NOTNULL,　　LASTNAMEVARCHAR(50)NOTNULL,　　DATE_HIREDDATETIMENOTNULL,　　IS_ACTIVEBITNOTNULLDEFAULT1,　　CONSTRAINTPK_EMPLOYEEPRIMARYKEY(EMPLOYEEID),　　CONSTRAINTUQ_EMPLOYEE_LASTNAMEUNIQUE(LASTNAME,FIRSTNAME)　　)　　GO　　INSERTINTODBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)　　SELECT'George','Washington','1999-03-15'　　GO　　INSERTINTODBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)　　SELECT'Benjamin','Franklin','2001-07-05'　　GO　　INSERTINTODBO.EMPLOYEE(FIRSTNAME,LASTNAME,DATE_HIRED)　　SELECT'Thomas','Jefferson','2002-11-10'　　GO

現在，我們可以去看看SQL Server如何儲存資料和索引頁。但是我們該從哪裡開始?我們能從哪裡找到這張表的頁和它的資料的所在?其實，這裡還有另外的DBCC命令 – DBCC IND – 你可以用它來列出一張表的所有資料和索引頁。

DBCC IND引數DBCC IND
(
['database name'|database id], -- the database to use
table name, -- the table name to list results
index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)

　　讓我們透過執行下面的命令列來列出EMPLOYEE表的頁結構。

　　ListdataandindexpagesallocatedtotheEMPLOYEEtable　　DBCCIND('MSSQLTIPS',EMPLOYEE,-1)　　GO

　　以下是我的資料庫所輸出的結果：

　　請注意，為了更簡潔，上述的影像只列出了執行DBCC命令後輸出的前面11列的結果。還有另外的列沒有列出來，這些列包括可以讓你看到這些頁如何彼此關聯的連結列表資訊。

　　這一次的資料意味著什麼呢?為了達到這篇文章講述的方法的目的，我們專注於一些關鍵列。列PageFID和PagePID分別代表頁所在的檔案數目和資料在檔案內的頁數目。IndexID是在sys.indexes之中找到的索引的index_id。PageType表示頁的型別，Type = 1是資料頁，Type = 2是索引頁，Type = 10是儲存頁本身的IAM頁。IndexLevel是按頁數時IAM結構的級別。如果 level = 0，那麼這是索引的葉級別頁。要想了解更多這些列的詳細資訊(畢竟這是一個沒有在正式檔案中說明的命令)，可以看看由微軟前儲存引擎專家Paul Randal寫的MSDN blog，這個部落格很詳細地解釋了這些。

有了這些資訊，現在我們可以看看我們插入的Washington, Franklin和Jefferson三行是如何儲存到EMPLOYEE表中的。EMPLOYEE表有一個聚簇索引(主鍵定義的結果)，它表示應該有一個由DBCC IND輸出結果產生的IndexID = 1(index_id = 1指向聚簇索引，在這個聚簇索引中，對於表來說葉級別頁是真正的資料)。觀察由DBCC IND產生的輸出結果，我們可以看到，這個PageType = 1聚簇索引可以在檔案數(PageFID) = 1和頁碼(PagePID) = 143的地方找到。這裡有四個不同的顯示頁資料的列印選項。我使用的是包含頁標題資訊和資料的列印選項3。

　　注意：在我們能夠執行DBCC PAGE之前，要求跟蹤標誌3604設定成指導引擎去傳送輸出結果到控制檯，否則你將什麼都看不到。

　　DBCCTRACEON(3604)　　DBCCPAGE('MSSQLTIPS',1,143,3)WITHTABLERESULTS　　GO

　　滾動到這些結果的結尾處，我們可以看到，我們的資料已經儲存了並且它儲存在聚簇索引的列上。資料行儲存在以零點偏移開始的槽變數上。

　　EMPLOYEE表也有一個非聚簇索引(透過在表中定義的約束)。讓我們檢視建立的非聚簇索引。再次觀察DBCC IND輸出，我們可以很容易確定非聚簇頁，因為它是IndexID = 2 (PageType = 2)並且它可以在檔案數(PageFID)= 1和頁碼(PagePID) = 153中找到。注意，如果我們有表上的不同索引，我們可以檢視sys.indexes並且得到隨後要用來檢視具體索引的index_id。現在，讓我們來看看索引資料：

　　DBCCPAGE('MSSQLTIPS',1,153,3)WITHTABLERESULTS　　GO

滾動到這些結果的末端，我們可以看到我們的索引資料是按姓和名的邏輯排列來儲存的。你也應該注意到，聚簇索引鍵也儲存在索引行中。當需要一個書籤查詢(bookmark lookup)時，引擎可以用它來檢索聚簇索引(這種型別的查詢發生在索引欄沒有包含需要用來滿足一個查詢的所有欄時)。

　　如果這裡沒有表上的聚簇索引，那麼另外的欄將會指向實際的資料頁。讓我們重新建立作為非聚簇索引的主鍵並且重新檢查由UNIQUE約束建立的非聚簇索引。注意，透過重新建立沒有聚簇索引的表，基本頁的資料已經改變了。透過DBCC IND，你可以看到這些頁結構如何改變。

　　ALTERTABLEDBO.EMPLOYEEDROPCONSTRAINTPK_EMPLOYEE　　GO　　ALTERTABLEDBO.EMPLOYEEADDCONSTRAINTPK_EMPLOYEE　　PRIMARYKEYNONCLUSTERED(EMPLOYEEID)　　GO　　DBCCIND('MSSQLTIPS',EMPLOYEE,-1)　　DBCCPAGE('MSSQLTIPS',1,155,3)WITHTABLERESULTS　　GO

　　正如你所看到的，在HEAP表中的索引(這張表不是聚簇的)儲存了一個不同的指示器，這個指示器直接指向包含要求的另外的資料的頁面。

　　這些都是很簡單的例子，但是它們對給你關於如何和在哪裡獲得和顯示數表和索引資料的想法是綽綽有餘的。在以後的方法中，我將利用更多的例子來說明問題，這些例子將顯示當一行改變並且它不適合某一頁時將會發生什麼，當行被刪除時將發生什麼，還有DBCC PAGE如何幫助解決阻塞和死鎖問題。

　　我必須強調，DBCC IND和DBCC PAGE沒有在正式檔案中說明，它們可能在以後的SQL Server版本中會消失。在那出現之前，我會繼續利用這些命令作為窺探引擎的資料儲存技術和解決SQL Server問題的主要工具。