sys.dm_db_index_physical_stats (Transact-SQL)

适用于:SQL ServerAzure SQL 数据库Azure SQL 托管实例

返回SQL Server中指定表或视图的数据和索引的大小和碎片信息。 对于索引,针对每个分区中的 B 树的每个级别,返回与其对应的一行。 对于堆,为每个分区的 IN_ROW_DATA 分配单元返回一行。 对于 (LOB) 数据的大型对象,将为每个分区的 LOB_DATA 分配单元返回一行。 如果表中存在行溢出数据,则为每个分区中的 ROW_OVERFLOW_DATA 分配单元返回一行。

注意

SQL Server 文档在提到索引时一般使用 B 树这个术语。 在行存储索引中,SQL Server 实现了 B+ 树。 这不适用于列存储索引或内存中数据存储。 有关详细信息,请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南

sys.dm_db_index_physical_stats 不返回有关内存优化列存储索引的信息。 有关内存优化索引使用的信息,请参阅 sys.dm_db_xtp_index_stats (Transact-SQL)

如果在承载可用性组可读辅助副本 (replica) 的服务器实例上进行查询sys.dm_db_index_physical_stats,可能会遇到REDO阻塞问题。 这是因为此动态管理视图获取IS指定用户表或视图的锁,该锁可以阻止线程对该X用户表或视图的锁的请求REDO

Transact-SQL 语法约定

语法

sys.dm_db_index_physical_stats (
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

参数

database_id |NULL |0 |默认

数据库的 ID。 database_idsmallint。 有效输入是数据库的 ID、NULL、0 或 DEFAULT。 默认值为 0。 NULL、0 和 DEFAULT 是此上下文中的等效值。

指定 NULL 可返回 SQL Server 实例中所有数据库的信息。 如果为 database_id指定 NULL,则还必须为 object_idindex_idpartition_number指定 NULL。

可以指定内置函数 DB_ID。 使用 DB_ID 但不指定数据库名称时,当前数据库的兼容级别必须为 90 或更高。

object_id |NULL |0 |默认

索引所针对的表或视图的对象 ID。 object_idint

有效输入是表和视图的 ID、NULL、0 或 DEFAULT。 默认值为 0。 NULL、0 和 DEFAULT 是此上下文中的等效值。 自 2016 (13.x) SQL Server 起,有效输入还包括 Service Broker 队列名称或队列内部表名称。 当应用默认参数 (即所有对象、所有索引等) 时,所有队列的碎片信息将包含在结果集中。

指定 NULL 可返回指定数据库中的所有表和视图的信息。 如果为 object_id指定 NULL,则还必须为 index_idpartition_number指定 NULL。

index_id |0 |NULL |-1 |默认

索引的 ID。 index_idint。有效输入是索引的 ID,如果 object_id 为堆、NULL、-1 或 DEFAULT,则为 0。 默认值为 -1。 NULL、-1 和 DEFAULT 是此上下文中的等效值。

指定 NULL 可返回基表或视图的所有索引的信息。 如果为 index_id指定 NULL,则还必须为 partition_number指定 NULL。

partition_number |NULL |0 |默认

对象中的分区号。 partition_numberint。有效输入是索引或堆 的partion_number 、NULL、0 或 DEFAULT。 默认值为 0。 NULL、0 和 DEFAULT 是此上下文中的等效值。

指定 NULL,以返回有关所属对象的所有分区的信息。

partition_number 从 1 开始。 非分区索引或堆 partition_number 设置为 1。

模式 |NULL |默认

模式的名称。 mode 指定用于获取统计信息的扫描级别。 modesysname。 有效输入为 DEFAULT、NULL、LIMITED、SAMPLED 或 DETAILED。 默认值 (NULL) 为 LIMITED。

返回的表

列名称 数据类型 说明
database_id smallint 表或视图的数据库 ID。

在 Azure SQL Database 中,这些值在单一数据库或弹性池中是唯一的,但在逻辑服务器中不是唯一的。
object_id int 索引所在的表或视图的对象 ID。
index_id int 索引的索引 ID。

0 = 堆。
partition_number int 所属对象内从 1 开始的分区号;表、视图或索引。

1 = 未分区的索引或堆。
index_type_desc nvarchar(60) 索引类型的说明:

- 堆
- CLUSTERED INDEX
- NONCLUSTERED INDEX
- PRIMARY XML INDEX
- 扩展索引
- XML INDEX
- 列存储映射索引 (内部)
- COLUMNSTORE DELETEBUFFER INDEX (内部)
- COLUMNSTORE DELETEBITMAP INDEX (内部)
hobt_id bigint 索引或分区的堆或 B 树 ID。

对于列存储索引,这是跟踪分区的内部列存储数据的行集的 ID。 行集存储为数据堆或 B 树。 它们的索引 ID 与父列存储索引相同。 有关详细信息,请参阅 sys.internal_partitions (Transact-SQL)
alloc_unit_type_desc nvarchar(60) 对分配单元类型的说明:

- IN_ROW_DATA
- LOB_DATA
- ROW_OVERFLOW_DATA

分配 LOB_DATA 单元包含存储在 textntextimagevarchar (max) nvarchar (max) varbinary (max) xml 的列中的数据。 有关详细信息,请参阅 数据类型 (Transact-SQL)

分配 ROW_OVERFLOW_DATA 单元包含存储在 varchar (n) nvarchar (n) varbinary (n) sql_variant 的列中的数据。
index_depth tinyint 索引级别数。

1 = 堆或 LOB_DATAROW_OVERFLOW_DATA 分配单位。
index_level tinyint 索引的当前级别。

0 表示索引叶级别、堆和 LOB_DATAROW_OVERFLOW_DATA 分配单位。

大于 0 的值表示非叶索引级别。 index_level 是索引根级别的最高值。

仅当 mode = DETAILED 时,才会处理索引的非叶级别。
avg_fragmentation_in_percent float 索引的逻辑碎片,或分配单元中堆的 IN_ROW_DATA 盘区碎片。

此值按百分比计算,并将考虑多个文件。 有关逻辑碎片和区碎片的定义,请参阅“注释”。

0 表示 LOB_DATAROW_OVERFLOW_DATA 分配单位。

mode = SAMPLED 时堆的 NULL。
fragment_count bigint 分配单元的叶级别 IN_ROW_DATA 中的片段数。 有关碎片的详细信息,请参阅“注释”。

对于索引和 LOB_DATAROW_OVERFLOW_DATA 分配单位的非叶级别,为 NULL。

mode = SAMPLED 时堆的 NULL。
avg_fragment_size_in_pages float 分配单元的叶级别 IN_ROW_DATA 中一个片段中的平均页数。

对于索引和 LOB_DATAROW_OVERFLOW_DATA 分配单位的非叶级别,为 NULL。

mode = SAMPLED 时堆的 NULL。
page_count bigint 索引或数据页的总数。

对于索引,是分配单元中 B 树 IN_ROW_DATA 当前级别的索引页总数。

对于堆,为分配单元中的数据 IN_ROW_DATA 页总数。

对于 LOB_DATAROW_OVERFLOW_DATA 分配单位,分配单元中的总页数。
avg_page_space_used_in_percent float 所有页中使用的可用数据存储空间的平均百分比。

对于索引,平均值适用于分配单元中 B 树的 IN_ROW_DATA 当前级别。

对于堆,为分配单元中所有数据页的 IN_ROW_DATA 平均值。

对于 LOB_DATAROW_OVERFLOW_DATA 分配单位,为分配单元中所有页面的平均值。

mode = LIMITED 时为 NULL。
record_count bigint 总记录数。

对于索引,记录总数适用于分配单元中 IN_ROW_DATA B 树的当前级别。

对于堆,为分配单元中的 IN_ROW_DATA 记录总数。

注意: 对于堆,从此函数返回的记录数可能与通过对堆运行 返回 SELECT COUNT(*) 的行数不匹配。 这是因为一行可能包含多个记录。 例如,在某些更新情况下,单个堆行可能由于更新操作而包含一条前推记录和一条被前推记录。 此外,大多数大型 LOB 行在存储中 LOB_DATA 拆分为多个记录。

对于 LOB_DATAROW_OVERFLOW_DATA 分配单位,为完整分配单元中的记录总数。

mode = LIMITED 时为 NULL。
ghost_record_count bigint 分配单元中将被虚影清除任务删除的虚影记录数。

0 表示分配单元中 IN_ROW_DATA 索引的非叶级别。

mode = LIMITED 时为 NULL。
version_ghost_record_count bigint 由分配单元中未完成的快照隔离事务保留的虚影记录数。

0 表示分配单元中 IN_ROW_DATA 索引的非叶级别。

mode = LIMITED 时为 NULL。
min_record_size_in_bytes int 最小记录大小(字节)。

对于索引,最小记录大小适用于分配单元中 B 树的 IN_ROW_DATA 当前级别。

对于堆,分配单元中的 IN_ROW_DATA 最小记录大小。

对于 LOB_DATAROW_OVERFLOW_DATA 分配单位,为完整分配单元中的最小记录大小。

mode = LIMITED 时为 NULL。
max_record_size_in_bytes int 最大记录大小(字节)。

对于索引,最大记录大小适用于分配单元中 B 树的 IN_ROW_DATA 当前级别。

对于堆,分配单元中 IN_ROW_DATA 的最大记录大小。

对于 LOB_DATAROW_OVERFLOW_DATA 分配单位,为完整分配单元中的最大记录大小。

mode = LIMITED 时为 NULL。
avg_record_size_in_bytes float 平均记录大小(字节)。

对于索引,平均记录大小适用于分配单元中 B 树的 IN_ROW_DATA 当前级别。

对于堆,分配单元中的 IN_ROW_DATA 平均记录大小。

对于 LOB_DATAROW_OVERFLOW_DATA 分配单位,为完整分配单元中的平均记录大小。

mode = LIMITED 时为 NULL。
forwarded_record_count bigint 堆中具有指向另一个数据位置的转向指针的记录数。 (更新期间,如果没有足够的空间将新行存储在原始位置,则会出现此状态。)

对于堆的 IN_ROW_DATA 分配单位以外的任何分配单元,为 NULL。

当 mode = LIMITED 时,堆为 NULL。
compressed_page_count bigint 压缩页的数目。

对于堆,新分配的页面不会进行 PAGE 压缩。 堆在以下两种特殊情况下进行 PAGE 压缩:大量导入数据时和重新生成堆时。 导致页面分配的典型 DML 操作不会进行 PAGE 压缩。 当 compressed_page_count 值增长到超过您所需的阈值时,将重新生成堆。

对于具有聚集索引的表,compressed_page_count 值表示 PAGE 压缩的效率。
columnstore_delete_buffer_state tinyint 0 = NOT_APPLICABLE
1 = OPEN
2 = 排空
3 = FLUSHING
4 = 正在停用
5 = READY

适用于:SQL Server 2016 (13.x) 及更高版本、Azure SQL Database 和 Azure SQL 托管实例
columnstore_delete_buffer_state_desc nvarchar(60) 无效 - 父索引不是列存储索引。

OPEN - 删除程序和扫描程序使用此。

排出 - 删除器正在耗尽,但扫描仪仍在使用它。

FLUSHING - 缓冲区已关闭,缓冲区中的行正在写入删除位图。

停用 - 关闭的删除缓冲区中的行已写入删除位图,但缓冲区尚未被截断,因为扫描程序仍在使用它。 新扫描程序不需要使用即将停用的缓冲区,因为打开的缓冲区就足够了。

READY - 此删除缓冲区可供使用。

适用于:SQL Server 2016 (13.x) 及更高版本、Azure SQL Database 和 Azure SQL 托管实例
version_record_count bigint 这是在此索引中维护的行版本记录的计数。 这些行版本由 加速数据库恢复 功能维护。

适用于:SQL Server 2019 (15.x) 及更高版本,以及 Azure SQL Database
inrow_version_record_count bigint 数据行中保留的 ADR 版本记录计数,以便快速检索。

适用于:SQL Server 2019 (15.x) 及更高版本,以及 Azure SQL Database
inrow_diff_version_record_count bigint 以与基本版本的差异形式保存的 ADR 版本记录计数。

适用于:SQL Server 2019 (15.x) 及更高版本,以及 Azure SQL Database
total_inrow_version_payload_size_in_bytes bigint 此索引的行内版本记录的总大小(以字节为单位)。

适用于:SQL Server 2019 (15.x) 及更高版本,以及 Azure SQL Database
offrow_regular_version_record_count bigint 在原始数据行之外保留的版本记录计数。

适用于:SQL Server 2019 (15.x) 及更高版本,以及 Azure SQL Database
offrow_long_term_version_record_count bigint 被视为长期的版本记录计数。

适用于:SQL Server 2019 (15.x) 及更高版本,以及 Azure SQL Database

注意

SQL Server 文档在提到索引时一般使用 B 树这个术语。 在行存储索引中,SQL Server 实现了 B+ 树。 这不适用于列存储索引或内存中数据存储。 有关详细信息,请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南

备注

动态 sys.dm_db_index_physical_stats 管理功能替换 DBCC SHOWCONTIG 语句。

扫描模式

函数的执行模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。 模式 指定为 LIMITED、SAMPLED 或 DETAILED。 该函数遍历分配单元的页链,这些分配单元构成表或索引的指定分区。 sys.dm_db_index_physical_stats不管 在哪种模式中运行,它只需要一个意向共享 (IS) 表锁。

LIMITED 模式运行最快,扫描的页数最少。 对于索引,只扫描 B 树的父级别页(即叶级别以上的页)。 对于堆,只检查关联的 PFS 和 IAM 页;并在 LIMITED 模式下扫描堆的数据页。

在 LIMITED 模式下,为 NULL, compressed_page_count 因为数据库引擎仅扫描 B 树的非叶页以及堆的 IAM 和 PFS 页。 使用 SAMPLED 模式获取 compressed_page_count 的估计值,使用 DETAILED 模式获取 compressed_page_count 的实际值。 SAMPLED 模式基于索引或堆中所有页面的 1% 样本返回统计信息。 SAMPLED 模式中的结果应视为近似值。 如果索引或堆少于 10,000 页,则使用 DETAILED 模式代替 SAMPLED。

DETAILED 模式将扫描所有页并返回所有统计信息。

从 LIMITED 到 DETAILED 模式,速度将越来越慢,因为在每个模式中执行的任务越来越多。 若要快速测量表或索引的大小或碎片级别,请使用 LIMITED 模式。 它是最快的,并且不会为索引的分配单元中的每个 IN_ROW_DATA 非叶级别返回一行。

使用系统函数指定参数值

可以使用 Transact-SQL 函数 DB_IDOBJECT_IDdatabase_idobject_id 参数指定值。 但是,传递对这些函数无效的值可能会导致意外结果。 例如,如果由于数据库或对象名称不存在或拼写错误而找不到数据库或对象名称,则这两个函数都会返回 NULL。 sys.dm_db_index_physical_stats 函数将 NULL 解释为指定所有数据库或所有对象的通配符值。

此外,函数在 OBJECT_ID 调用函数之前 sys.dm_db_index_physical_stats 进行处理,因此在当前数据库的上下文中进行评估,而不是 database_id中指定的数据库。 此行为可能导致 OBJECT_ID 函数返回 NULL 值;或者,如果对象名称同时存在于当前数据库上下文和指定的数据库中,则可能会返回错误消息。 以下示例演示了这些意外的结果。

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database.
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks2022'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2022;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

最佳做法

始终确保在使用 DB_IDOBJECT_ID时返回有效的 ID。 例如,使用 OBJECT_ID时,请指定由三部分构成的名称,例如 OBJECT_ID(N'AdventureWorks2022.Person.Address'),或者在函数中使用 sys.dm_db_index_physical_stats 函数之前先测试函数返回的值。 下面的示例 A 和 B 演示了一种指定数据库和对象 ID 的安全方法。

检测碎片

在对表进而对表中定义的索引进行数据修改(INSERT、UPDATE 和 DELETE 语句)的整个过程中都会出现碎片。 由于这些修改通常不会在表和索引的行之间平均分布,因此每页的完整度可能会随时间而变化。 对于扫描表的部分或全部索引的查询,这种碎片会导致额外的页读取。 这会妨碍数据的并行扫描。

索引或堆的碎片级别显示在 avg_fragmentation_in_percent 列中。 对于堆,此值表示堆的区碎片。 对于索引,此值表示索引的逻辑碎片。 与 不同 DBCC SHOWCONTIG,这两种情况下的碎片计算算法都考虑跨多个文件的存储,因此是准确的。

逻辑碎片

这是索引的叶级页中出错页所占的百分比。 对于出错页,分配给索引的下一个物理页不是当前叶级别页中的下一页指针所指向的页。

盘区碎片

这是堆的叶级页中出错区所占的百分比。 无序盘区是包含堆当前页的盘区在物理上不是包含上一页的盘区之后的下一个盘区。

为了获得最佳性能,avg_fragmentation_in_percent 的值应尽可能接近零。 但是,从 0 到 10% 范围内的值都可以接受。 所有减少碎片的方法(例如重新生成、重新组织或重新创建)都可用于降低这些值。 有关如何分析索引中的碎片程度的详细信息,请参阅 重新组织和重新生成索引

减少索引中的碎片

当索引分段的方式导致碎片影响查询性能时,有三种方法可减少碎片:

  • 删除并重新创建聚集索引。

    重新创建聚集索引将对数据进行重新分布,从而使数据页填满。 填充度可以使用 CREATE INDEX 中的 FILLFACTOR 选项进行配置。 这种方法的缺点是索引在删除和重新创建周期内为脱机状态,并且操作属原子级。 如果中断索引创建,则不能重新创建索引。 有关详细信息,请参阅 CREATE INDEX (Transact-SQL)

  • 使用 ALTER INDEX REORGANIZE(替换 的 DBCC INDEXDEFRAG)按逻辑顺序对索引的叶级别页进行重新排序。 由于这是联机操作,因此在语句运行时仍可使用索引。 中断此操作时不会丢失已经完成的任务。 此方法的缺点是它无法像索引重新生成操作那样很好地重新组织数据,而且不会更新统计信息。

  • 使用 ALTER INDEX REBUILD(替换 的 DBCC DBREINDEX)联机或脱机重新生成索引。 有关详细信息,请参阅 ALTER INDEX (Transact-SQL)

单靠碎片不足以重新组织或重新生成索引。 碎片的主要影响是,在索引扫描过程中会降低页的预读吞吐量。 这将导致响应时间变长。 如果碎片表或索引上的查询工作负荷不涉及扫描,因为工作负荷主要是单一实例查找,则删除碎片可能不起作用。

注意

如果在收缩操作期间部分或完全移动索引,则运行 DBCC SHRINKFILEDBCC SHRINKDATABASE 可能会引入碎片。 因此,如果必须执行收缩操作,则应在删除碎片之前进行。

减少堆中的碎片

若要减少堆的区碎片,请对表创建聚集索引,然后删除该索引。 在创建聚集索引时将重新分布数据。 同时会考虑数据库中可用空间的分布,从而使其尽可能优化。 然后删除聚集索引以重新创建堆时,数据不会移动,并且保持最佳状态。 有关如何执行这些操作的信息,请参阅 CREATE INDEXDROP INDEX

注意

在表上创建和删除聚集索引,将重新生成该表上所有非聚集索引两次。

压缩大型对象数据

默认情况下,ALTER INDEX REORGANIZE 语句将压缩包含大型对象 (LOB) 数据的页。 由于 LOB 页在空时不会解除分配,因此,如果删除了大量 LOB 数据或删除 LOB 列,压缩此数据可以提高磁盘空间的使用。

重新组织指定的聚集索引将压缩聚集索引中包含的所有 LOB 列。 重新组织非聚集索引将压缩作为索引中非键(已包括)列的所有 LOB 列。 如果语句中指定 ALL,则将对与指定表或视图关联的所有索引进行重新组织。 此外,将压缩与聚集索引、基础表或包含列的非聚集索引关联的所有 LOB 列。

评估磁盘空间使用情况

avg_page_space_used_in_percent 列指示页填充度。 若要实现最佳磁盘空间使用,对于没有太多随机插入的索引,此值应接近 100%。 但是,具有许多随机插入且页非常完整的索引的页拆分次数增加。 这将导致更多的碎片。 因此,为了减少页拆分,此值应小于 100%。 使用指定的 FILLFACTOR 选项重新生成索引可以改变页填充度,以便符合索引中的查询模式。 有关填充因子的详细信息,请参阅 指定索引的填充因子。 此外,ALTER INDEX REORGANIZE 还试图通过将页填充到上一次指定的 FILLFACTOR 来压缩索引。 这会增加 avg_space_used_in_percent 的值。 ALTER INDEX REORGANIZE 不能降低页面填充度。 相反,必须执行索引重新生成。

评估索引片段

碎片由分配单元中同一文件内的物理连续的叶级页组成。 一个索引至少有一个碎片。 索引可以包含的最大碎片数等于索引的叶级别页数。 碎片越大,意味着读取相同页数所需的磁盘 I/O 越少。 因此,avg_fragment_size_in_pages 值越大,范围扫描的性能越好。 avg_fragment_size_in_pagesavg_fragmentation_in_percent 值彼此成反比。 因此,重新生成或重新组织索引会减少碎片数量,但同时增大碎片大小。

限制和局限

不返回聚集列存储索引的数据。

权限

需要下列权限:

  • 对数据库中的指定对象具有 CONTROL 权限。

  • VIEW DATABASE STATE 或 VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) 使用对象通配符 @object_id=NULL 返回有关指定数据库中所有对象的信息的权限。

  • VIEW SERVER STATE 或 VIEW SERVER PERFORMANCE STATE (SQL Server 2022) 使用数据库通配符 @database_id = NULL 返回有关所有数据库的信息的权限。

授予 VIEW DATABASE STATE 权限允许返回数据库中的所有对象,而不考虑对特定对象拒绝的任何 CONTROL 权限。

拒绝 VIEW DATABASE STATE 将禁止返回数据库中的所有对象,而不管对特定对象授予的任何 CONTROL 权限。 此外,指定数据库通配符 @database_id=NULL 时,将省略数据库。

有关详细信息,请参阅 动态管理视图和函数 (Transact-SQL)

示例

A. 返回有关指定表的信息

下面的示例将返回 Person.Address 表的所有索引和分区的大小和碎片统计信息。 为了获得最佳性能并限制返回的统计信息,扫描模式设置为 'LIMITED'。 执行此查询至少需要对 Person.Address 表拥有 CONTROL 权限。

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
  
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.Person.Address');
  
IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

B. 返回有关堆的信息

以下示例返回 AdventureWorks2022 数据库中堆 dbo.DatabaseLog 的所有统计信息。 由于表包含 LOB 数据,因此除了对存储堆的数据页的 LOB_DATA 返回与其对应的一行外,还对 IN_ROW_ALLOCATION_UNIT 分配单元返回与其对应的一行。 执行此查询至少需要对 dbo.DatabaseLog 表拥有 CONTROL 权限。

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.DatabaseLog');
IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

C. 返回所有数据库的信息

以下示例通过为所有参数指定通配符NULL,返回 SQL Server 实例中所有表和索引的所有统计信息。 执行此查询需要 VIEW SERVER STATE 权限。

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO

D. sys.dm_db_index_physical_stats在脚本中使用 重新生成或重新组织索引

以下示例将自动重新组织或重新生成数据库中平均碎片超过 10% 的所有分区。 执行此查询需要 VIEW DATABASE STATE 权限。 此示例在不指定数据库名称的情况下,指定 DB_ID 作为第一个参数。 如果当前数据库的兼容性级别为 80 或更低,则会生成错误。 若要纠正此错误,请用有效的数据库名称替换 DB_ID()。 有关数据库兼容性级别的详细信息,请参阅 alter DATABASE Compatibility Level (Transact-SQL)

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
    AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR
FOR
SELECT *
FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1 = 1)
BEGIN;

    FETCH NEXT
    FROM partitions
    INTO @objectid,
        @indexid,
        @partitionnum,
        @frag;

    IF @@FETCH_STATUS < 0
        BREAK;

    SELECT @objectname = QUOTENAME(o.name),
        @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    INNER JOIN sys.schemas AS s
        ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;

    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = @objectid
        AND index_id = @indexid;

    SELECT @partitioncount = count(*)
    FROM sys.partitions
    WHERE object_id = @objectid
        AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    IF @frag < 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    IF @partitioncount > 1
        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));

    EXEC (@command);

    PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

E. 用于 sys.dm_db_index_physical_stats 显示页面压缩页数

下面的示例演示如何显示行和页已压缩的页,以及如何将已压缩页数与总页数进行比较。 此信息可用于确定压缩为索引或表提供的好处。

SELECT o.name,
    ips.partition_number,
    ips.index_type_desc,
    ips.record_count,
    ips.avg_record_size_in_bytes,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.page_count,
    ips.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.objects o
    ON o.object_id = ips.object_id
ORDER BY record_count DESC;

F. 在 SAMPLED 模式下使用sys.dm_db_index_physical_stats

下面的示例显示 SAMPLED 模式如何返回与 DETAILED 模式结果不同的近似值。

CREATE TABLE t3 (
    col1 INT PRIMARY KEY,
    col2 VARCHAR(500)
    )
    WITH (DATA_COMPRESSION = PAGE);
GO

BEGIN TRANSACTION

DECLARE @idx INT = 0;

WHILE @idx < 1000000
BEGIN
    INSERT INTO t3 (col1, col2)
    VALUES (
        @idx,
        REPLICATE('a', 100) + CAST(@idx AS VARCHAR(10)) + REPLICATE('a', 380)
        )

    SET @idx = @idx + 1
END

COMMIT;
GO

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'SAMPLED');

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'DETAILED');

G. 查询索引碎片的服务代理队列

适用于:SQL Server 2016 (13.x) 及更高版本。

以下示例演示如何查询服务器中转站队列进行碎片。

--Using queue internal table name
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('sys.queue_messages_549576996'), DEFAULT, DEFAULT, DEFAULT);

--Using queue name directly
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('ExpenseQueue'), DEFAULT, DEFAULT, DEFAULT);

另请参阅