Πώς αυτοματοποιούμε τη διαδικασία rebuild των indexes στον SQL Server χωρίς την χρήση maintenance plan
Πιστοποιήσεις:
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
- Πώς κάνουμε μαζικά detach / attach βάσεις δεδομένων στον SQL Server - 2 Φεβρουάριος 2024
Όπως έχουμε δει σε παλαιότερο άρθρο για τον ευκολότερο τρόπο προσπέλασης στα δεδομένα στις βάσεις δεδομένων χρησιμοποιούμε τα indexes και τα στατιστικά.
Τα indexes είναι ουσιαστικά ένα ευρετήριο που μας βοηθάει να βρούμε ότι ψάχνουμε πιο γρήγορα. Καθώς όμως πραγματοποιούντε αλλαγές στους πίνακες, τα indexes κατακερματίζονται και οι πληροφορίες στον ευρετήριο αυτό δεν είναι πλέον ταξινομημένες. Από την άλλη στα στατιστικά έχουμε πληροφορίες όπως το πλήθος εγγραφών και την κατανομή διαφορετικών τιμών στο κάθε πεδίο ενός πίνακα.
Στο άρθρο θα δούμε τον πιο εύκολο και αποδοτικό τρόπο ώστε να κρατάμε τα indexes και τα στατιστικά όσο πιο αποδοτικά γίνεται. Αυτό θα το κάνουμε με τη χρήση της procedure indexOptimize που έχει φτιάξει ο Hallengren.
Φυσικά αυτή η εργασία γίνεται με την χρήση T-SQL π.χ. alter index all rebuild ή maintance plans που έχει build-in ο SQL Server αλλά τα παραπάνω έχουν κάποιους περιορισμούς.
Τι παραπάνω προσφέρει
Με το indexOptimize μπορεί να πραγματοποιείτε διαφορετικό action ανάλογα το fragmentation που έχει το κάθε index. Όπως reorganize, rebuild και ενημέρωση των στατιστικών.
Επίσης μας προσφέρει μεγαλύτερο έλεγχο με τις παραμέτρους που δέχεται κατά την εκτέλεση της.
Η εγκατάσταση
Κατεβάσουμε από εδώ το πακέτο με τις procedure που θα χρειαστούμε:
ή απευθείας από το site του δημιουργού εδώ.
Αφού κατέβει εκτελούμε όλο το script στη βάση που θέλουμε να καλούντε από αυτήν π.χ σε μία custom βάση που έχουμε φτιάξει για monitoring ή στην συστημική βάση master.
Στη συνέχεια για να ξεκινήσει η διαδικασία, απλά εκτελούμε την procedure με τις παραμέτρους όπως παρακάτω:
EXECUTE dbo.IndexOptimize @Databases = 'ALL_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y', @SortInTempdb = 'Y', @MaxDOP = 0
Τι ακριβώς ορίζει η κάθε παράμετρος
- @Databases ορίζουμε η εργασία θα πραγματοποιηθεί σε όλες τις βάσεις δεδομένων του instance.
- @FragmentationLow ορίζουμε να μην πραγματοποιηθεί κάποια αλλαγή σε όσα indexes έχουν χαμηλό κατακερματισμό.
- @FragmentationMedium ορίζουμε σε όσα indexes έχουν μεσαίο κατακερματισμό να πραγματοποιηθεί αναδιάταξη των indexes και σε περίπτωση που αυτό δεν είνα δυνατό να γίνει online rebuild (δηλαδή είναι προσβάσιμός ο πίνακας κατά την διαδικασία) και αν ούτε αυτό δεν είναι δυνατό (π.χ. όχι enterprise edition) να γίνει offline (να μην είναι προσβάσιμός ο πίνακας κατά την διαδικασία).
- @FragmentationHigh = oρίζουμε σε όσα indexes έχουν υψηλό κατακερματισμό να πραγματοποιηθεί online rebuild (δηλαδή είναι προσβάσιμός ο πίνακας κατά την διαδικασία) και αν δεν είναι δυνατό (π.χ. όχι enterprise edition) να γίνει offline (να μην είναι προσβάσιμός ο πίνακας κατά την διαδικασία).
- @FragmentationLevel1 ορίζουμε το κατώτερο επίπεδο που θεωρείτε medium ο κατακερματισμός.
- @FragmentationLevel2 ορίζουμε το κατώτερο επίπεδο που θεωρείτε high ο κατακερματισμός.
- @UpdateStatistics ορίζουμε να ενημερωθούν και τα στατιστικά και στους πίνακες και στα indexes
- @OnlyModifiedStatistics ορίζουμε να ενημερώσει τα στατιστικά μόνο σε όσους πίνακες έχουν τροποποιηθεί από την τελευταία φορά που έτρεξε.
- @LogToTable ορίζουμε αν θα καταγράφονται τα commands του indexΟptimize στον πίνακα commandlog που δημιουργήθηκε όταν εγκαταστήσαμε το script.
- @SortInTempdb ορίζουμε αν θα κάνει χρήση την tempdb κατά την διαδικασία.
- @MaxDOP ορίζουμε την μέγιστη παραλληλία των cpu cores που επιτρέπεται, με 0 ορίζουμε ότι θέλουμε να χρησιμοποιήσει όσα υπάρχουν.
Μπορούμε να δούμε αναλυτικά και τις υπόλοιπες παραμέτρους στο manual στο επίσημο site εδώ.
Πως αυτοματοποιούμαι τη διαδικασία
Μπορούμε να προσθέσουμε την εκτέλεση της procedure σε ένα SQL Server Agent Job.
Για να το κάνουμε αυτό πάμε στον Objects Explorer, SQL Server Agent, Δεξί κλικ στο Jobs, New Job…
Στο παράθυρο που εμφανίζεται πάμε στην καρτέλα Steps και επιλέγουμε New.
Εκεί διαλέγουμε τη βάση master που είχαμε εγκαταστήσει την procedure και προσθέτουμε το script της procedure.
Στην καρτέλα Schedules μπορούμε να ορίσουμε την συχνότητα που θα εκτελείτε η εργασία. Καλό θα είναι να διαλέξουμε μια χρονική στιγμή που δεν έχει μεγάλο φόρτο εργασίας με προσπέλαση στα δεδομένα στο instance.
Για να δούμε αναλυτικά το κάθε action που έχει κάνει η procedure και πότε το έκανε
Απλά κάνουμε ένα select στον πίνακα commandlog:
select * from master..commandlog;
Bonus
Με το παρακάτω script που έχω φτιάξει, με μόνο ένα κλικ, κάνει εγκατάσταση το MaintenanceSolution του Ola Hallengren μαζί με έτοιμα παραμετροποιημένα jobs για Backup ,Database Integrity και Index Optimize με τις βέλτιστες παραμέτρους και χρονοδιάγραμμα.
Τα Backup jobs δημιουργούνται disabled καθώς μπορεί να έχουμε άλλη λύση για backup με 3rd party tool. Επίσης κάνει έλεγχο για Availability Group ώστε να τρέχουν μόνο αν είναι η Preffered Backup Replica (για backup / database integrity) και αν είναι Primary Replica (για Index Optimize).
Το μόνο που χρειάζεται να κάνουμε, είναι να εκτελέσουμε το παρακάτω script στο κάθε SQL Server Instance:
