go backsqlschool blogs list

Stored Procedure for rebuild or reorganize indexes in a database

by Antonios Chatzipavlis

Σε παλαιότερο μου άρθρο είχα φτιάξει κάποια scripts τα οποία μπορούν να χρησιμοποιηθούν για το indexes fragmentation. Σήμερα σας δίνω μια stored procedure την οποία την κάνω system procedure και μπορεί να είναι διαθέσιμη σε όλες τις databases, αλλά παίρνει και παραμέτρους για να καλυφτούν περισσότερες ανάγκες ανά περίπτωση

Οι παράμετροι είναι οι παρακάτω:

@page_threshold : Ο αριθμός των index pages που πρέπει να έχει ο index ώστε να γίνει rebuild / reorganize. H default τιμή της παραμέτρου 1000 index pages

@fragementation_threshold_limit : Το ποσοστό του fragmentation απο το οποίο και μετά θα γίνει το rebuild / reorganize. H default τιμή της παραμέτρου είναι 5%.

@with_log : Αν θα γίνεται log η διαδικασία στα SQL Server logs. H default τιμή της παραμέτρου είναι 1 ( να γίνεται log ) (0: να μην γίνεται log)

Επίσης θα πρέπει να αναφέρω ότι μέσα στην συγκεκριμένη stored procedure γίνονται διάφοροι έλεγχοι όπως:

  • Αν ο πίνακας έχει blob field τότε δεν γίνεται online το rebuild του index
  • Aν το fragmentation είναι πάνω από 30% γίνεται rebuild αλλιώς reorganize
  • Όλα τα rebuild γίνονται με MAXDOP=1 εφόσον είναι και ONLINE=ON, αλλιώς γίνονται με MAXDOP=2



 USE master;
 GO

 
 CREATE PROC sp_db_index_maintenance (   @page_threshold int = 1000
                                      ,  @fragementation_threshold_limit decimal(5,2) = 5.00
                                      ,  @with_log bit = 1
                                     )
 as
 BEGIN
 
    SET NOCOUNT ON;
    DECLARE idx CURSOR READ_ONLY FOR 
    WITH db_indexes as
    (
        SELECT    
                idx.name as index_name 
                ,OBJECT_SCHEMA_NAME(idx.object_id) as table_schema_name 
                ,OBJECT_NAME(idx.object_id) as table_name 
                ,S.avg_fragmentation_in_percent as fragmentation_pct
                ,CASE
                 WHEN S.avg_fragmentation_in_percent < 30.0  THEN 'REORGANIZE'
                 ELSE 'REBUILD' 
                 END as defrag_action 
                ,CASE 
                 WHEN (SELECT COUNT(*) FROM sys.partitions AS p WHERE (p.object_id = idx.object_id) AND (p.index_id = s.index_id)) > 1 THEN 1
                 ELSE 0
                 END  as has_partitions
                ,s.partition_number
                ,idx.type as index_type
                ,CASE 
                 WHEN SERVERPROPERTY('EditionID') in (1804890536,    -- Enterprise
                                                      1872460670,    -- Enterprise Edition: Core-based Licensing
                                                      610778273,     -- Enterprise Evaluation
                                                      -2117995310)   -- Developer
                        THEN 1
                 ELSE 0
                 END as support_online
                ,CASE 
                 WHEN (select count(*) from INFORMATION_SCHEMA.COLUMNS
                                        WHERE TABLE_SCHEMA =  OBJECT_SCHEMA_NAME(idx.object_id) AND TABLE_NAME = OBJECT_NAME(idx.object_id)
                                        AND 
                                        (DATA_TYPE in ( 'text','ntext','image','binary','varbinary','xml')
                                         OR
                                         CHARACTER_MAXIMUM_LENGTH=-1) ) > 0
                       THEN 1
                 ELSE 0
                 END as has_blobs   
        FROM    sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'LIMITED') AS s
        JOIN    sys.indexes AS idx 
                ON idx.OBJECT_ID = s.OBJECT_ID AND idx.index_id = s.index_id
        WHERE   
                s.page_count > @page_threshold
                AND 
                s.avg_fragmentation_in_percent > @fragementation_threshold_limit
                AND
                idx.type in (1,2) 
                AND
                s.alloc_unit_type_desc='IN_ROW_DATA'
    )
    select  'ALTER INDEX ' + QUOTENAME(index_name) 
            + ' ON ' 
            + QUOTENAME(table_schema_name) 
            + '.' 
            + QUOTENAME(table_name)
            + ' ' + defrag_action + ' '
            + CASE has_partitions
              WHEN 1 THEN ' PARTITION = ' + CAST(partition_number AS nvarchar(10))
              ELSE ''
              END
            + CASE 
              WHEN defrag_action = 'REBUILD' AND support_online=1 AND has_blobs = 0 THEN ' WITH ( MAXDOP = 1 , ONLINE = ON ) '
              WHEN defrag_action = 'REBUILD' AND ( support_online=0  OR has_blobs = 1 ) THEN ' WITH ( MAXDOP = 2 ) '
              ELSE ''
              END 
            + ';' as idx

    from db_indexes
    ORDER BY table_schema_name , table_name , index_type , partition_number



    DECLARE @msg nvarchar(max);
    DECLARE @stm nvarchar(max);
    OPEN idx

    FETCH NEXT FROM idx INTO @stm
    WHILE (@@fetch_status <> -1)
    BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
            if (@with_log = 1 )
            begin
                SET @msg = 'Starting '+ @stm  
                RAISERROR (@msg,10,1) WITH LOG;
            end

            EXEC (@stm); 

            if (@with_log = 1 )
            begin
                SET @msg = 'Finished '+ @stm  
                RAISERROR (@msg,10,1) WITH LOG;
            end
        END
        FETCH NEXT FROM idx INTO @stm
    END

    CLOSE idx;

    DEALLOCATE idx;
END
GO

USE master;
GO 
EXEC sp_ms_marksystemobject 'sp_db_index_maintenance';
GO


//antonch

Ημερομηνία: 18 May 2017 14:10
Αξιολόγηση: ( 2 )
Κατηγορίες:
Share it:

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


newsletter subscription

Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
PASS chapter logo
Official Professional Association for SQL Server (PASS) chapter for Greece
Join to PASS