本文主要向大家介绍SQL Server如何找出表中包含的页面信息(Page)的相关信息。文章通过示例代码介绍的非常详细,对于大家学习或者使用SQL Server有一定的参考学习价值,
前言
在SQL Server中,如何查找表或索引有哪些页?有时候,我们在分析研究的时候确实有这样的需求(比如死锁分析),那么怎么办呢? SQL Server 2012提供了一个未记录的DMF(www.gsm-guard.net_db_database_page_allocations
)可以实现我们的需求。 www.gsm-guard.net_db_database_page_allocations
具有以下参数:
对于大表,如果选择“DETAILED”参数,消耗的资源和时间会非常长。这时,选择“LIMITED”参数就非常有必要了。
为了更好地理解www.gsm-guard.net_db_database_page_allocations
输出的数据,其实我们需要简单了解和回顾一下SQL Server中数据存储的相关知识点。这就涉及到Page和Extent的概念了。 SQL Server中数据存储的基本单位是页,磁盘I/O操作都是在页级别进行的。换句话说,SQL Server 可以读取或写入数据的最小单位是页,以 8 KB 为单位。
面积是管理空间的基本单位。一个盘区是 8 个物理上连续的页(64KB)的集合,所有页都存储在该盘区中。区域用于有效地管理页面。所有页面都存储在区域中。 SQL Server 中有两种类型的区:
SQL Server 中的页面也有很多种类型。详情请参阅下表。
注:部分页面类型比较少见,部分信息暂时没有补充完善
页面类型 | 页面类型 | 页面类型代码 | 描述 |
1 | 数据页 | 数据页 | 数据页用于存储数据 l 堆中的数据页 l 聚集索引中的“叶子”页 |
2 | 索引页 | INDEX_PAGE | 索引页、聚集索引的非叶节点以及非聚集索引的所有索引记录 |
3 | 文字混合页面 | TEXT_MIX_PAGE | 包含小块 LOB 值和文本树内部的文本页,可以在索引或堆的同一分区中的 LOB 值之间共享。 一个文本页,包含小块 LOB 值以及文本树的内部部分。这些可以在索引或堆的同一分区中的 LOB 值之间共享。 |
4 | 文本树页面 | TEXT_TREE_PAGE | 一个文本页,包含来自单个列值的大块 LOB 值 |
7 | 排序页面 | 存储排序操作期间中间结果的页面 | |
8 | 全球分配图页面 | GAM_PAGE | GAM 位于数据文件的第三页上。文件编号和页码为 (1:2)。它使用位来标识相应的盘区是否已分配。它可以识别大约 64,000 个区域(8k 页 * 每字节 8 位),即 4G 的空间。如果数据空间超过 4G,数据库会使用另一个 GAM 页来标识下一个 4G 空间 Bit=1 :表示当前区域空闲,可用于分配 Bit=0:表示当前区域已被数据使用 |
9 | 共享全球分配地图页面 | SGAM_PAGE | SGAM 位于数据文件的第四页上。文件和页码为 (1:3)。其结构与GAM相同。不同的是 Bit 的含义不同: Bit=1:Area 是混合区域,该区域中至少有一个 page 可以用于分配 Bit=0:该区域是一个统一区域,或者混合区域但该区域内所有页面都在使用中 |
10 | 索引分配图页 | IAM_PAGE | 表或索引使用的区域的信息。 |
11 | 页面可用空间页面 | PFS_PAGE | 在此数据文件中存储有关所有页面分配和页面可用空间的信息 |
13 | 启动页面 | BOOT_PAGE | 包含有关数据库的相关信息。数据库中只有一个。位于文件 1 的第 9 页。 |
15 | 文件标题页 | FILEHEADER_PAGE | 文档标题页。包含有关文件的信息。每个文件一个,文件的第 0 页。 |
16 | 差异变化地图 | DIFF_MAP_PAGE | 有关自上次 BACKUP DATABASE 语句以来更改的区域的信息 |
17 | 批量更改地图 | 自上次 BACKUP LOG 语句以来批量操作锁定修改的区域的信息 | |
18 | 修复操作期间释放的页面 | ||
19 | 处理索引时使用的临时页(或 DBCC INDEXDEFRAG) | ||
20 | 作为批量加载操作的一部分预先分配的页面,最终将被格式化为“真实”页面 |
另外,关于www.gsm-guard.net_db_database_page_allocations
的输出字段信息如下(搜索相关资料并结合自己的理解,如有错误请指出):
场 | 中文字段描述 | 英文说明 |
database_id | 数据库 ID | 数据库ID |
object_id | 表或视图对象的 ID | 表或视图的对象 ID |
index_id | 索引 ID | 索引的 ID |
partition_id | 索引的分区号 | 索引的分区号 |
rowset_id | 索引的分区ID | 索引的分区 ID |
allocation_unit_id | 分配单位的ID | 分配单元ID |
allocation_unit_type | 分配单位类型 | 分配单位类型 |
allocation_unit_type_desc | 分配单元类型描述 | 分配单位说明 |
data_clone_id | ? | |
克隆状态 | ? | |
clone_state_desc | ? | |
extent_file_id | 区域 | 的文件ID扩展的文件ID |
extent_page_id | 区域 | 的文件ID扩展的页面 ID |
分配_page_iam_file_id | 与页面关联的索引分配映射页面的文件ID | 与页面关联的索引分配映射页面的文件 ID |
分配_page_iam_page_id | 与页面关联的索引分配映射页面的页面ID | 与页面关联的索引分配映射页面的页面 ID |
分配_页面_文件_id | 指定页面的文件ID | 分配页面的文件ID |
分配_page_page_id | 指定页面的Page ID | 分配页面的页面 ID |
已分配 | 此页已分配吗? | 指示是否分配页面 |
is_iam_page | 是否为IAM页面 | 指示该页是否为索引分配页 |
is_mixed_page_allocation | 是否分配了混合页面 | 指示是否分配页面 |
page_free_space_percent | 页面空闲率 | 页面上可用空间的百分比 |
页面类型 | 页面类型(数字描述) | 页面类型描述 |
page_type_desc | 输入页面描述 | |
页面级别 | 页数 | |
下一页文件_id | 下一页的字段 ID | 下一页的文件 ID |
下一页_page_id | 下一页的页ID | 下一页的页面 ID |
上一页_文件_id | 上一页的文件ID | 上一页的文件 ID |
上一页_page_id | 上一页的页面ID | 上一页的页面 ID |
is_page_compressed | 页面是否压缩 | 指示页面是否被压缩 |
has_ghost_records | 是否有鬼记录 | 指示页面是否有鬼记录 |
简单了解了上面的知识点之后,我们就可以利用这个DMF来查找与表或者索引相关的页面,输出的信息我们基本就可以明白了。
使用 AdventureWorks2014
去
SELECT DB_NAME(pa.database_id) AS [database_name] ,
OBJECT_NAME(pa.object_id) AS [表名] ,
www.gsm-guard.net AS [索引名称] ,
pa.partition_id AS [partition_id],
www.gsm-guard.net_allocated AS [已分配],
pa.allocated_page_file_id AS [file_id] ,
pa.allocated_page_page_id AS [page_id] ,
www.gsm-guard.net_type_desc,
www.gsm-guard.net_level,
pa.previous_page_page_id AS [previous_page_id] ,
www.gsm-guard.net_page_page_id AS [next_page_id] ,
www.gsm-guard.net_mixed_page_allocation AS [is_mixed_page_allocation],
www.gsm-guard.net_iam_page AS [is_iam_page],
pa.allocation_unit_id AS [allocation_unit_id],
pa.has_ghost_records AS [has_ghost_records]
FROM www.gsm-guard.net_db_database_page_allocations(DB_ID('AdventureWorks2014'),
OBJECT_ID('TestDeadLock'), NULL,
NULL,“详细”)pa左外连接 sys.indexes id ON id.object_id = pa.object_id
AND id.index_id = pa.index_id
按页面级别 DESC 排序,
已分配 DESC ,
previous_page_page_id;
参考:
https://www.gsm-guard.net/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/
总结
以上就是本文全部内容。希望本文内容对大家的学习或者工作有一定的参考和学习价值,