sys.dm_db_index_physical_stats (Transact-SQL)
适用于:SQL Server Azure 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)。
如果在托管可用性组可读次要副本的服务器实例上查询sys.dm_db_index_physical_stats
,可能会遇到REDO
阻塞性问题。 这是因为此动态管理视图获取IS
指定用户表或视图上的锁,该锁可以通过线程X
阻止REDO
该用户表或视图上的锁的请求。
语法
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较小。 有效输入是数据库、NULL、0 或 DEFAULT 的 ID。 默认值为 0。 NULL、0 和 DEFAULT 是此上下文中的等效值。
指定 NULL 以返回 SQL Server 实例中的所有数据库的信息。 如果为 database_id指定 NULL,则还必须为 object_id、 index_id和 partition_number指定 NULL。
你可以指定内置函数 DB_ID。 如果在不指定数据库名称的情况下使用 DB_ID
,则当前数据库的兼容级别必须是 90 或更高。
object_id |NULL |0 |违约
索引所依据的表或视图的对象 ID。 object_id为 int。
有效输入是表和视图、NULL、0 或 DEFAULT 的 ID。 默认值为 0。 NULL、0 和 DEFAULT 是此上下文中的等效值。 从 SQL Server 2016(13.x)起,有效输入还包括 Service Broker 队列名称或队列内部表名称。 应用默认参数(即所有对象、所有索引等),所有队列的碎片信息将包含在结果集中。
指定 NULL 可返回指定数据库中的所有表和视图的信息。 如果为 object_id指定 NULL,则还必须为 index_id 和 partition_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。
mode |NULL |违约
模式的名称。 模式 指定用于获取统计信息的扫描级别。 模式 为 sysname。 有效输入为 DEFAULT、NULL、LIMITED、SAMPLED 或 DETAILED。 默认值 (NULL) 为 LIMITED。
返回的表
列名称 | 数据类型 | 描述 |
---|---|---|
database_id | smallint | 表或视图的数据库 ID。 在 Azure SQL 数据库中,这些值在单一数据库或弹性池中是唯一的,但在逻辑服务器中不是唯一的。 |
object_id | int | 索引位于的表或视图的对象 ID。 |
index_id | int | 索引的索引 ID。 0 = 堆。 |
partition_number | int | 所属对象内从 1 开始的分区号;表、视图或索引。 1 = 未分区的索引或堆。 |
index_type_desc | nvarchar(60) | 索引类型的说明: - 堆 - 聚集索引 - NONCLUSTERED INDEX - PRIMARY XML INDEX - 扩展索引 - XML 索引 - 列存储映射索引(内部) - 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 单元包含存储在文本、ntext、image、varchar(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 | 索引的当前级别。 对于索引叶级别、堆和 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元,为 0。大于 0 的值表示非叶索引级别。 index_level 是索引根级别的最高级别。 仅当 mode = 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 | 索引或数据页的总数。 对于索引,分配单元中 B 树 IN_ROW_DATA 当前级别的索引页总数。对于堆,分配单元中的数据 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 记录总数。注意: 对于堆,从此函数返回的记录数可能与通过对堆运行 a SELECT COUNT(*) 返回的行数不匹配。 这是因为一行可能包含多个记录。 例如,在某些更新情况下,单个堆行可能由于更新操作而包含一条前推记录和一条被前推记录。 此外,大多数大型 LOB 行将拆分为存储中的 LOB_DATA 多个记录。对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单位,完整的分配单元中的记录总数。模式 = LIMITED 时为 NULL。 |
ghost_record_count | bigint | 分配单元中将被虚影清除任务删除的虚影记录数。 0 表示分配单元中 IN_ROW_DATA 索引的非叶级别。模式 = LIMITED 时为 NULL。 |
version_ghost_record_count | bigint | 由分配单元中未完成的快照隔离事务保留的虚影记录数。 0 表示分配单元中 IN_ROW_DATA 索引的非叶级别。模式 = 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 | 最大记录大小(字节)。 对于索引,最大记录大小适用于分配单元中 B 树的 IN_ROW_DATA 当前级别。对于堆,分配单元中 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 压缩的有效性。 |
columnstore_delete_buffer_state | tinyint | 0 = NOT_APPLICABLE 1 = OPEN 2 = 清空 3 = 刷新 4 = 停用 5 = 就绪 适用于:SQL Server 2016(13.x)及更高版本、Azure SQL 数据库和Azure SQL 托管实例 |
columnstore_delete_buffer_state_desc | nvarchar(60) | 无效 - 父索引不是列存储索引。 OPEN - 删除器和扫描程序使用此功能。 清空 - 删除器正在耗尽,但扫描程序仍然使用它。 FLUSHING - 缓冲区已关闭,缓冲区中的行正在写入删除位图。 正在停用 - 关闭的删除缓冲区中的行已写入删除位图,但缓冲区尚未截断,因为扫描程序仍在使用它。 新扫描程序不需要使用停用的缓冲区,因为打开的缓冲区就足够了。 READY - 此删除缓冲区可供使用。 适用于:SQL Server 2016(13.x)及更高版本、Azure SQL 数据库和Azure SQL 托管实例 |
version_record_count | bigint | 这是在此索引中维护的行版本记录的计数。 这些行版本由 加速数据库恢复 功能维护。 适用于:SQL Server 2019 (15.x) 及更高版本和 Azure SQL 数据库 |
inrow_version_record_count | bigint | 用于快速检索的数据行中保留的 ADR 版本记录计数。 适用于:SQL Server 2019 (15.x) 及更高版本和 Azure SQL 数据库 |
inrow_diff_version_record_count | bigint | ADR 版本记录的计数以基本版本的差异形式保存。 适用于:SQL Server 2019 (15.x) 及更高版本和 Azure SQL 数据库 |
total_inrow_version_payload_size_in_bytes | bigint | 此索引的行内版本记录的总大小(以字节为单位)。 适用于:SQL Server 2019 (15.x) 及更高版本和 Azure SQL 数据库 |
offrow_regular_version_record_count | bigint | 在原始数据行之外保留的版本记录计数。 适用于:SQL Server 2019 (15.x) 及更高版本和 Azure SQL 数据库 |
offrow_long_term_version_record_count | bigint | 长期考虑的版本记录计数。 适用于:SQL Server 2019 (15.x) 及更高版本和 Azure SQL 数据库 |
注意
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_ID和OBJECT_ID来指定database_id和object_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_ID
OBJECT_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 SHRINKFILE
DBCC SHRINKDATABASE
可能会引发碎片。 因此,如果必须执行收缩操作,则应在删除碎片之前进行。
减少堆中的碎片
若要减少堆的区碎片,请对表创建聚集索引,然后删除该索引。 在创建聚集索引时将重新分布数据。 同时会考虑数据库中可用空间的分布,从而使其尽可能优化。 然后删除聚集索引以重新创建堆时,不会移动数据,并且保持最佳位置。 有关如何执行这些操作的信息,请参阅 CREATE INDEX 和 DROP 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 权限。
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 兼容性级别(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);