Πριν μερικές μέρες έγραψα το πρώτο μέρος για το συγκεκριμένο θέμα στο οποίο έδειξα όλη την περιπτωσιολογία πάνω σε αυτό.
Σήμερα θα σας εξηγήσω όσο απλούστερα γίνεται τι κρύβεται πίσω από την κουρτίνα.

Το περιβάλλον

Αρχικά θα πρέπει να επισημάνω ότι οι περιπτώσεις που έδειξα δεν είναι οι μόνες, είναι όμως οι πιο χαρακτηριστικές και οι πιο συχνές.
Επίσης θα πρέπει να τονίσω ιδιαίτερα ότι η συγκεκριμένη μηχανή που έγιναν τα παραδείγματα αυτά είναι ιδανικά φτιαγμένη, και με αυτό θέλω να πω ότι έχει προσεχθεί και η παραμικρή λεπτομέρεια στην εγκατάσταση του SQL Server. Επίσης θα πρέπει να τονίσω ότι οι βάσεις έχουν δημιουργηθεί σε ξεχωριστούς δίσκους οι οποίοι και έχουν γίνει format με 64KB cluster size και ότι δεν είναι fragment.

Η εξήγηση

Με αυτό στο μυαλό μας θα εξηγήσω τις περιπτώσεις που έδειξα το προηγούμενο post μου.

Στις δύο πρώτες περιπτώσεις είδαμε ότι έχουμε το ίδιο αποτέλεσμα είτε έχω δημιουργήσει την βάση με τα defaults (1η περίπτωση) είτε έχω κάνει pre-allocate τον χώρο (2η περίπτωση).

Αυτό έγινε διότι πρώτα εκτέλεσα την πρώτη περίπτωση σε ένα άδειο δίσκο με συνέπεια σαν αρχείο να μην χρειάζεται να γίνεται fragment, αρκετά ιδανικό σενάριο.

Μετά εκτέλεσα την δεύτερη περίπτωση που έκανε allocate τον χώρο και έτσι δεν ήταν fragment το αρχείο.

Η εξήγηση γιατί αυτές οι δύο περιπτώσεις έχουν ακριβώς το ίδιο fragmentation ενώ δεν θα έπρεπε, απορρέει από το παραπάνω γεγονός ότι δεν υπήρχε physical fragmentation στην πρώτη περίπτωση.

Αυτό όμως δεν θα ήταν έτσι αν είχα την βάση αυτή σε ένα δίσκο που είτε είχε άλλες βάσεις που και αυτές είχαν δημιουργηθεί με τα defaults είτε είχαν μικρό filegrowth είτε ήταν σε ένα δίσκο που άλλες εφαρμογές γράφουν αρχεία.

Σε αυτές τις περιπτώσεις θα είχα παντελώς διαφορετικά νούμερα με την πρώτη περίπτωση να έχει πολύ μεγαλύτερα νούμερα.

Και θα με ρωτήσετε τώρα γιατί δεν είναι μηδέν το fragmentation παρόλο που έχουμε κάνει index rebuild.
H εξήγηση απλή εφόσον γνωρίζουμε την αρχιτεκτονική μιας βάσης στον SQL Server σε συνδυασμό με το γεγονός ότι τα δεδομένα που είχα βάλει ήταν 10.000 γραμμές και ο index που έφτιαξα δημιούργησε 24 index pages στο leaf level αυτού.

Όπως έχω πει και έχω γράψει αρκετές φορές στο παρελθόν ο SQL Server αποθηκεύει τα δεδομένα του (data & indexes) σε 8K pages, κάθε 8 συνεχόμενες pages μας φτιάχνουν ένα extent, και υπάρχουν δύο ειδών extent τα uniform και τα mixed. Έτσι ο SQL Server τις πρώτες 8 σελίδες είτε είναι από table είτε είναι από index τις βάζει σε ένα ή περισσότερα mixed extents. Επειδή οι σελίδες που έχουμε είναι μόνο 24 οι 8 πρώτες θα είναι σε ένα ή περισσότερα mixed extents.

Αυτό είναι εύκολο να το δείτε εάν εκτελέσετε την DBCC EXTENINFO (0,Τ,-1) και δείτε τις γραμμές που έχουν την τιμή 2 στο index_id field.

Αν θέλετε να βρείτε σε ποιο extent είναι δεν έχετε παρά να διαιρέσετε το αριθμό της σελίδας (page_id) με το 8 και το ακέραιο μέρος είναι ο αριθμός του extent.

Προσοχή αν θα κάνετε τις πράξεις προσθέτοντας την τιμή που έχει το pg_alloc θα δείτε ότι έχετε 25 αντί για 24 pages. Αυτή η παραπάνω είναι η root index page απλά και αυτή καταναλώνει χώρο και η DBCC EXTENDINFO δεν την ξεχωρίζει.

Αυτό μπορείτε να το δείτε αν εκτελέσετε το query που δείχνει το external fragmentation με DETAILED αντί για LIMITED και επίσης για όσους θέλουν να ψαχθούν περισσότερο μπορούν να εκτελέσουν την DBCC IND ('idx1',T,2) όπου θα δουν περισσότερες λεπτομέρειες.

Αυτό που όμως κάνει την διαφορά είναι ο αριθμός των index fragments που στην πρώτη περίπτωση είναι 7 και στην δεύτερη 6 και αν όπως έχω πει και παραπάνω η βάση ήταν σε ένα δίσκο που χρησιμοποιούνταν και από άλλους το fragmentation στην πρώτη περίπτωση θα ήταν μεγαλύτερο.

Αυτό μάλιστα είναι ξεκάθαρο στις περιπτώσεις 3 και 4 όπου εδώ βάζω 100.000 γραμμές και όπου βλέπουμε να έχουμε σαφώς μικρότερο fragmentation και όπου η 4η περίπτωση όπου η βάση έχει δημιουργηθεί με pre-allocation του χώρου  μετά το rebuild έχει πάντα μηδέν.

Αυτό γίνεται διότι έχει περισσότερες σελίδες ο index και ο SQL Server αποφασίζει να του δώσει uniform extents. Όμως η 3η περίπτωση που δεν έχει pre-allocated τον χώρο και αυτός που έχει δημιουργηθεί δεν είναι συνεχόμενος μου δείχνει external fragmentation.

Επίλογος

Αυτό που θέλω να περάσω σαν μήνυμα από τα δύο αυτά post μου είναι:

1. Πάντα βάζουμε τις βάσεις σε ξεχωριστούς δίσκους
2. Πάντα κάνουμε pre-allocation χώρου στο δίσκο.

Με αυτά τα δύο είναι δεδομένο ότι θα εξαλειφθεί το external fragmentation, με μία επισήμανση ότι αυτό θα γίνει σε indexes με μεγάλο αριθμό σελίδων. Οι indexes με μικρό αριθμό σελίδων θα δείχνουν fragmented αλλά έτσι θα είναι και απλά μπορείτε να αγνοήσετε το fragmentation σε αυτούς.

Προσωπικά όταν θέλω να δημιουργήσω μια νέα βάση πρώτα κοιτάζω αν ο δίσκο που σκοπεύω να βάλω αυτή είναι fragmented. Αν είναι και δεν έχω άλλο δίσκο προσπαθώ να τον κάνω defrag είτε με το windows defrag  που όμως δεν κάνει defrag τα αρχεία που χρησιμοποιούνται όπως για παράδειγμα τα αρχεία μιας βάσης, και αν θέλεις να κάνεις αυτά θα πρέπει να σταματήσεις το SQL Server service, και αυτό δεν είναι πάντα εφικτό να γίνει, είτε με άλλα εργαλεία τρίτων είτε με το contig tool από την sysinternals suite.

Αυτό είναι κάτι το οποίο είναι πόνος μεγάλος αλλά τελικά τι θέλουμε να κάνουμε είναι η απάντηση. Θέλουμε ένα σύστημα που να αντιμετωπίζει συνεχώς θέματα ή να κλείσουμε το σύστημα για κάποιο χρόνο ώστε να κάνουμε defrag και μετά να είμαστε άρχοντες;

/*antonch*/