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 分区中的分配单元返回一行。

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、index_id和partition_number。NULL

你可以指定内置函数 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和partition_number。NULL

index_id | 0 |NULL |-1 |违约

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

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

partition_number |NULL |0 |违约

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

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

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

mode |NULL |违约

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

返回的表

注解

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% 的值是可以接受的。 减少碎片的所有方法（例如重新生成、重新组织或重新创建）都可用于减少这些值。 有关如何分析索引中碎片程度的详细信息，请参阅 优化索引维护以提高查询性能并减少资源消耗。

减少索引中的碎片

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

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

  重新创建聚集索引会重新分发数据并生成完整数据页。 填充度可以使用 CREATE INDEX 中的 FILLFACTOR 选项进行配置。 此方法的缺点是索引在删除和重新创建周期期间处于脱机状态，并且操作是原子的。 如果索引创建中断，则不会重新创建索引。 有关详细信息，请参阅创建索引。

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

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

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

减少堆中的碎片

若要减少堆的区碎片，请对表创建聚集索引，然后删除该索引。 在创建聚集索引时将重新分布数据。 同时会考虑数据库中可用空间的分布，从而使其尽可能优化。 然后删除聚集索引以重新创建堆时，不会移动数据，并且保持最佳位置。 有关如何执行这些操作的信息，请参阅 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. 返回有关堆的信息

以下示例将返回有关 AdventureWorks2022 数据库中 dbo.DatabaseLog 堆的所有统计信息。 由于表包含 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);

相关内容

• 系统动态管理视图
• 索引相关的动态管理视图和函数 （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 参考（数据库引擎）