sqlschool.gr logo

articles

Articles of SQLschool.gr Team

Stored Procedure for rebuild or reorganize indexes in a database

Antonios Chatzipavlis
Thursday 18 May 2017

Σε παλαιότερο μου άρθρο είχα φτιάξει κάποια 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

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.

Episode

Transparent Data Encryption (TDE)

image

More Episodes...

Tip

Refresh Intellisence in SSMS

Για να κάνουμε refresh το intellisence μέσα στο SSMS αρκεί να πατήσουμε Ctrl+Shift+R

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn


sqlschool.gr © 2010-2023 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.