前言

在SQL Server中，如何找到一張表或某個(gè)索引擁有那些頁(yè)面（page）呢？ 有時(shí)候，我們?cè)诜治龊脱芯浚ɡ?，死鎖分析）的時(shí)候還真有這樣的需求，那么如何做呢？ SQL Server 2012提供了一個(gè)無(wú)文檔的DMF（sys.dm_db_database_page_allocations）可以實(shí)現(xiàn)我們的需求，sys.dm_db_database_page_allocations有下面幾個(gè)參數(shù)： 

•  @DatabaseId:    數(shù)據(jù)庫(kù)的ID，可以用DB_ID()函數(shù)獲取某個(gè)數(shù)據(jù)庫(kù)或當(dāng)前數(shù)據(jù)庫(kù)的ID
•  @TableId:     表的ID。 我們可以使用OBJECT_ID（）函數(shù)通過表名獲取表ID。 這是一個(gè)可選參數(shù)，如果將其作為NULL傳遞，則返回與數(shù)據(jù)庫(kù)中所有表的關(guān)聯(lián)頁(yè)面，當(dāng)它為NULL時(shí)，將忽略接下來的兩個(gè)參數(shù)（即@IndexId和@PartionId）值
• @IndexId:     索引的索引ID。 我們可以使用sys.indexes目錄視圖來獲取索引ID。 它是一個(gè)可選參數(shù)，如果將其作為NULL傳遞，則返回所有索引關(guān)聯(lián)的頁(yè)面。
• @PartitionId: 分區(qū)的ID，它是一個(gè)可選參數(shù)，如果將其作為NULL傳遞，則返回與所有分區(qū)關(guān)聯(lián)的頁(yè)面.
• @Mode:       這是必填參數(shù)，有“LIMITED”或“DETAILED”兩個(gè)參數(shù)。 “LIMITED”返回的信息較少。 “DETAILED”會(huì)返回詳細(xì)/更多信息。顯然，“DETAILED”模式會(huì)占用更多資源。

對(duì)于大表而言，如果選擇“DETAILED”參數(shù)，則消耗的資源和時(shí)間非常長(zhǎng)，這個(gè)時(shí)候非常有必要選擇“LIMITED”參數(shù)。

為了更好的理解sys.dm_db_database_page_allocations輸出的數(shù)據(jù)，其實(shí)我們有必要簡(jiǎn)單了解、回顧一下SQL Server中數(shù)據(jù)存儲(chǔ)的相關(guān)知識(shí)點(diǎn)。 這就涉及到頁(yè)（Page）和區(qū)（Extent）的概念了。SQL Server中數(shù)據(jù)存儲(chǔ)的基本單位是頁(yè)，磁盤I/O操作在頁(yè)級(jí)執(zhí)行。也就是說，SQL Server讀取或?qū)懭霐?shù)據(jù)的最小單位就是以8 KB為單位的頁(yè)。

區(qū)是管理空間的基本單位。 一個(gè)區(qū)是8個(gè)物理上連續(xù)的頁(yè)的集合（64KB），所有頁(yè)都存儲(chǔ)在區(qū)中。區(qū)用來有效地管理頁(yè)所有頁(yè)都存儲(chǔ)在區(qū)中。 SQL Server中有兩種類型的區(qū)： 

• 統(tǒng)一區(qū)： 由單個(gè)對(duì)象所有。區(qū)中的所有8頁(yè)只能有一個(gè)對(duì)象使用。
• 混合區(qū)： 最多可由8個(gè)對(duì)象共享。區(qū)中8頁(yè)中每一頁(yè)都可由不同的對(duì)象所有。但是一頁(yè)總是只能屬于一個(gè)對(duì)象。

SQL Server中頁(yè)也有很多類型，具體參考下面表格。

注意事項(xiàng)：有些Page Type比較少見，暫時(shí)有些資料沒有補(bǔ)充完善

另外，關(guān)于sys.dm_db_database_page_allocations的輸出字段信息如下所示（搜索相關(guān)資料結(jié)合自己的理解，如果錯(cuò)誤，敬請(qǐng)指出）：

簡(jiǎn)單了解了上面知識(shí)點(diǎn)后，我們?cè)谑褂眠@個(gè)DMF找出表或索引相關(guān)的頁(yè)面，基本上可以讀懂這些輸出信息了。

USE AdventureWorks2014
GO
SELECT DB_NAME(pa.database_id) AS [database_name] ,
 OBJECT_NAME(pa.object_id) AS [table_name] ,
 id.name  AS [index_name] ,
 pa.partition_id AS [partition_id],
 pa.is_allocated AS [is_allocated],
 pa.allocated_page_file_id AS [file_id] ,
 pa.allocated_page_page_id AS [page_id] ,
 pa.page_type_desc ,
 pa.page_level ,
 pa.previous_page_page_id AS [previous_page_id] ,
 pa.next_page_page_id AS [next_page_id] ,
 pa.is_mixed_page_allocation AS [is_mixed_page_allocation],
 pa.is_iam_page  AS [is_iam_page],
 pa.allocation_unit_id AS [allocation_unit_id],
 pa.has_ghost_records AS [has_ghost_records]
FROM sys.dm_db_database_page_allocations(DB_ID('AdventureWorks2014'),
   OBJECT_ID('TestDeadLock'), NULL,
   NULL, 'DETAILED') pa
 LEFT OUTER JOIN sys.indexes id ON id.object_id = pa.object_id
   AND id.index_id = pa.index_id
ORDER BY page_level DESC ,
 is_allocated DESC ,
 previous_page_page_id;

參考資料： 

https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

總結(jié)

以上就是我在處理客戶端真實(shí)IP的方法，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)我們的支持。