SQL Server頁類型匯總+疑問匯總_MsSql

SQL Server中包含多種不同類型的頁，來滿足資料存放區的需求。不管是什麼類型的頁，它們的儲存結構都是相同的。每個資料檔案都包含相當數量的由8KB組成的頁，即每頁有8192bytes可用，每頁都有96byte用於頁頭的儲存，剩下的空間

才用來儲存實際的資料，在頁的最後是資料行位移數組，也可以叫“頁槽”數組，我們可以把一個頁看做是有一個個方格的書櫥，哪行資料佔用了哪個槽，都在頁尾的位置進行標示，並且頁尾數組的寫入順序是倒敘的，這樣就可以有效利用頁空間。

由此可以預見，頁面上的“槽”並不一定是有序存放的，當有新的ID進來，並且該ID位於該頁的最大ID和最小ID之間時（假設是以ID進行排序的葉子頁），那麼該ID資料行則直接插入到已經存在的資料行的後面即可，當有查詢需要檢索該ID所在的行時，

資料庫引擎從索引頁找到該“葉子”頁，將該頁全部載入到記憶體中，通過頁尾的行位移數組找到對應的行。頁尾數組的記錄大小儲存在頁頭裡，數組裡面每一個關於“頁槽”的記錄佔用空間為2bytes。

據我所知，SQL Server資料檔案共有14種頁類型：

類型1——資料頁(Data Page)：堆中的資料頁叢集索引中的“葉子”頁在資料檔案中的位置是隨機的DBCC PAGE 中m_type=1

類型2——索引頁(Index Page)：

非叢集索引非“葉子”級叢集索引在資料檔案中的位置是隨機的DBCC PAGE 中m_type=2

類型3——文本混合頁(Text Mixed Page)：

較短長度的LOB資料類型，多種類型，多行儲存在資料檔案中的位置是隨機的DBCC PAGE 中m_type=3

類型4——文本頁(Text Tree Page)：

儲存單個LOB行在資料檔案中的位置是隨機的DBCC PAGE 中m_type=4

類型5——排序頁(Sort Page)：

進行排序操作時的臨時頁常見於TempDB中，在使用者資料中進行“ONLINE"操作時也可見(例如：聯機建立索引未指定SORT_IN_TEMPDB選項時)在資料檔案中的位置是隨機的DBCC PAGE 中m_type=19

類型6——全域分配映射頁(GAM Page)：

Global Allocation Map，記錄已指派的非共用（混合）區是否已被使用每個區佔用一個bit位，如果該值為1，說明該區可以使用，0則說明已被使用（但是並不一定儲存空間已滿）第一個GAM頁總是儲存在每個資料檔案PageID為2的頁上DBCC PAGE 中m_type=8

類型7——共用全域分配映射頁(SGAM Page)：

Shared Global Allocation Map，記錄每一個共用（混合）區是否已被使用每個區佔用一個bit位，如果該值為1，說明該區有閒置儲存空間，0則說明區已滿第一個SGAM頁總是儲存在每個資料檔案PageID為3的頁上DBCC PAGE 中m_type=9

類型8——索引配置對應頁(IAM Page)：

Index Allocation Map，記錄GAM頁之間堆表或者索引的區分配在資料檔案中的位置是隨機的DBCC PAGE 中m_type=10

類型9——空閑空間跟蹤頁(PFS Page)：

Page Free Space，跟蹤頁的可用空間。
第一個PFS頁總是儲存在每個資料檔案PageID為1的頁上DBCC PAGE 中m_type=11

類型10——啟動頁(Boot Page)：

儲存所在資料庫範圍的資訊僅在每個資料庫檔案(file)ID為1的PageID為9的頁上DBCC PAGE 中m_type=13

類型11——服務配置頁(Server Configuration Page)：

儲存了sys.configurations中返回結果中的部分資訊該頁僅存在於master資料庫的檔案ID為1PageID為10的頁上

類型12——檔案頭頁(File Header Page)：

所在檔案的資訊總是存在於每個檔案PageID為0的頁上DBCC PAGE 中m_type=15

類型13——差異更改映射(Differential Changed map)：

記錄GAM之間的每次全備或差異備份之後更改過的頁面第一個DCM頁面在每個資料檔案PageID為6的頁上DBCC PAGE 中m_type=16

類型14——大容量更改映射(Bulk Change Map)：

記錄每個GAM之間上次備份之後大容量操作的更改第一個BCM頁面在每個資料檔案PageID為7的頁上DBCC PAGE 中m_type=17

如下SQL可以查詢到你當前的資料庫中的緩衝的頁類型及數量：

SELECT CASE page_type WHEN 'DIFF_MAP_PAGE' THEN '差異更改映射(Differential Changed map)' WHEN 'TEXT_MIX_PAGE' THEN '文本混合頁(Text Mixed Page)' WHEN 'ML_MAP_PAGE' THEN '這個字面意思應該是Minimally-Logged,最小化日誌記錄' WHEN 'INDEX_PAGE' THEN '索引頁(Index Page)' WHEN 'FILEHEADER_PAGE' THEN '檔案頭頁(File Header Page)' WHEN 'DATA_PAGE' THEN '資料頁(Data Page)' WHEN 'IAM_PAGE' THEN '索引配置對應頁(IAM Page)' WHEN 'GAM_PAGE' THEN '全域分配映射頁(GAM Page)' WHEN 'BULK_OPERATION_PAGE' THEN '這個字面意思應該是大容量更改記錄' WHEN 'TEXT_TREE_PAGE' THEN '文本頁(Text Tree Page)' WHEN 'SGAM_PAGE' THEN '共用全域分配映射頁(SGAM Page)' WHEN 'PFS_PAGE' THEN '空閑空間跟蹤頁(PFS Page)' WHEN 'BOOT_PAGE' THEN '啟動頁(Boot Page)' ELSE '排序頁?' END , page_type , COUNT(*) cntFROM sys.dm_os_buffer_descriptors WITH ( NOLOCK )WHERE database_id = DB_ID()GROUP BY page_type

結果如下圖所示：

 

按上面的資料類型介紹，我們很自然地認為類型14——大容量更改映射(Bulk Change Map)就是圖示查詢結果中第10行BULK_OPERATION_PAGE

但是事實是嗎？我們將data_type=BULK_OPERATION_PAGE的記錄查出來：

SELECT TOP 10 *FROM sys.dm_os_buffer_descriptors WHERE page_type='BULK_OPERATION_PAGE' AND DB_ID()=database_id
ORDER BY database_id,FILE_ID,page_id

查詢結果：

我們把查詢結果中的一個PageID帶入DBCC PAGE（其實這裡已經看出，這個pageID並不像上面說的第一個BCM頁面在每個資料檔案PageID為7的頁上，它們是邏輯上連續的頁！

我們發現上面的m_type=20！

我搜遍了google也沒有找到m_type=20是什麼記錄！

參考網址：http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx

但是我們可以查到如下資訊：

m_type=17的這個資料類型ML map page，是在“大容量日誌”模式下，記錄自上次備份以來哪些區被更改過，該頁第一個位置總是在每個檔案的第7頁上，我們折回上面第一個查詢時的第三行，即PageType是ML_MAP_PAGE的那行，

並將其帶入如下SQL查詢出pageID的記錄：

發現這才是傳說中的那個第一頁總是出現在每個檔案第7頁的混蛋！

我們將PageID7帶入DBCC PAGE:

Oh,SHIT!這個的m_type是17！

好吧，我只能說，是我曲解了人家字面的意思，原來：

BCM ，大容量更改映射(Bulk Change Map)，在資料庫緩衝中對應的PageType竟然是ML_MAP_PAGE！Minimally-Logged Page！

而那個該死的BULK_OPERATION_PAGE(m_type=20)是什麼東西，誰能告訴我？

另外那個UNLINKED_REORG_PAGE，應該就是排序頁吧？