Αναμφίβολα όλοι γνωρίζουμε την αξία που έχουν οι indexes σε ένα πίνακα και την ευεργετική (στις περισσότερες περιπτώσεις) επίδραση τους στα queries που γίνονται σε αυτούς.

Επίσης όλοι ξέρουν ότι το index fragmentation είναι ένας παράγοντας που έχει αρνητική επίδραση στην αξία των index και όλοι γνωρίζουν πως σε OLTP συστήματα θα πρέπει σε τακτά χρονικά διαστήματα να κάνουμε index defragmentation.

Αυτό όμως που έχω παρατηρήσει είναι ότι ενώ σχεδόν όλοι ξέρουν πώς να βρουν το τι fragmentation έχει ένας index είτε μέσα από τον SSMS είναι ρωτώντας την DMV προβληματίζονται στην κατανόηση του τι είναι internal και τι external fragmentation. (Για όλα αυτά μπορείτε να δείτε το αντίστοιχο SQL Saturday Night)

Έχω ακούσει διάφορες εξηγήσεις τον τελευταίο καιρό και διάφορες απόψεις που δεν ήταν όμως όλες σωστές και για αυτό γράφω αυτό το post για να μπούνε τα πράγματα στην θέση στους.

External Fragmentation

Τέτοιου είδους fragmentation έχουμε όταν τα index pages που είναι στο leaf level δεν είναι σε λογική σειρά. Τι σημαίνει όμως αυτό;

Όταν δημιουργώ ένα index τα index keys μπαίνουν σε σελίδες που έχουν λογική σειρά. Για παράδειγμα έστω ότι σε ένα πίνακα έχω ένα πεδίο τύπου integer το οποίο και έχω κάνει index.

Για την ευκολία στην απεικόνιση που ακολουθεί θεωρούμε ότι σε κάθε index page στο leaf χωράνε τέσσερα index keys. Έτσι όταν φτιάχνουμε για πρώτη φορά τον index θα έχουμε το παρακάτω index structure. Οι αριθμοί των σελίδων είναι τυχαίοι

PAGE 123 : 1 | 2 | 4 | 6
PAGE 124 : 8 | 10 | 12 | 13
PAGE 125 : 14 | 20 | 24 | 26

Αν μετά από την δημιουργία του index γίνει ένα insert που έρχεται να βάλει το 3 τότε θα έχω την εξής δομή

PAGE 123 :   1 | 2 | 3 | κενο
PAGE 124 :   8 | 10 | 12 | 13
PAGE 125 : 14 | 20 | 24 | 26
PAGE 130 :  4  | 6 | κενο | κενό

Στις περιπτώσεις που έχω ερωτήματα που είναι τυχαία πχ ψάχνω το 4 ή το 6 ή κάποιο άλλο δεν θα έχω κάποιο πρόβλημα. Όμως όταν ψάχνω με τέτοιο τρόπο που πρέπει να γυρίσει ταξινομημένο (ordered) αποτέλεσμα τότε ναι έχω πρόβλημα καθώς θα έχω extra page switch όπως το λέμε καθώς θα πρέπει να διαβαστούν τα περιεχόμενα της σελίδας 130. Φανταστείτε ότι αυτό δεν θα είναι μια σελίδα αλλά με περισσότερες.

Internal Fragmentation

Τέτοιου είδος fragmentation έχουμε όταν δεν χρησιμοποιείται βέλτιστα ο χώρος που έχουμε στα index pages. Θα πρέπει να επισημάνω ότι πριν αποφανθούμε για αυτό θα πρέπει να δούμε τι fill factor έχουμε ορίσει στον index αυτό. Να εξηγήσουμε όμως λιγάκι καλύτερα αυτό χρησιμοποιώντας τα προηγούμενο παράδειγμα. Έστω λοιπόν ότι φτιάξαμε τον index και έχουμε την αρχική μας δομή

PAGE 123 : 1 | 2 | 4 | 6
PAGE 124 : 8 | 10 | 12 | 13
PAGE 125 : 14 | 20 | 24 | 26

Κάνουμε μερικές διαγραφές σε εγγραφές και έχουμε

PAGE 123 : 1 | κενο | κενο | 6
PAGE 124 : κενο | 10 | κενο | κενο
PAGE 125 : 14 | κενο |κενο | 26

Στην περίπτωση που κάνω inserts, updates έτσι όπως είναι τώρα η δομή μπορεί και να επωφεληθώ καθώς αν βάλω τιμές που λείπουν θα καταλάβουν τα κενά. Αν όμως πάω να ρωτήσω τότε θα διαβάσω περισσότερες σελίδες από όσες πραγματικά χρειάζονται.

Πως βρίσκω όμως τι internal & external fragmentation έχω;

Επειδή ναι μεν ανέφερα ότι όλοι γνωρίζουμε πώς να δούμε το fragmentation που έχουμε σε ένα index εντούτοις θα το επαναλάβω καθώς δεν είμαι και τόσο σίγουρος ότι αυτό ισχύει, οπότε με τα παρακάτω queries μπορώ να βρω το κάθε fragmentation που έχω σε κάθε index στη βάση μου

--
-- EXTERNAL FRAGMENTATION
--
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_fragmentation_in_percent AS 'external_Fragmentation_(%)'
      , S.fragment_count AS 'index_fragments'
      , S.avg_fragment_size_in_pages AS 'index_avg_fragment_size'
    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;
GO
--
-- INTERNAL FRAGMENTATION
--
SELECT    IDX.name AS 'index_name'
      , S.index_level AS 'index_level'
      , S.page_count AS 'index_Pages#'
      , S.avg_page_space_used_in_percent AS 'page_fullness_(%)'
      , 100.0 - S.avg_page_space_used_in_percent AS 'internal_fragmentation_(%)'
    FROM sys.dm_db_index_physical_stats(  
        DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'DETAILED') AS S
    JOIN sys.indexes AS IDX
        ON IDX.OBJECT_ID = S.OBJECT_ID AND IDX.index_id = S.index_id;
GO

Αυτό που πρέπει όμως να προσέξουμε είναι ότι το internal fragmentation βγαίνει από το avg_page_space_used_in_percent field της sys.dm_db_index_physical_stats ενώ το external fragmentation βγαίνει από avg_fragmentation_in_percent field της sys.dm_db_index_physical_stats

Στον SSMS πρέπει να επισημανθεί ότι το  avg_page_space_used_in_percent είναι αυτό που εμφανίζεται στο Page fullness και το avg_fragmentation_in_percent στο Total fragmentation

Τέλος πρέπει να επισημανθεί ότι το avg_page_space_used_in_percent θέλουμε να είναι 100% αν δεν είναι τότε η διαφορά του με το 100% μας δίνει το internal fragmentation και για αυτό στο query υπάρχει και το πεδίο internal_fragmentation που υπολογίζει αυτό.
Ελπίζω να έγιναν πλέον ξεκάθαρα τα πράγματα
Για όσους θέλουν να κάνουν τώρα defrag υπάρχουν οι γνωστές διαδικασίες για index reorganize και rebuild


/*antonch*/