Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on sys.dm_db_index_physical_stats.

Is there any way to speed up the following query:

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

I'm not a DBA and could be making an obvious mistake in the query above, or maybe there are some indexes or statistics that would help? Maybe it's just the size of the database (around 20Gb with about 140 tables).

The reason I ask is that we only have a very small window for maintenance during the night and this is taking up most of the time.

share|improve this question

3 Answers 3

up vote 10 down vote accepted

'DETAILED' implies a full scan of every single page in the index (or heap). Do this for every table and every secondary index, the result means you are doing a full database scan, end to end, and not a very efficient one (ie. not nearly as fast as backup would read it, for instance). The time is driven by:

  • how big your database is
  • how fast your IO subsytem is to read the entire database
  • aditional concurent load competing for the IO throughput

Basically, if all you have is a straw (your IO throughput) it takes 30 minutes to drink a bucket (your database size). Buy faster IO, reduce the size of your data, or use SAMPLED scans.

That being said... 20Gb is quite small. 30 minutes to read 20Gb is a lot of time. Is you IO subsystem that slow? Did you deploy on 7200 RPM consumer 1TB drives?

share|improve this answer
    
Thanks Remus, great information. It is running in a data centre on a SAN, but unfortunately it's very old hardware (5yrs+) and overdue an upgrade. This will all hopefully add weight to an ever growing argument to upgrade the hardware! –  Rob Bird Jul 11 '12 at 8:46

In addition to the recommendation by @Remus to use a SAMPLED scan, I don't know that this query can't start until your maintenance window starts. Why not pre-populate a table with the results? If you start this query (let's say a sampled scan takes 10 minutes) about 15-20 minutes before your maintenance window, and stuff the results in a table, the data would be ready to use as soon as the maintenance window starts, and the underlying data won't really have changed all that much in the meantime. If you avoid the sorting and filtering on the original query, it should complete faster as well, e.g.

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

Then in your first nightly job (which starts before your maintenance window):

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;

Now your actual defrag script already has all the information it needs to proceed immediately. (You could even chain the jobs together or force the above to then wait for your maintenance window start time using WAITFOR TIME.)

You may also consider playing with LIMITED and see how that fares.

share|improve this answer
2  
+1 for prestart, that is a neat idea –  Remus Rusanu Jul 10 '12 at 13:27
    
+1 Thanks, I love this idea, can't believe I hadn't thought of that! simple but effective. –  Rob Bird Jul 11 '12 at 8:47

Disclaimer: These scripts have been tested on SQL Server 2005/2008. However, this code and information are provided "AS IS" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties or merchantability and/or fitness for a particular purpose. As always, test this in your test environment before attempting to deploy to your production environment. Now that that's out of the way...

One of the problems I run into when dealing with the index DMVs is that they cannot be correlated. Meaning, you cannot use the CROSS/OUTER APPLY against them, in order to limit which indices you are performing scans against. In order to get around this, I deploy a wrapper function, for physical and operational index DMVs, to my master database:

Physical:

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

Operational:

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

I then reference this function in my index maintenance jobs in the following manner:

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

As always, your mileage may vary, but feel free to use/alter these scripts to fit your needs.

Have a good one,

Matt

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.