問題
我讀了很多關於數據庫頁和如何利用它們來存儲表數據和索引數據的文章。有沒有一種方法可以讓我真正看到這方面的信息呢?
專家解答
在SQL Server中最糟的一個秘密是沒有正式文件說明的DBCC PAGE命令，而這個命令可以讓你查看數據和索引頁的內容。其中，某些信息仍然是神秘的，而你喜愛的搜索引擎是一個讓你開始著手查找輸出結果背後展示的很多意思的好去處。盡管如此，我發現，當我遍歷數據庫來解決數據庫問題而我只有通過頁信息來繼續摸索這個問題時，或者當我只是想看看當遇到某些數據庫操作發生時數據庫引擎如何處理數據和索引頁時，我發現DBCC命令是很有用的。
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問題的主要工具。
