在SQL Server中，如何找到一張表或某個索引擁有那些頁面（page）呢？ 有時候，我們在分析和研究（例如，死鎖分析）的時候還真有這樣的需求，那么如何做呢？ SQL Server 2012提供了一個無檔案的DMF（sys.dm_db_database_page_allocations）可以實作我們的需求，sys.dm_db_database_page_allocations有下面幾個引數：
· @DatabaseId: 資料庫的ID，可以用DB_ID()函式獲取某個資料庫或當前資料庫的ID
· @TableId: 表的ID， 我們可以使用OBJECT_ID（）函式通過表名獲取表ID， 這是一個可選引數，如果將其作為NULL傳遞，則回傳與資料庫中所有表的關聯頁面，當它為NULL時，將忽略接下來的兩個引數（即@IndexId和@PartionId）值
· @IndexId: 索引的索引ID， 我們可以使用sys.indexes目錄視圖來獲取索引ID， 它是一個可選引數，如果將其作為NULL傳遞，則回傳所有索引關聯的頁面，
· @PartitionId: 磁區的ID，它是一個可選引數，如果將其作為NULL傳遞，則回傳與所有磁區關聯的頁面.
· @Mode: 這是必填引數，有“LIMITED”或“DETAILED”兩個引數， “LIMITED”回傳的資訊較少， “DETAILED”會回傳詳細/更多信息，顯然，“DETAILED”模式會占用更多資源，
對于大表而言，如果選擇“DETAILED”引數，則消耗的資源和時間非常長，這個時候非常有必要選擇“LIMITED”引數，
為了更好的理解sys.dm_db_database_page_allocations輸出的資料，其實我們有必要簡單了解、回顧一下SQL Server中資料存盤的相關知識點， 這就涉及到頁（Page）和區（Extent）的概念了，SQL Server中資料存盤的基本單位是頁，磁盤I/O操作在頁級執行，也就是說，SQL Server讀取或寫入資料的最小單位就是以8 KB為單位的頁，
區是管理空間的基本單位， 一個區是8個物理上連續的頁的集合（64KB），所有頁都存盤在區中，區用來有效地管理頁所有頁都存盤在區中， SQL Server中有兩種型別的區：
統一區： 由單個物件所有，區中的所有8頁只能有一個物件使用，
混合區： 最多可由8個物件共享，區中8頁中每一頁都可由不同的物件所有，但是一頁總是只能屬于一個物件，
SQL Server中頁也有很多型別，具體參考下面表格，
注意事項：有些Page Type比較少見，暫時有些資料沒有補充完善
| PAGE_TYPE | 頁型別 | 頁型別碼 | 描述 |
| 1 | Data Page | DATA_PAGE | 資料頁(Data Page)用來存放資料 l 堆中的資料頁 l 聚集索引中“葉子“頁 |
| 2 | Index Page | INDEX_PAGE | 索引頁（Index Page）,聚集索引的非葉子節點和非聚集索引的所有索引記錄 |
| 3 | Text Mixed Page | TEXT_MIX_PAGE | 一個文本頁面，其中包含小塊的LOB值以及text tree的內部，這些可以在索引或堆的同一磁區中的LOB值之間共享， A text page that holds small chunks of LOB values plus internal parts of text tree. These can be shared between LOB values in the same partition of an index or heap. |
| 4 | Text Tree Page | TEXT_TREE_PAGE | A text page that holds large chunks of LOB values from a single column value |
| 7 | Sort Page |
| 在排序操作期間存盤中間結果的頁面 |
| 8 | Global Allocation Map Page | GAM_PAGE | GAM在資料檔案中第三個頁上，檔案和頁的編號為（1:2），它用bit位來標識相應的區（extents）是否已經被分配，它差不多能標識約64000個區（8k pages * 8 bits per byte），也就是4G的空間，如果資料空間超過4G，那么資料庫會用另外一個GAM頁來標識下一個4G空間 Bit=1： 標識當前的區是空閑的，可以用來分配 Bit=0： 標識當前的區已經被資料使用了 |
| 9 | Shared Global Allocation Map Page | SGAM_PAGE | SGAM在資料檔案的第四個頁上，檔案和頁編號為（1:3），它的結構和GAM是一樣的，區別在于Bit位的含義不同： Bit=1：區是混合區，且區內至少有一個頁是可以被用來分配的 Bit=0：區是統一區， 或者是混合區但是區內所有的頁都是在被使用的 |
| 10 | Index Allocation Map Page | IAM_PAGE | 表或索引所使用的區的資訊， |
| 11 | Page Free Space Page | PFS_PAGE | 存盤本資料檔案里所有頁分配和頁的可用空間的資訊 |
| 13 | Boot Page | BOOT_PAGE | 包含有關資料庫的相關資訊， 資料庫中有且只有一個， 它位于檔案1中的第9頁， |
| 15 | File header page | FILEHEADER_PAGE | 檔案標題頁， 包含有關檔案的資訊， 每個檔案一個，檔案的第0頁， |
| 16 | Differential Changed Map | DIFF_MAP_PAGE | 自最后一條BACKUP DATABASE陳述句之后更改的區的資訊 |
| 17 | Bulk Changed Map |
| 自最后一條BACKUP LOG陳述句之后的大容量操作鎖修改的區的資訊 |
| 18 |
|
| a page that’s be deallocated by DBCC CHECKDB during a repair operation |
| 19 |
|
| the temporary page that ALTER INDEX … REORGANIZE (or DBCC INDEXDEFRAG) uses when working on an index |
| 20 |
|
| a page pre-allocated as part of a bulk load operation, which will eventually be formatted as a ‘real’ page |
另外，關于sys.dm_db_database_page_allocations的輸出欄位資訊如下所示（搜索相關資料結合自己的理解，如果錯誤，敬請指出）：
| 欄位 | 中文欄位描述 | 英文描述 |
| database_id | 資料庫ID | ID of the database |
| object_id | 表或視圖物件的ID | Object ID For the table or view |
| index_id | 索引ID | ID for the index |
| partition_id | 索引的磁區號 | Partition number for the index |
| rowset_id | 索引的Partition ID | Partition ID for the index |
| allocation_unit_id | 分配單元的 ID | ID of the allocation unit |
| allocation_unit_type | 分配單元的型別 | Type of allocation unit |
| allocation_unit_type_desc | 分配單元的型別描述 | Description for the allocation unit |
| data_clone_id |
| ? |
| clone_state |
| ? |
| clone_state_desc |
| ? |
| extent_file_id | 區的檔案ID | File ID of the extend |
| extent_page_id | 區的檔案ID | Page ID for the extend |
| allocated_page_iam_file_id | 與頁面關聯的索引分配映射頁面的檔案ID | File ID for the index allocation map page associate to the page |
| allocated_page_iam_page_id | 與頁面關聯的索引分配映射頁面的頁面ID | Page ID for the index allocation map page associated to the page |
| allocated_page_file_id | 分配頁面的File ID | File ID of the allocated page |
| allocated_page_page_id | 分配頁面的Page ID | Page ID for the allocated page |
| is_allocated | 該頁是否被分配出去了 | Indicates whether a page is allocated |
| is_iam_page | 是否為IAM頁 | Indicates whether a page is the index allocation page |
| is_mixed_page_allocation | 是否分配的混合頁面 | Indicates whether a page is allocated |
| page_free_space_percent | 頁面的空閑比例 | Percentage of space free on the page |
| page_type | 頁面的型別（數字描述） | Description of the page type |
| page_type_desc | 頁面的型別描述 |
|
| page_level | 頁的層數 |
|
| next_page_file_id | 下一個頁的 Fiel ID | File ID for the next page |
| next_page_page_id | 下一個頁的Page ID | Page ID for the next page |
| previous_page_file_id | 前一個頁的File ID | File ID for the previous page |
| previous_page_page_id | 前一個頁的Page ID | Page ID for the previous Page |
| is_page_compressed | 頁是否壓縮 | Indicates whether the page is compressed |
| has_ghost_records | 是否存虛影記錄記錄 | Indicates whether the page have ghost records |
簡單了解了上面知識點后，我們在使用這個DMF找出表或索引相關的頁面，基本上可以讀懂這些輸出資訊了，
USE AdventureWorks2014GOSELECT 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_idORDER 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/
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/237018.html
標籤：其他