SQL Server如何找出一个表包含的页信息

41次阅读

共计 5116 个字符，预计需要花费 13 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家分享的是有关 SQL Server 如何找出一个表包含的页信息的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。

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

另外，关于 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 AdventureWorks2014
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;

感谢各位的阅读！关于“SQL Server 如何找出一个表包含的页信息”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

向 AI 问一下细节

正文完