SQL Server怎么找出一个表包含的页信息（Page)
前言
在SQLServer中，如何找到一张表或某个索引拥有那些页面（page）呢？有时候，我们在分析和研究（例如，死锁分析）的时候还真有这样的需求，那么如何做呢？SQLServer2012提供了一个无文档的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输出的数据，其实我们有必要简单了解、回顾一下SQLServer中数据存储的相关知识点。这就涉及到页（Page）和区（Extent）的概念了。SQLServer中数据存储的基本单位是页，磁盘I/O操作在页级执行。也就是说，SQLServer读取或写入数据的最小单位就是以8KB为单位的页。
区是管理空间的基本单位。一个区是8个物理上连续的页的集合（64KB），所有页都存储在区中。区用来有效地管理页所有页都存储在区中。SQLServer中有两种类型的区：
- 统一区：由单个对象所有。区中的所有8页只能有一个对象使用。
- 混合区：最多可由8个对象共享。区中8页中每一页都可由不同的对象所有。但是一页总是只能属于一个对象。
SQLServer中页也有很多类型，具体参考下面表格。
注意事项：有些PageType比较少见，暂时有些资料没有补充完善
PAGE_TYPE
页类型
页类型码
描述
1
DataPage
DATA_PAGE
数据页(DataPage)用来存放数据
l 堆中的数据页
l 聚集索引中“叶子“页
2
IndexPage
INDEX_PAGE
索引页（IndexPage）,聚集索引的非叶子节点和非聚集索引的所有索引记录
3
TextMixedPage
TEXT_MIX_PAGE
一个文本页面，其中包含小块的LOB值以及texttree的内部，这些可以在索引或堆的同一分区中的LOB值之间共享。
AtextpagethatholdssmallchunksofLOBvaluesplusinternalpartsoftexttree.ThesecanbesharedbetweenLOBvaluesinthesamepartitionofanindexorheap.
4
TextTreePage
TEXT_TREE_PAGE
AtextpagethatholdslargechunksofLOBvaluesfromasinglecolumnvalue
7
SortPage
在排序操作期间存储中间结果的页面
8
GlobalAllocationMapPage
GAM_PAGE
GAM在数据文件中第三个页上，文件和页的编号为（1:2），它用bit位来标识相应的区（extents）是否已经被分配。它差不多能标识约64000个区（8kpages*8bitsperbyte），也就是4G的空间，如果数据空间超过4G，那么数据库会用另外一个GAM页来标识下一个4G空间
Bit=1： 标识当前的区是空闲的，可以用来分配
Bit=0： 标识当前的区已经被数据使用了
9
SharedGlobalAllocationMapPage
SGAM_PAGE
SGAM在数据文件的第四个页上，文件和页编号为（1:3），它的结构和GAM是一样的，区别在于Bit位的含义不同：
Bit=1：区是混合区，且区内至少有一个页是可以被用来分配的
Bit=0：区是统一区， 或者是混合区但是区内所有的页都是在被使用的
10
IndexAllocationMapPage
IAM_PAGE
表或索引所使用的区的信息。
11
PageFreeSpacePage
PFS_PAGE
存储本数据文件里所有页分配和页的可用空间的信息
13
BootPage
BOOT_PAGE
包含有关数据库的相关信息。 数据库中有且只有一个。它位于文件1中的第9页。
15
Fileheaderpage
FILEHEADER_PAGE
文件标题页。 包含有关文件的信息。 每个文件一个，文件的第0页。
16
DifferentialChangedMap
DIFF_MAP_PAGE
自最后一条BACKUPDATABASE语句之后更改的区的信息
17
BulkChangedMap
自最后一条BACKUPLOG语句之后的大容量操作锁修改的区的信息
18
apagethat'sbedeallocatedby duringarepairoperation
19
thetemporarypagethat (or DBCCINDEXDEFRAG)useswhenworkingonanindex
20
apagepre-allocatedaspartofabulkloadoperation,whichwilleventuallybeformattedasa‘real'page
另外，关于sys.dm_db_database_page_allocations的输出字段信息如下所示（搜索相关资料结合自己的理解，如果错误，敬请指出）：
字段
中文字段描述
英文描述
database_id
数据库ID
IDofthedatabase
object_id
表或视图对象的ID
ObjectIDForthetableorview
index_id
索引ID
IDfortheindex
partition_id
索引的分区号
Partitionnumberfortheindex
rowset_id
索引的PartitionID
PartitionIDfortheindex
allocation_unit_id
分配单元的 ID
IDoftheallocationunit
allocation_unit_type
分配单元的类型
Typeofallocationunit
allocation_unit_type_desc
分配单元的类型描述
Descriptionfortheallocationunit
data_clone_id
?
clone_state
?
clone_state_desc
?
extent_file_id
区的文件ID
FileIDoftheextend
extent_page_id
区的文件ID
PageIDfortheextend
allocated_page_iam_file_id
与页面关联的索引分配映射页面的文件ID
FileIDfortheindexallocationmappageassociatetothepage
allocated_page_iam_page_id
与页面关联的索引分配映射页面的页面ID
PageIDfortheindexallocationmappageassociatedtothepage
allocated_page_file_id
分配页面的FileID
FileIDoftheallocatedpage
allocated_page_page_id
分配页面的PageID
PageID
is_allocated
该页是否被分配出去了
Indicateswhetherapageisallocated
is_iam_page
是否为IAM页
Indicateswhetherapageistheindexallocationpage
is_mixed_page_allocation
是否分配的混合页面
Indicateswhetherapageisallocated
page_free_space_percent
页面的空闲比例
Percentageofspacefreeonthepage
page_type
页面的类型（数字描述）
Descriptionofthepagetype
page_type_desc
页面的类型描述
page_level
页的层数
next_page_file_id
下一个页的 FielID
FileIDforthenextpage
next_page_page_id
下一个页的PageID
PageIDforthenextpage
previous_page_file_id
前一个页的FileID
FileIDforthepreviouspage
previous_page_page_id
前一个页的PageID
PageIDforthepreviousPage
is_page_compressed
页是否压缩
Indicateswhetherthepageiscompressed
has_ghost_records
是否存虚影记录记录
Indicateswhetherthepagehaveghostrecords
简单了解了上面知识点后，我们在使用这个DMF找出表或索引相关的页面，基本上可以读懂这些输出信息了。
USEAdventureWorks2014
GO
SELECTDB_NAME(pa.database_id)AS[database_name],
OBJECT_NAME(pa.object_id)AS[table_name],
id.nameAS[index_name],
pa.partition_idAS[partition_id],
pa.is_allocatedAS[is_allocated],
pa.allocated_page_file_idAS[file_id],
pa.allocated_page_page_idAS[page_id],
pa.page_type_desc,
pa.page_level,
pa.previous_page_page_idAS[previous_page_id],
pa.next_page_page_idAS[next_page_id],
pa.is_mixed_page_allocationAS[is_mixed_page_allocation],
pa.is_iam_pageAS[is_iam_page],
pa.allocation_unit_idAS[allocation_unit_id],
pa.has_ghost_recordsAS[has_ghost_records]
FROMsys.dm_db_database_page_allocations(DB_ID('AdventureWorks2014'),
OBJECT_ID('TestDeadLock'),NULL,
NULL,'DETAILED')pa
LEFTOUTERJOINsys.indexesidONid.object_id=pa.object_id
ANDid.index_id=pa.index_id
ORDERBYpage_levelDESC,
is_allocatedDESC,
previous_page_page_id;
参考资料：
https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。