Αυτοματοποιημένη διαχείριση του 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.


Αλλαγές στη διαχείριση των indexes


Άλλη μια αλλαγή στο νέο SQL Server είναι ο τρόπος με τον οποίο μπορούμε να διαχειριστούμε τα indexes. Πλέον έχει προστεθεί functionality στην εντολή ALTER INDEX και δεν είναι υποχρεωτικό να χρησιμοποιούμε τα DBCC statements. Έτσι, μπορούμε να κάνουμε τα παρακάτω:

  • Disable ένα index
    ALTER INDEX <index_name> ΟΝ <table_name> DISABLE
  • Rebuild ένα index
    ALTER INDEX <index_name> ΟΝ <table_name> REBUILD
    Αυτό αντιστοιχεί με το DBCC DBREINDEX και πέραν του προφανή λόγου που χρησιμοποιείται κάνει επίσης enable ένα index που έχουμε κάνει disable με το προηγούμενο statement (Λογικό αυτό γιατί το disabled index έχει «μείνει» στα παλιά data).
  • Reorganize index
    ALTER INDEX <index_name> ΟΝ <table_name> REORGANIZE
    Αυτό αντιστοιχεί με το DBCC INDEXDEFRAG δηλαδή είναι on-line operation (όπερ σημαίνει ότι δεν γίνονται locks που να επηρεάζουν την ομαλή λειτουργία του πίνακα) όμως παίζει μόνο στο leaf-level του index.

Επίσης, κατά το ALTER INDEX και CREATE INDEX υπάρχουν και κάποια index options όπως ONLINE = {ON | OFF}, ALLOW_ROW_LOCKS
= {ON | OFF}, ALLOW_PAGE_LOCKS = {ON | OFF} και MAXDOP = number_of_processors

Σε ότι αφορά τα indexes, μια αλλαγή που έχει να κάνει με το performance είναι ότι πλέον μπορούμε να συμπεριλάβουμε σε ένα nonclustered index και πεδία που δεν είναι κλειδιά. Μάλιστα, αυτά τα πεδία δεν προσμετράνε στον περιορισμό των 16 πεδίων ανά index. Ουσιαστικά, αυτό που γίνεται είναι να «ανεβαίνουν» τα πεδία από τα data pages του πίνακα, στα leaf-pages του index και έτσι, μπορούμε να δημιουργήσουμε ευκολότερα covering indexes. Ο τρόπος για να κάνουμε τα προηγούμενα είναι στο CREATE INDEX statement να προσθέσουμε στο τέλος ένα INCLUDE με τα πεδία που μας ενδιαφέρουν.

Τέλος, μπορούμε να δημιουργήσουμε και partioned indexes ακολουθώντας σχεδόν την ίδια διαδικασία με τα partitioned tables.