Αυτοματοποιημένη διαχείριση του fragmentation στον SQL Server

Ένα από τα βασικά aspects της διαχείρισης σε μία βάση του SQL Server είναι η συντήρηση των Indexes των πινάκων. Το πρόβλημα προκύπτει από το γεγονός ότι σε πολλές περιπτώσεις το OLTP σύστημα χρησιμοποιείται και για reporting σκοπούς, πράγμα που σημαίνει ότι πρέπει να υπάρχουν αρκετά indexes. Τα indexes είναι καλά για να επιστρέφουν πληροφορίες αλλά αποτελούν πρόβλημα όταν γίνονται αρκετά write operations γιατί παρουσιάζεται το φαινόμενο του fragmentation. Έτσι λοιπόν, χρειάζεται ανά τακτά χρονικά διαστήματα να κάνουμε reindex ή defrag τα indexes. Για περισσότερες πληροφορίες, ρίξτε μια ματιά σε αυτό το εξαιρετικό άρθρο: SQL Server Index Fragmentation and Its Resolution και μετά συνεχίστε εδώ.

Ωραία, τώρα που ξέρετε το πως ανιχνεύουμε το fragmentation το επόμενο πρόβλημα που πρέπει να λύσουμε είναι το πως αυτοματοποιούμε τη διαδικασία. Το ζητούμενο είναι να μην χρειάζεται να δώσουμε χειροκίνητα όλα αυτά τα DBCC SHOWCONTIG και να ξεκινήσουμε κατόπιν τα reindex/index defrag.

Στον SQL Server 2005, υπάρχει ένα dynamic management function που ονομάζεται sys.dm_db_index_physical_stats. Όταν κάνουμε SELECT σε αυτό το function, επιστρέφονται παρόμοια δεδομένα με αυτά που επιστρέφει η DBCC SHOWCONTIG WITH TABLERESULTS, NO_INFOMSGS, δηλαδή το output είναι σε πινακοειδή μορφή, κατάλληλο για να το ρίξουμε σε κάποιον πίνακα.
Στο msdn, στα παραδείγματα χρήσης του sys.dm_db_index_physical_stats έχει το παράδειγμα D το οποίο είναι ένα πάρα πολύ καλό ολοκληρωμένο script που τρέχει την sys.dm_db_index_physical_stats και κατόπιν κάνει generate και execute όλα τα απαραίτητα index rebuild/index defrag βάσει των αποτελεσμάτων της. Μερικά σημεία που αξίζει να παρατηρήσουμε:

Το βασικό query είναι αυτό:

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

Και μας επιστρέφει όλα τα cluster indexes με πάνω από 10% logical fragmentation. Κατόπιν, χτίζει έναν cursor πάνω στα προηγούμενα αποτελέσματα και χρησιμοποιεί για κάθε εγγραφή του #work_to_do ένα IF βάσει του οποίου αποφασίζει βάσει του πόσο fragmented είναι το index αν θα κάνει defrag ή rebuild. Μπορείτε να αλλάξετε το παραπάνω ώστε να γράφει τα αποτελέσματα σε έναν μόνιμο πίνακα ο οποίος θα έχει ένα extra πεδίο ημερομηνίας που θα παίρνει default τιμή με GETDATE() ώστε να έχετε ένα lineage του fragmentation των indexes σε περίπτωση που θέλετε να κάνετε πιο advanced troubleshooting.

        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;

Το μόνο που έχετε να κάνετε είναι να το προσαρμόσετε στις ανάγκες σας (αυτά που λέγαμε περί reporting vs OLTP) και κατόπιν να προγραμματίσετε ένα job που θα το εκτελεί ανά τακτά χρονικά διαστήματα!

Στον SQL Server 2000 δεν υπάρχει η sys.dm_db_index_physical_stats οπότε θα πρέπει να χρησιμοποιήσουμε τη DBCC SHOWCONTIG και να αλλάξουμε λίγο τo script…

Αρχικά θα φτιάξουμε τον πίνακα που θα κρατήσει τα αποτελέσματα της DBCC SHOWCONTIG.

CREATE TABLE SHOWCONTIG (
    ObjectName CHAR(255),
    ObjectId INT,
    IndexName CHAR(255),
    IndexId INT,
    Lvl INT,
    CountPages INT,
    CountRows INT,
    MinRecSize INT,
    MaxRecSize INT,
    AvgRecSize INT,
    ForRecCount INT,
    Extents INT,
    ExtentSwitches INT,
    AvgFreeBytes INT,
    AvgPageDensity INT,
    ScanDensity DECIMAL,
    BestCount INT,
    ActualCount INT,
    LogicalFrag DECIMAL,
    ExtentFrag DECIMAL,
    DateOfCount DATETIME DEFAULT ( GETDATE() ) )

Κατόπιν, χρειαζόμαστε μια SP που να κάνει ό,τι περίπου κάνει το query που είδαμε παραπάνω:

CREATE PROCEDURE FillShowContigTable AS
declare @RETURN_VALUE int

   DECLARE @command nvarchar(2000)

    SET @command = 'INSERT  INTO SHOWCONTIG (
        ObjectName,
        ObjectId,
        IndexName,
        IndexId,
        Lvl,
        CountPages,
        CountRows,
        MinRecSize,
        MaxRecSize,
        AvgRecSize,
        ForRecCount,
        Extents,
        ExtentSwitches,
        AvgFreeBytes,
        AvgPageDensity,
        ScanDensity,
        BestCount,
        ActualCount,
        LogicalFrag,
        ExtentFrag )
        EXEC (''DBCC SHOWCONTIG ("?") WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS'')'

exec @RETURN_VALUE = sp_MSforeachtable @command1 = @command

Το κόλπο εδώ είναι η undocumented sp_Msforeachtable που θα εκτελέσει το INSERT INTO μία φορά για κάθε πίνακα της βάσης.

Οπότε το script του MSDNμετατρέπεται ως εξής (έχω κάνει την υλοποίηση που λέγαμε, με το DATETIME πεδίο):

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @indexes bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
EXEC FillShowContigTable
-- Declare the cursor for the list of indexes to be processed.
DECLARE indexes CURSOR FOR SELECT ObjectID [object_id], IndexID index_id, LogicalFrag frag FROM SHOWCONTIG
WHERE DATEDIFF(day, DateOfCount, getdate()) = 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM indexes
           INTO @objectid, @indexid, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
   SET @command = 'DBCC DBREINDEX (''' + @schemaname + N'.' + @objectname + ''', ' + @indexname + ',80)';
        IF @frag >= 30.0
            SET @command = 'DBCC INDEXDEFRAG (' + DB_NAME() + ', ''' + @schemaname + N'.' + @objectname + ''', ' + @indexname + ')';
  EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

Οι αλλαγές που χρειάστηκαν να γίνουν είναι:

• Αλλαγή του SELECT ώστε να παίζει πάνω στον πίνακα SHOWCONTIG και να φιλτράρει ως προς την ημερομηνία για να παίρνει μόνο τις μετρήσεις της ίδιας μέρας
• Αφαίρεση των σχετικών με partitions γιατί στον SQL Server 2000 δεν υποστηρίζονται partiotioned indexes.
• Αλλαγή των ALTER INDEX … REORGANIZE και ALTER INDEX … REBUILD με τα αντίστοιχα DBCC.

Το script αυτό αποτελεί μια καλή βάση για να ξεκινήσετε και να το βελτιώσετε ώστε να γίνει πιο ευέλικτο ως προς το πότε θα κάνει reindex/defrag κάποιον πίνακα με το να χρησιμοποιεί διαφορετικές ρυθμίσεις (αποθηκευμένες σε κάποιο "settings" πίνακα) για κάθε πίνακα ανάλογα με τη χρήση του όπως επίσης και για το fillfactor ή να προστεθεί και ο έλεγχος για physical fragmentation.