這篇文章給大家分享的是有關(guān)SQL Server如何找出一個(gè)表包含的頁信息的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過來看看吧。
創(chuàng)新互聯(lián)公司服務(wù)項(xiàng)目包括靖遠(yuǎn)網(wǎng)站建設(shè)、靖遠(yuǎn)網(wǎng)站制作、靖遠(yuǎn)網(wǎng)頁制作以及靖遠(yuǎn)網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，靖遠(yuǎn)網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到靖遠(yuǎn)省份的部分城市，未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
在SQL Server中，如何找到一張表或某個(gè)索引擁有那些頁面（page）呢？ 有時(shí)候，我們在分析和研究（例如，死鎖分析）的時(shí)候還真有這樣的需求，那么如何做呢？ SQL Server 2012提供了一個(gè)無文檔的DMF（sys.dm_db_database_page_allocations）可以實(shí)現(xiàn)我們的需求，sys.dm_db_database_page_allocations有下面幾個(gè)參數(shù)：
@DatabaseId: 數(shù)據(jù)庫的ID，可以用DB_ID()函數(shù)獲取某個(gè)數(shù)據(jù)庫或當(dāng)前數(shù)據(jù)庫的ID
@TableId: 表的ID。 我們可以使用OBJECT_ID（）函數(shù)通過表名獲取表ID。 這是一個(gè)可選參數(shù)，如果將其作為NULL傳遞，則返回與數(shù)據(jù)庫中所有表的關(guān)聯(lián)頁面，當(dāng)它為NULL時(shí)，將忽略接下來的兩個(gè)參數(shù)（即@IndexId和@PartionId）值
@IndexId: 索引的索引ID。 我們可以使用sys.indexes目錄視圖來獲取索引ID。 它是一個(gè)可選參數(shù)，如果將其作為NULL傳遞，則返回所有索引關(guān)聯(lián)的頁面。
@PartitionId: 分區(qū)的ID，它是一個(gè)可選參數(shù)，如果將其作為NULL傳遞，則返回與所有分區(qū)關(guān)聯(lián)的頁面.
@Mode: 這是必填參數(shù)，有“LIMITED”或“DETAILED”兩個(gè)參數(shù)。 “LIMITED”返回的信息較少。 “DETAILED”會(huì)返回詳細(xì)/更多信息。顯然，“DETAILED”模式會(huì)占用更多資源。
對于大表而言，如果選擇“DETAILED”參數(shù)，則消耗的資源和時(shí)間非常長，這個(gè)時(shí)候非常有必要選擇“LIMITED”參數(shù)。
為了更好的理解sys.dm_db_database_page_allocations輸出的數(shù)據(jù)，其實(shí)我們有必要簡單了解、回顧一下SQL Server中數(shù)據(jù)存儲(chǔ)的相關(guān)知識(shí)點(diǎn)。 這就涉及到頁（Page）和區(qū)（Extent）的概念了。SQL Server中數(shù)據(jù)存儲(chǔ)的基本單位是頁，磁盤I/O操作在頁級執(zhí)行。也就是說，SQL Server讀取或?qū)懭霐?shù)據(jù)的最小單位就是以8 KB為單位的頁。
區(qū)是管理空間的基本單位。 一個(gè)區(qū)是8個(gè)物理上連續(xù)的頁的集合（64KB），所有頁都存儲(chǔ)在區(qū)中。區(qū)用來有效地管理頁所有頁都存儲(chǔ)在區(qū)中。 SQL Server中有兩種類型的區(qū)：
統(tǒng)一區(qū)： 由單個(gè)對象所有。區(qū)中的所有8頁只能有一個(gè)對象使用。
混合區(qū)： 最多可由8個(gè)對象共享。區(qū)中8頁中每一頁都可由不同的對象所有。但是一頁總是只能屬于一個(gè)對象。
SQL Server中頁也有很多類型，具體參考下面表格。
注意事項(xiàng)：有些Page Type比較少見，暫時(shí)有些資料沒有補(bǔ)充完善
PAGE_TYPE | 頁類型 | 頁類型碼 | 描述 |
1 | Data Page | DATA_PAGE | 數(shù)據(jù)頁(Data Page)用來存放數(shù)據(jù) l 堆中的數(shù)據(jù)頁 l 聚集索引中“葉子“頁 |
2 | Index Page | INDEX_PAGE | 索引頁（Index Page）,聚集索引的非葉子節(jié)點(diǎn)和非聚集索引的所有索引記錄 |
3 | Text Mixed Page | TEXT_MIX_PAGE | 一個(gè)文本頁面，其中包含小塊的LOB值以及text tree的內(nèi)部，這些可以在索引或堆的同一分區(qū)中的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 |
| 在排序操作期間存儲(chǔ)中間結(jié)果的頁面 |
8 | Global Allocation Map Page | GAM_PAGE | GAM在數(shù)據(jù)文件中第三個(gè)頁上，文件和頁的編號(hào)為（1:2），它用bit位來標(biāo)識(shí)相應(yīng)的區(qū)（extents）是否已經(jīng)被分配。它差不多能標(biāo)識(shí)約64000個(gè)區(qū)（8k pages * 8 bits per byte），也就是4G的空間，如果數(shù)據(jù)空間超過4G，那么數(shù)據(jù)庫會(huì)用另外一個(gè)GAM頁來標(biāo)識(shí)下一個(gè)4G空間 Bit=1： 標(biāo)識(shí)當(dāng)前的區(qū)是空閑的，可以用來分配 Bit=0： 標(biāo)識(shí)當(dāng)前的區(qū)已經(jīng)被數(shù)據(jù)使用了 |
9 | Shared Global Allocation Map Page | SGAM_PAGE | SGAM在數(shù)據(jù)文件的第四個(gè)頁上，文件和頁編號(hào)為（1:3），它的結(jié)構(gòu)和GAM是一樣的，區(qū)別在于Bit位的含義不同： Bit=1：區(qū)是混合區(qū)，且區(qū)內(nèi)至少有一個(gè)頁是可以被用來分配的 Bit=0：區(qū)是統(tǒng)一區(qū)， 或者是混合區(qū)但是區(qū)內(nèi)所有的頁都是在被使用的 |
10 | Index Allocation Map Page | IAM_PAGE | 表或索引所使用的區(qū)的信息。 |
11 | Page Free Space Page | PFS_PAGE | 存儲(chǔ)本數(shù)據(jù)文件里所有頁分配和頁的可用空間的信息 |
13 | Boot Page | BOOT_PAGE | 包含有關(guān)數(shù)據(jù)庫的相關(guān)信息。 數(shù)據(jù)庫中有且只有一個(gè)。它位于文件1中的第9頁。 |
15 | File header page | FILEHEADER_PAGE | 文件標(biāo)題頁。 包含有關(guān)文件的信息。 每個(gè)文件一個(gè)，文件的第0頁。 |
16 | Differential Changed Map | DIFF_MAP_PAGE | 自最后一條BACKUP DATABASE語句之后更改的區(qū)的信息 |
17 | Bulk Changed Map |
| 自最后一條BACKUP LOG語句之后的大容量操作鎖修改的區(qū)的信息 |
18 |
|
| a page that's be deallocated by during a repair operation |
19 |
|
| the temporary page that (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 |
另外，關(guān)于sys.dm_db_database_page_allocations的輸出字段信息如下所示（搜索相關(guān)資料結(jié)合自己的理解，如果錯(cuò)誤，敬請指出）：
字段 | 中文字段描述 | 英文描述 |
database_id | 數(shù)據(jù)庫ID | ID of the database |
object_id | 表或視圖對象的ID | Object ID For the table or view |
index_id | 索引ID | ID for the index |
partition_id | 索引的分區(qū)號(hào) | 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 | 區(qū)的文件ID | File ID of the extend |
extent_page_id | 區(qū)的文件ID | Page ID for the extend |
allocated_page_iam_file_id | 與頁面關(guān)聯(lián)的索引分配映射頁面的文件ID | File ID for the index allocation map page associate to the page |
allocated_page_iam_page_id | 與頁面關(guān)聯(lián)的索引分配映射頁面的頁面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 | 頁面的類型（數(shù)字描述） | Description of the page type |
page_type_desc | 頁面的類型描述 |
|
page_level | 頁的層數(shù) |
|
next_page_file_id | 下一個(gè)頁的 Fiel ID | File ID for the next page |
next_page_page_id | 下一個(gè)頁的Page ID | Page ID for the next page |
previous_page_file_id | 前一個(gè)頁的File ID | File ID for the previous page |
previous_page_page_id | 前一個(gè)頁的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 |
簡單了解了上面知識(shí)點(diǎn)后，我們在使用這個(gè)DMF找出表或索引相關(guān)的頁面，基本上可以讀懂這些輸出信息了。
重慶分公司
重慶分公司
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;感謝各位的閱讀！關(guān)于“SQL Server如何找出一個(gè)表包含的頁信息”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！
