sys.dm_db_index_physical_stats (Transact-SQL)

适用于:yesSQL Server(所有支持的版本)YesAzure SQL 数据库YesAzure SQL 托管实例

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

注意

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

如果在托管Always On可读辅助副本的服务器实例上查询sys.dm_db_index_physical_stats,可能会遇到重做阻止问题。 这是因为此动态管理视图获取指定用户表或视图的 IS 锁,而该锁可能阻止 REDO 线程对该用户表或视图的 X 锁请求。

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

主题链接图标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_id很小。 有效的输入包括数据库的 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_id为 int

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

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

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

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

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

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

partition_number 基于 1。 非分区索引或堆 partition_number 设置为 1。

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

返回的表

列名称 数据类型 说明
database_id smallint 表或视图的数据库 ID。
object_id int 索引所在的表或视图的对象 ID。
index_id int 索引的索引 ID。

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

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

HEAP

CLUSTERED INDEX

NONCLUSTERED INDEX

PRIMARY XML INDEX

EXTENDED INDEX

XML INDEX

列存储映射索引 (内部)

COLUMNSTORE DELETEBUFFER INDEX (内部)

COLUMNSTORE DELETEBITMAP INDEX (内部)
hobt_id bigint 索引或分区的堆或 B 树 ID。

除了返回用户定义的索引hobt_id外,还返回内部列存储索引的hobt_id。
alloc_unit_type_desc nvarchar(60) 对分配单元类型的说明:

IN_ROW_DATA

LOB_DATA

ROW_OVERFLOW_DATA

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

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

1 = 堆,或 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。
index_level tinyint 索引的当前级别。

0 表示索引叶级别、堆以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。

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

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

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

0 表示 LOB_DATA 和 ROW_OVERFLOW_DATA 分配单元。

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

对于索引的非叶级别,以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元,为 NULL。

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

对于索引的非叶级别,以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元,为 NULL。

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

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

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

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元,表示该分配单元中的总页数。
avg_page_space_used_in_percent float 所有页中使用的可用数据存储空间的平均百分比。

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

对于堆,表示 IN_ROW_DATA 分配单元中所有数据页的平均百分比。

对于 LOB_DATA 或 ROW_OVERFLOW DATA 分配单元,表示该分配单元中所有页的平均百分比。

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

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

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

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

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元,表示整个分配单元中总记录数。

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

对于 IN_ROW_DATA 分配单元中索引的非叶级别,为 0。

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

对于 IN_ROW_DATA 分配单元中索引的非叶级别,为 0。

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

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

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

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元,表示整个分配单元中的最小记录大小。

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

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

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

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元,表示整个分配单元中的最大记录大小。

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

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

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

对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元,表示整个分配单元中的平均记录大小。

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

除 IN_ROW_DATA 分配单元外,对于堆的其他所有分配单元都为 NULL。

模式 = LIMITED 时堆的 NULL。
compressed_page_count bigint 压缩页的数目。

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

对于具有聚集索引的表,compressed_page_count 值表示 PAGE 压缩的效率。
hobt_id bigint 对于列存储索引,这是用于跟踪分区内部列存储数据的行集的 ID。 行集存储为数据堆或 B 树。 它们与父列存储索引具有相同的索引 ID。 有关详细信息,请参阅 sys.internal_partitions (Transact-SQL)

如果为 NULL

适用于:SQL Server 2016 及更高版本,Azure SQL Database,Azure SQL 托管实例
column_store_delete_buffer_state tinyint 0 = NOT_APPLICABLE

1 = OPEN

2 = 清空

3 = 刷新

4 = 停用

5 = 就绪

适用于:SQL Server 2016 及更高版本,Azure SQL Database,Azure SQL 托管实例
column_store_delete_buff_state_desc 无效 - 父索引不是列存储索引。

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

清空 - 删除程序正在耗尽,但扫描程序仍使用它。

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

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

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

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

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

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

适用于:yes SQL Server 2019 (15.x) ,Azure SQL 数据库
total_inrow_version_payload_size_in_bytes bigint 此索引的 Inrow 版本记录的总大小(以字节为单位)。

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

适用于:yes SQL Server 2019 (15.x) ,Azure SQL 数据库
offrow_long_term_version_record_count bigint 被视为长期的版本记录计数。

适用于:yes SQL Server 2019 (15.x) ,Azure SQL 数据库

注意

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

注解

sys.dm_db_index_physical_stats 动态管理函数将替换 DBCC SHOWCONTIG 语句。

扫描模式

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

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

使用 LIMITED 模式时,compressed_page_count为 NULL,因为数据库引擎仅扫描 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 解释为指定所有数据库或所有对象的通配符值。

此外,在调用sys.dm_db_index_physical_stats函数之前处理OBJECT_ID函数,因此在当前数据库的上下文中进行评估,而不是 在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'AdventureWorks'), 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 AdventureWorks2012;  
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_ID 或 OBJECT_ID 时返回了有效的 ID。 例如,使用OBJECT_ID时,请指定一个三部分名称,或在 OBJECT_ID(N'AdventureWorks2012.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 SHRINKFILE 或 DBCC 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_pages 和 avg_fragmentation_in_percent 值成反比。 因此,重新生成或重新组织索引会减少碎片数量,但同时增大碎片大小。

限制和局限

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

权限

需要下列权限:

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

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

  • 使用数据库通配符 @database_id = NULL 返回有关所有数据库的信息的 VIEW SERVER STATE 权限。

授予 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'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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. 返回关于堆的信息

以下示例返回 AdventureWorks2019 数据库中堆 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'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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 兼容性级别 (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  
        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  
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 TRAN  
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) 到 SQL Server。

以下示例演示如何查询服务器代理队列进行碎片处理。

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

另请参阅

动态管理视图和函数 (Transact-SQL)
与索引相关的动态管理视图和函数 (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
系统视图 (Transact-SQL)