go backarticles

Articles of SQLschool.gr Team

Αναμφίβολα όλοι γνωρίζουμε την αξία που έχουν οι 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


ssms index


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


/*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.


Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

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