提高sys.dm_db_index_physical_stats的性能

Question

在维护过程中，我试图获得一个支离破碎的索引列表。但是查询非常慢，执行时间超过30分钟。我认为这是因为对sys.dm_db_index_physical_stats进行了远程扫描。

是否有任何方法可以加快以下查询：

SELECT
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS TableIndexName
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat 
    INNER JOIN sys.indexes i 
        ON i.OBJECT_ID = phystat.OBJECT_ID AND i.index_id = phystat.index_id 
WHERE 
    phystat.avg_fragmentation_in_percent > 20 
    AND OBJECT_NAME(i.OBJECT_ID) IS NOT NULL
ORDER BY phystat.avg_fragmentation_in_percent DESC

我不是DBA，可能在上面的查询中犯了一个明显的错误，或者可能有一些索引或统计数据会有所帮助？也许这只是数据库的大小(大约20 it，约140张表)。

我问这个问题，是因为我们晚上只有一个很小的窗户来维修，而且大部分时间都是这样。

Answer 1

'DETAILED'意味着对索引(或堆)中的每个页面进行全面扫描。对每个表和每个辅助索引执行此操作，结果意味着您正在进行一次完整的数据库扫描，端到端，而不是非常有效的扫描(即。例如，没有备份读取它的速度快)。时间由以下因素决定：

• 你的数据库有多大
• IO子系统读取整个数据库的速度有多快？
• 竞争IO吞吐量的负载竞争

基本上，如果您拥有的只是一根稻草( IO吞吐量)，那么需要30分钟才能喝下一个桶(您的数据库大小)。购买更快的IO，缩小数据的大小，或者使用SAMPLED扫描。

有人说..。20 is很小。30分钟阅读20 of是很长的时间。你的IO子系统速度这么慢吗？您在7200 RPM消费者1TB驱动器上部署了吗？

Answer 2

除了@Remus建议使用SAMPLED扫描之外，我不知道在您的维护窗口启动之前，这个查询还不能启动。为什么不预先填充一个包含结果的表呢？如果您在维护窗口之前15到20分钟启动此查询(假设抽样扫描需要10分钟)，并将结果填入表中，那么一旦维护窗口启动，数据就可以立即使用，并且底层数据在这段时间内不会发生太大的变化。如果您避免了对原始查询的排序和筛选，那么它也应该完成得更快。

CREATE TABLE dbo.IndexStats
(
  TableName SYSNAME,
  IndexName SYSNAME,
  Frag DECIMAL(5,2)
);
CREATE INDEX x ON dbo.IndexStats(Frag);

然后，在第一个夜间工作中(在维护窗口之前开始)：

TRUNCATE TABLE dbo.IndexStats;

INSERT dbo.IndexStats
SELECT 
  OBJECT_NAME(i.[object_id]),
  i.name,
  s.avg_fragmentation_in_percent
FROM
  sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS s 
INNER JOIN sys.indexes AS i 
ON i.[object_id] = s.[object_id]
AND i.index_id = s.index_id;

DELETE dbo.IndexStats WHERE Frag < 20
  OR TableName IS NULL;

现在，您的实际碎片整理脚本已经具备了立即进行所需的所有信息。(您甚至可以将这些作业链接在一起，或者强制上述操作，然后使用WAITFOR TIME等待维护窗口的启动时间。)

您还可以考虑使用LIMITED，看看结果如何。

Answer 3

免责声明:这些脚本已经在Server 2005/2008上进行了测试。然而，本代码和信息“如实”提供，没有明示或默示的任何类型的保证，包括但不限于默示保证或适销性和/或适合某一特定用途。与往常一样，在尝试部署到生产环境之前，在测试环境中测试这一点。既然这事不碍事了..。

我在处理指数DMVs时遇到的问题之一是，它们不能相互关联。也就是说，您不能对它们使用交叉/外部应用程序，以限制您正在针对哪些索引执行扫描。为了解决这个问题，我将物理和操作索引DMVs的包装器函数部署到我的主数据库中：

实物：

ALTER FUNCTION [dbo].[tfn_IndexPhysicalStats_select]
(
    @DatabaseID SMALLINT = 0,
    @ObjectID INT = 0,
    @IndexID INT = -1,
    @PartitionNumber INT = 0,
    @Mode NVARCHAR(20) = NULL
)
RETURNS @IndexPhysicalStats TABLE
(
    database_id SMALLINT NOT NULL,
    object_id INT NOT NULL,
    index_id INT NOT NULL,
    partition_number INT NOT NULL,
    index_type_desc NVARCHAR(60) NULL,
    alloc_unit_type_desc NVARCHAR(60) NULL,
    index_depth TINYINT NOT NULL,
    index_level TINYINT NOT NULL,
    avg_fragmentation_in_percent FLOAT NULL,
    fragment_count BIGINT NULL,
    avg_fragment_size_in_pages FLOAT NULL,
    page_count BIGINT NOT NULL,
    avg_page_space_used_in_percent FLOAT NULL,
    record_count BIGINT NULL,
    ghost_record_count BIGINT NULL,
    version_ghost_record_count BIGINT NULL,
    min_record_size_in_bytes INT NULL,
    max_record_size_in_bytes INT NULL,
    avg_record_size_in_bytes FLOAT NULL,
    forwarded_record_count BIGINT NULL
)
AS
BEGIN

    INSERT INTO @IndexPhysicalStats
    (
        database_id,
        object_id,
        index_id,
        partition_number,
        index_type_desc,
        alloc_unit_type_desc,
        index_depth,
        index_level,
        avg_fragmentation_in_percent,
        fragment_count,
        avg_fragment_size_in_pages,
        page_count,
        avg_page_space_used_in_percent,
        record_count,
        ghost_record_count,
        version_ghost_record_count,
        min_record_size_in_bytes,
        max_record_size_in_bytes,
        avg_record_size_in_bytes,
        forwarded_record_count
    )
    SELECT
        ddips.database_id,
        ddips.object_id,
        ddips.index_id,
        ddips.partition_number,
        ddips.index_type_desc,
        ddips.alloc_unit_type_desc,
        ddips.index_depth,
        ddips.index_level,
        ddips.avg_fragmentation_in_percent,
        ddips.fragment_count,
        ddips.avg_fragment_size_in_pages,
        ddips.page_count,
        ddips.avg_page_space_used_in_percent,
        ddips.record_count,
        ddips.ghost_record_count,
        ddips.version_ghost_record_count,
        ddips.min_record_size_in_bytes,
        ddips.max_record_size_in_bytes,
        ddips.avg_record_size_in_bytes,
        ddips.forwarded_record_count
    FROM sys.dm_db_index_physical_stats
    (
        @DatabaseID,
        @ObjectID,
        @IndexID,
        @PartitionNumber,
        @Mode
    ) AS ddips;

    RETURN;
END

业务：

ALTER FUNCTION [dbo].[tfn_IndexOperationalStats_select]
(
    @DatabaseID SMALLINT = 0,
    @TableID INT = 0,
    @IndexID INT = -1,
    @PartitionNumber INT = 0
)
RETURNS @IndexOperationalStats TABLE
(
    database_id SMALLINT NOT NULL,
    object_id INT NOT NULL,
    index_id INT NOT NULL,
    partition_number INT NOT NULL,
    leaf_insert_count BIGINT NULL,
    leaf_delete_count BIGINT NULL,
    leaf_update_count BIGINT NULL,
    leaf_ghost_count BIGINT NULL,
    nonleaf_insert_count BIGINT NULL,
    nonleaf_delete_count BIGINT NULL,
    nonleaf_update_count BIGINT NULL,
    leaf_allocation_count BIGINT NULL,
    nonleaf_allocation_count BIGINT NULL,
    leaf_page_merge_count BIGINT NULL,
    nonleaf_page_merge_count BIGINT NULL,
    range_scan_count BIGINT NULL,
    singleton_lookup_count BIGINT NULL,
    forwarded_fetch_count BIGINT NULL,
    lob_fetch_in_pages BIGINT NULL,
    lob_fetch_in_bytes BIGINT NULL,
    lob_orphan_create_count BIGINT NULL,
    lob_orphan_insert_count BIGINT NULL,
    row_overflow_fetch_in_pages BIGINT NULL,
    row_overflow_fetch_in_bytes BIGINT NULL,
    column_value_push_off_row_count BIGINT NULL,
    column_value_pull_in_row_count BIGINT NULL,
    row_lock_count BIGINT NULL,
    row_lock_wait_count BIGINT NULL,
    row_lock_wait_in_ms BIGINT NULL,
    page_lock_count BIGINT NULL,
    page_lock_wait_count BIGINT NULL,
    page_lock_wait_in_ms BIGINT NULL,
    index_lock_promotion_attempt_count BIGINT NULL,
    index_lock_promotion_count BIGINT NULL,
    page_latch_wait_count BIGINT NULL,
    page_latch_wait_in_ms BIGINT NULL,
    page_io_latch_wait_count BIGINT NULL,
    page_io_latch_wait_in_ms BIGINT NULL
    PRIMARY KEY CLUSTERED
    (
        database_id ASC,
        object_id ASC,
        index_id ASC,
        partition_number ASC
    )
)
AS
BEGIN
    INSERT INTO @IndexOperationalStats
    (
        database_id,
        object_id,
        index_id,
        partition_number,
        leaf_insert_count,
        leaf_delete_count,
        leaf_update_count,
        leaf_ghost_count,
        nonleaf_insert_count,
        nonleaf_delete_count,
        nonleaf_update_count,
        leaf_allocation_count,
        nonleaf_allocation_count,
        leaf_page_merge_count,
        nonleaf_page_merge_count,
        range_scan_count,
        singleton_lookup_count,
        forwarded_fetch_count,
        lob_fetch_in_pages,
        lob_fetch_in_bytes,
        lob_orphan_create_count,
        lob_orphan_insert_count,
        row_overflow_fetch_in_pages,
        row_overflow_fetch_in_bytes,
        column_value_push_off_row_count,
        column_value_pull_in_row_count,
        row_lock_count,
        row_lock_wait_count,
        row_lock_wait_in_ms,
        page_lock_count,
        page_lock_wait_count,
        page_lock_wait_in_ms,
        index_lock_promotion_attempt_count,
        index_lock_promotion_count,
        page_latch_wait_count,
        page_latch_wait_in_ms,
        page_io_latch_wait_count,
        page_io_latch_wait_in_ms
    )
    SELECT
        ddios.database_id,
        ddios.object_id,
        ddios.index_id,
        ddios.partition_number,
        ddios.leaf_insert_count,
        ddios.leaf_delete_count,
        ddios.leaf_update_count,
        ddios.leaf_ghost_count,
        ddios.nonleaf_insert_count,
        ddios.nonleaf_delete_count,
        ddios.nonleaf_update_count,
        ddios.leaf_allocation_count,
        ddios.nonleaf_allocation_count,
        ddios.leaf_page_merge_count,
        ddios.nonleaf_page_merge_count,
        ddios.range_scan_count,
        ddios.singleton_lookup_count,
        ddios.forwarded_fetch_count,
        ddios.lob_fetch_in_pages,
        ddios.lob_fetch_in_bytes,
        ddios.lob_orphan_create_count,
        ddios.lob_orphan_insert_count,
        ddios.row_overflow_fetch_in_pages,
        ddios.row_overflow_fetch_in_bytes,
        ddios.column_value_push_off_row_count,
        ddios.column_value_pull_in_row_count,
        ddios.row_lock_count,
        ddios.row_lock_wait_count,
        ddios.row_lock_wait_in_ms,
        ddios.page_lock_count,
        ddios.page_lock_wait_count,
        ddios.page_lock_wait_in_ms,
        ddios.index_lock_promotion_attempt_count,
        ddios.index_lock_promotion_count,
        ddios.page_latch_wait_count,
        ddios.page_latch_wait_in_ms,
        ddios.page_io_latch_wait_count,
        ddios.page_io_latch_wait_in_ms
    FROM sys.dm_db_index_operational_stats
    (
        @DatabaseID,
        @TableID,
        @IndexID,
        @PartitionNumber
    ) AS ddios;

    RETURN;
END

然后，我以下列方式在索引维护作业中引用此函数：

DECLARE 
    @DDL NVARCHAR(MAX);

DECLARE ddl_cursor CURSOR
FOR
    SELECT
        CONVERT(NVARCHAR(MAX), DDL.DDL) AS DDL
    FROM
    (
        SELECT
            MasterIndexes.SchemaName,
            MasterIndexes.TableName,
            MasterIndexes.IndexName,
            MasterIndexes.DatabaseID,
            MasterIndexes.ObjectID,
            MasterIndexes.IndexID,
            MasterIndexes.PartitionNumber,
            MasterIndexes.type_desc,
            MasterIndexes.is_unique,
            MasterIndexes.is_primary_key,
            MasterIndexes.is_unique_constraint,
            MasterIndexes.fill_factor,
            MasterIndexes.allow_row_locks,
            MasterIndexes.allow_page_locks,
            MasterIndexes.UpdateStatisticsIndicator,
            1 AS SortInTempDB,
            CASE
                WHEN CONVERT(VARCHAR(100), SERVERPROPERTY('edition')) LIKE 'Enterprise Edition%' THEN 1
                ELSE 0
            END AS OnlineIndicator,
            CASE
                WHEN 
                    ips.avg_fragmentation_in_percent BETWEEN CONVERT(FLOAT, 10) AND CONVERT(FLOAT, 30)
                    AND ips.page_count >= 100
                THEN
                    1
                ELSE
                    0
            END AS ReorganizationIndicator,
            CASE
                WHEN
                (
                    ips.avg_fragmentation_in_percent >= 30
                    AND ips.page_count >= 100
                )
                OR
                (
                    ips.avg_fragmentation_in_percent BETWEEN CONVERT(FLOAT, 10) AND CONVERT(FLOAT, 30)
                    AND ips.page_count < 100
                )
                THEN 
                    1
                ELSE
                    0
            END AS RebuildIndicator
        FROM
        (
            SELECT
                s.name AS SchemaName,
                t.name AS TableName,
                ix.name AS IndexName,
                DB_ID() AS DatabaseID,
                ddps.object_id AS ObjectID,
                ddps.index_id AS IndexID,
                ddps.partition_number AS PartitionNumber,
                ix.type_desc,
                ix.is_unique,   
                ix.is_primary_key,
                ix.is_unique_constraint,
                ix.fill_factor, 
                ix.allow_row_locks,
                ix.allow_page_locks,
                1 AS UpdateStatisticsIndicator  
            FROM sys.schemas AS s

                INNER JOIN sys.tables AS t
                    ON s.schema_id = t.schema_id

                    INNER JOIN sys.indexes AS ix
                        ON t.object_id = ix.object_id

                        INNER JOIN sys.dm_db_partition_stats AS ddps
                            ON ix.object_id = ddps.object_id
                            AND ix.index_id = ddps.index_id

                CROSS APPLY master.dbo.tfn_IndexOperationalStats_select
                (
                    DB_ID(),
                    t.object_id,
                    ix.index_id,
                    NULL
                ) AS ios

            WHERE
                CASE
                    WHEN ddps.row_count = 0 THEN 0
                    ELSE
                    (
                        (
                            CONVERT
                            (
                                FLOAT,
                                (
                                    ios.nonleaf_insert_count + 
                                    ios.nonleaf_update_count + 
                                    ios.leaf_insert_count + 
                                    ios.leaf_update_count
                                )
                            ) /
                            CONVERT
                            (
                                FLOAT,
                                ddps.row_count
                            )
                        ) * 100.0
                    ) 
                END >= 10.0
            AND t.is_ms_shipped = 0
            AND t.name NOT LIKE 'MSmerge%'
            AND ix.index_id > 0
        ) AS MasterIndexes

            CROSS APPLY master.dbo.tfn_IndexPhysicalStats_select
            (
                MasterIndexes.DatabaseID,
                MasterIndexes.ObjectID,
                MasterIndexes.IndexID,
                MasterIndexes.PartitionNumber,
                'SAMPLED'
            ) AS ips
    ) AS MasterIndexList    

        CROSS APPLY
        (
            SELECT          
                'ALTER INDEX ' + 
                MasterIndexList.IndexName + 
                ' ON ' + 
                MasterIndexList.SchemaName + '.' + MasterIndexList.TableName +
                ' REBUILD WITH(' + 
                'FILLFACTOR = ' + 
                    CASE
                        WHEN MasterIndexList.fill_factor = 0 THEN '100'
                        ELSE CONVERT(VARCHAR(3), MasterIndexList.fill_factor)
                    END + ', ' +
                'SORT_IN_TEMPDB = ' + 
                    CASE
                        WHEN MasterIndexList.SortInTempDB = 1 THEN 'ON'
                        ELSE 'OFF'
                    END + ', ' +
                'ONLINE = ' + 
                    CASE
                        WHEN MasterIndexList.OnlineIndicator = 1 THEN 'ON'
                        ELSE 'OFF'
                    END + ', ' + 
                'ALLOW_ROW_LOCKS = ' + 
                    CASE
                        WHEN MasterIndexList.[allow_row_locks] = 1 THEN 'ON'
                        ELSE 'OFF'
                    END + ', ' + 
                'ALLOW_PAGE_LOCKS = ' + 
                    CASE
                        WHEN MasterIndexList.[allow_page_locks] = 1 THEN 'ON'
                        ELSE 'OFF'
                    END + ');' AS [DDL],

                1 AS DDLOrdinal

            WHERE MasterIndexList.RebuildIndicator = 1

            UNION ALL
            SELECT          
                'ALTER INDEX ' + 
                MasterIndexList.IndexName + 
                ' ON ' + 
                MasterIndexList.SchemaName + '.' + MasterIndexList.TableName +
                ' REORGANIZE;' AS [DDL],

                2 AS DDLOrdinal

            WHERE MasterIndexList.ReorganizationIndicator = 1

            UNION ALL
            SELECT
                'UPDATE STATISTICS ' + 
                MasterIndexList.SchemaName + '.' + MasterIndexList.TableName + ' ' + 
                MasterIndexList.IndexName + ' ' + 
                'WITH FULLSCAN;' AS [DDL],

                3 AS DDLOrdinal

            WHERE MasterIndexList.UpdateStatisticsIndicator = 1
            AND MasterIndexList.RebuildIndicator = 0
            AND STATS_DATE(MasterIndexList.ObjectID, MasterIndexList.IndexID) <= DATEADD(hh, -20, GETDATE())
        ) AS [DDL]

    ORDER BY
        ObjectID ASC,
        IndexID ASC,
        DDLOrdinal ASC;

OPEN ddl_cursor;

FETCH NEXT FROM ddl_cursor
INTO @DDL;

WHILE @@FETCH_STATUS = 0
BEGIN

    EXECUTE sys.sp_executesql 
        @stmt = @DDL;

    FETCH NEXT FROM ddl_cursor
    INTO @DDL;
END

CLOSE ddl_cursor;
DEALLOCATE ddl_cursor;
GO

和往常一样，您的里程可能会有所不同，但是可以随意使用/修改这些脚本来满足您的需要。

那多保重,

哑光