SQL Server怎么找出一个表包含的页信息（Page)

2023-08-13 02:13:04 69

前言

在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 fortheallocatedpage 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/

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。

SQL Server怎么找出一个表包含的页信息（Page)

热门推荐

随机推荐