Popraw wydajność sys.dm_db_index_physical_stats

Podczas prac konserwacyjnych próbuję uzyskać listę pofragmentowanych indeksów. Ale zapytanie jest bardzo wolne i jego wykonanie zajmuje ponad 30 minut . Myślę, że jest to spowodowane zdalnym skanowaniem sys.dm_db_index_physical_stats.

Czy istnieje sposób na przyspieszenie następującego zapytania:

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

Nie jestem DBA i może popełniam oczywisty błąd w powyższym zapytaniu, a może istnieją jakieś indeksy lub statystyki, które mogłyby pomóc? Może to tylko rozmiar bazy danych (około 20 GB z około 140 tabelami).

Pytam dlatego, że mamy bardzo małe okno do konserwacji w nocy, co zajmuje większość czasu.

'DETAILED'oznacza pełne skanowanie każdej strony w indeksie (lub sterty). Zrób to dla każdej tabeli i każdego dodatkowego indeksu, wynik oznacza, że ​​wykonujesz pełny skan bazy danych, od końca do końca, i niezbyt wydajny (tj. Nie tak szybki, jak na przykład kopia zapasowa). Czas zależy od:

• jak duża jest twoja baza danych
• jak szybko twój podsystem IO odczytuje całą bazę danych
• dodatkowe współbieżne obciążenie konkurujące o przepustowość operacji we / wy

Zasadniczo, jeśli masz tylko słomkę (przepustowość we / wy), wypicie wiadra (rozmiar bazy danych) zajmuje 30 minut. Kupuj szybsze operacje we / wy, zmniejsz rozmiar danych lub korzystaj ze SAMPLEDskanów.

Biorąc to pod uwagę ... 20 Gb jest dość małe. 30 minut na odczytanie 20 GB to dużo czasu. Czy jesteś tak powolnym podsystemem we / wy ? Czy wdrożyłeś dyski konsumenckie 1 TB o prędkości 7200 obr./min?

Oprócz zalecenia @Remus, aby użyć SAMPLEDskanowania, nie wiem, czy to zapytanie nie może się rozpocząć, dopóki nie uruchomi się okno konserwacji. Dlaczego nie wstępnie wypełnić tabelę wynikami? Jeśli rozpoczniesz tę kwerendę (powiedzmy, że skanowanie próbkowane zajmuje 10 minut) około 15-20 minut przed oknem konserwacji i umieścisz wyniki w tabeli, dane będą gotowe do użycia, jak tylko okno konserwacji się uruchomi, a w międzyczasie dane podstawowe nie zmieniłyby się tak bardzo. Jeśli unikniesz sortowania i filtrowania oryginalnego zapytania, powinno ono również zakończyć się szybciej, np

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

Następnie w pierwszej nocnej pracy (która rozpoczyna się przed oknem konserwacji):

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;

Teraz twój rzeczywisty skrypt defrag posiada już wszystkie informacje potrzebne do natychmiastowego kontynuowania. (Możesz nawet połączyć zadania razem lub zmusić powyższe do oczekiwania na czas rozpoczęcia okna konserwacji WAITFOR TIME.)

Możesz także rozważyć zabawę LIMITEDi zobaczyć, jak się to ma.

Oświadczenie: Te skrypty zostały przetestowane na SQL Server 2005/2008. Jednak ten kod i informacje są dostarczane „TAK JAK SĄ” bez jakichkolwiek gwarancji, wyraźnych lub dorozumianych, w tym między innymi dorozumianych gwarancji lub przydatności handlowej i / lub przydatności do określonego celu. Jak zawsze przetestuj to w środowisku testowym przed próbą wdrożenia w środowisku produkcyjnym. Teraz to już nie przeszkadza ...

Jednym z problemów, na jakie napotykam, mając do czynienia z indeksowymi DMV, jest to, że nie można ich skorelować. Oznacza to, że nie możesz użyć aplikacji CROSS / OUTER APPLY przeciwko nim, aby ograniczyć indeksy, przeciwko którym przeprowadzasz skanowanie. Aby obejść ten problem, wdrażam funkcję otoki dla fizycznych i operacyjnych indeksów DMV w mojej głównej bazie danych:

Fizyczny:

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

Operacyjny:

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

Następnie odnoszę się do tej funkcji w moich zadaniach konserwacji indeksu w następujący sposób:

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

Jak zawsze przebieg może się różnić, ale możesz używać / modyfikować te skrypty, aby dopasować je do swoich potrzeb.

Miłego,

Matt

Poniższy kodek działa dobrze w bazie danych ~ 185 GB.

DECLARE @dbid int

SET @dbid = DB_ID()

select o.name as ObjectName, 
       O.id as ObjectID,
       I.name as IndexName,
       I.index_id as IndexID,
       I.type,
       I.type_Desc,
       ps.avg_fragmentation_in_percent 

from sysobjects O with (nolock)
inner join sys.indexes i with (nolock) ON O.id = i.object_id 
inner join sys.dm_db_index_physical_stats (@dbid,null,null,null,'LIMITED') ps on ps.database_id = @dbid
                                                                             and ps.object_id = O.id 
                                                                             and ps.index_id = I.index_id
where xtype = 'U'
and LEFT(o.name,2) <> 'MS'
and ps.avg_fragmentation_in_percent > 10
order by o.name 

Zauważyłem, że mój czas zbierania informacji o pofragmentowanych indeksach wzrósł z 3 i pół godziny do 5 minut, wykonując statystyki aktualizacji. Zaktualizuj statystyki tabel za pomocą zadania aktualizacji statystyk Oli Hallengren i to powinno to zrobić.