sys.dm_db_index_physical_stats (Transact-SQL)

项目
10/04/2024

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例

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

注意

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

sys.dm_db_index_physical_stats 不返回有关内存优化索引的信息。有关内存优化索引使用的信息，请参阅 sys.dm_db_xtp_index_stats。

如果在托管可用性组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_id较小。有效输入是数据库、NULL或 0DEFAULT. 默认为 0。 NULL、 0和 DEFAULT 在此上下文中是等效的值。

指定 NULL 为 SQL Server 实例中的所有数据库返回信息。如果指定database_id，则还必须NULL指定object_id、NULL和partition_number。

你可以指定内置函数 DB_ID。在不使用指定数据库名称的情况下使用 DB_ID 时，当前数据库的兼容级别必须 90 更大。

object_id |NULL |0 |违约

索引所依据的表或视图的对象 ID。 object_id为 int。有效输入是表和视图的 ID、NULL或0DEFAULT。默认为 0。 NULL、 0和 DEFAULT 在此上下文中是等效的值。

在 SQL Server 2016（13.x）及更高版本中，有效输入还包括 Service Broker 队列名称或队列内部表名称。应用默认参数（即所有对象、所有索引等），所有队列的碎片信息将包含在结果集中。

指定 NULL 以返回指定数据库中所有表和视图的信息。如果指定object_id，则还必须指定NULLindex_id和NULL。

index_id | 0 |NULL |-1 |违约

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

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

partition_number |NULL |0 |违约

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

指定 NULL 返回拥有对象的所有分区的信息。

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

mode |NULL |违约

模式的名称。模式指定用于获取统计信息的扫描级别。模式为 sysname。有效输入为DEFAULT、、NULLLIMITED、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)	索引类型的说明： - `HEAP` - `CLUSTERED INDEX` - `NONCLUSTERED INDEX` - `PRIMARY XML INDEX` - `EXTENDED INDEX` - `XML INDEX` - `COLUMNSTORE MAPPING INDEX` （内部） - `COLUMNSTORE DELETEBUFFER INDEX` （内部） - `COLUMNSTORE DELETEBITMAP INDEX` （内部）
`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 的列中的数据。有关详细信息，请参阅数据类型。分配`ROW_OVERFLOW_DATA`单元包含存储在 varchar（n）、nvarchar（*n）、varbinary（n）和sql_variant的列中的数据，这些列*被推送到行外。
`index_depth`	tinyint	索引级别数。 `1`= 堆或`LOB_DATAROW_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` 分配单位。 `NULL`当模式`SAMPLED`
`fragment_count`	bigint	分配单元的 `IN_ROW_DATA` 叶级别中的片段数。有关片段的详细信息，请参阅 “备注”。 `NULL`用于索引的非叶级别或`LOB_DATAROW_OVERFLOW_DATA`分配单元。 `NULL`当模式`SAMPLED`
`avg_fragment_size_in_pages`	float	分配单元的叶级别中一个 `IN_ROW_DATA` 片段中的平均页数。 `NULL`用于索引的非叶级别或`LOB_DATAROW_OVERFLOW_DATA`分配单元。 `NULL`当模式`SAMPLED`
`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` 分配单位，分配单元中所有页面的平均值。 `NULL`模式`LIMITED`
`record_count`	bigint	总记录数。对于索引，记录总数适用于分配单元中 `IN_ROW_DATA` B 树的当前级别。对于堆，分配单元中的 `IN_ROW_DATA` 记录总数。注意：对于堆，从此函数返回的记录数可能与通过对堆运行 a `SELECT COUNT(*)` 返回的行数不匹配。这是因为一行可以包含多个记录。例如，在某些更新情况下，由于更新操作，单个堆行可能具有转发记录和转发记录。此外，大多数大型 LOB 行将拆分为存储中的 `LOB_DATA` 多个记录。对于 `LOB_DATA` 或 `ROW_OVERFLOW_DATA` 分配单位，完整的分配单元中的记录总数。 `NULL`模式`LIMITED`
`ghost_record_count`	bigint	分配单元中将被虚影清除任务删除的虚影记录数。 `0` 用于分配单元中 `IN_ROW_DATA` 索引的非叶级别。 `NULL`模式`LIMITED`
`version_ghost_record_count`	bigint	由分配单元中未完成的快照隔离事务保留的虚影记录数。 `0` 用于分配单元中 `IN_ROW_DATA` 索引的非叶级别。 `NULL`模式`LIMITED`
`min_record_size_in_bytes`	int	最小记录大小（字节）。对于索引，最小记录大小适用于分配单元中 `IN_ROW_DATA` B 树的当前级别。对于堆，分配单元中的 `IN_ROW_DATA` 最小记录大小。对于 `LOB_DATA` 或 `ROW_OVERFLOW_DATA` 分配单位，完整分配单元中的最小记录大小。 `NULL`模式`LIMITED`
`max_record_size_in_bytes`	int	最大记录大小（字节）。对于索引，最大记录大小适用于分配单元中 B 树的 `IN_ROW_DATA` 当前级别。对于堆，分配单元中 `IN_ROW_DATA` 的最大记录大小。对于 `LOB_DATA` 或 `ROW_OVERFLOW_DATA` 分配单位，完整分配单元中的最大记录大小。 `NULL`模式`LIMITED`
`avg_record_size_in_bytes`	float	平均记录大小（字节）。对于索引，平均记录大小适用于分配单元中 `IN_ROW_DATA` B 树的当前级别。对于堆，分配单元中的 `IN_ROW_DATA` 平均记录大小。对于 `LOB_DATA` 或 `ROW_OVERFLOW_DATA` 分配单位，完整的分配单元中的平均记录大小。 `NULL`模式`LIMITED`
`forwarded_record_count`	bigint	堆中具有指向其他数据位置的指针的记录数。（在更新期间，当没有足够的空间将新行存储在原始位置时，会出现此状态。 `NULL` 对于堆的分配单元以外的 `IN_ROW_DATA` 任何分配单元。 `NULL`当模式`LIMITED`
`compressed_page_count`	bigint	压缩页的数目。对于堆，不会压缩新分配的页面 `PAGE` 。堆 `PAGE` 在两个特殊条件下进行压缩：在批量导入数据或重新生成堆时。导致页面分配的典型 DML 操作不会 `PAGE` 压缩。当值大于所需的阈值时 `compressed_page_count` 重新生成堆。对于具有聚集索引的表， `compressed_page_count` 该值指示压缩的有效性 `PAGE` 。
`hobt_id`	bigint	索引或分区的堆或 B 树 ID。对于列存储索引，这是跟踪分区的内部列存储数据的行集的 ID。行集存储为数据堆或 B 树。它们与父列存储索引具有相同的索引 ID。有关详细信息，请参阅 sys.internal_partitions。
`columnstore_delete_buffer_state`	tinyint	`0` = `NOT_APPLICABLE` `1` = `OPEN` `2` = `DRAINING` `3` = `FLUSHING` `4` = `RETIRING` `5` = `READY` 适用于：SQL Server 2016（13.x）及更高版本、Azure SQL 数据库和Azure SQL 托管实例
`columnstore_delete_buffer_state_desc`	nvarchar(60)	`NOT VALID` - 父索引不是列存储索引。 `OPEN` - 删除器和扫描程序使用此代码。 `DRAINING` - 删除器正在耗尽，但扫描程序仍然使用它。 `FLUSHING` - 缓冲区已关闭，缓冲区中的行正在写入删除位图。 `RETIRING` - 关闭的删除缓冲区中的行已写入删除位图，但缓冲区尚未截断，因为扫描程序仍在使用它。新扫描程序不需要使用停用的缓冲区，因为打开的缓冲区就足够了。 `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 数据库

注意

注解

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

扫描模式

函数的执行模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。模式指定为 LIMITED， SAMPLED或 DETAILED。该函数遍历分配单元的页链，这些分配单元构成表或索引的指定分区。 sys.dm_db_index_physical_stats 不需要意向共享表锁（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 模式扫描所有页面并返回所有统计信息。

模式逐渐变慢LIMITEDDETAILED，因为每个模式都会执行更多的工作。若要快速测量表或索引的大小或碎片级别，请使用模式 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_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% 的值是可以接受的。减少碎片的所有方法（例如重新生成、重新组织或重新创建）都可用于减少这些值。有关如何分析索引中碎片程度的详细信息，请参阅优化索引维护以提高查询性能并减少资源消耗。

减少索引中的碎片

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

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

重新创建聚集索引会重新分发数据并生成完整数据页。填充度可以使用 FILLFACTOR 中的 CREATE INDEX 选项进行配置。此方法的缺点是索引在删除和重新创建周期期间处于脱机状态，并且操作是原子的。如果索引创建中断，则不会重新创建索引。有关详细信息，请参阅创建索引。
使用 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 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。

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

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

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. 返回有关堆的信息

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

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 作为第一个参数。

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

通过

sys.dm_db_index_physical_stats (Transact-SQL)

语法

参数

database_id |NULL |0 |违约

object_id |NULL |0 |违约

index_id | 0 |NULL |-1 |违约

partition_number |NULL |0 |违约

mode |NULL |违约

返回的表

注解

扫描模式

使用系统函数指定参数值

最佳做法

检测碎片

逻辑碎片

盘区碎片

减少索引中的碎片

减少堆中的碎片

压缩大型对象数据

评估磁盘空间使用情况

评估索引片段

限制

权限

示例

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

B. 返回有关堆的信息

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

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

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

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

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

反馈

其他资源

通过

sys.dm_db_index_physical_stats (Transact-SQL)

语法

参数

database_id |NULL |0 |违约

object_id |NULL |0 |违约

index_id | 0 |NULL |-1 |违约

partition_number |NULL |0 |违约

mode |NULL |违约

返回的表

注解

扫描模式

使用系统函数指定参数值

最佳做法

检测碎片

逻辑碎片

盘区碎片

减少索引中的碎片

减少堆中的碎片

压缩大型对象数据

评估磁盘空间使用情况

评估索引片段

限制

权限

示例

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

B. 返回有关堆的信息

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

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

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

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

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

相关内容

反馈

其他资源