go backarticles

Articles of SQLschool.gr Team

Why each database must have more than one data file?

Antonios Chatzipavlis

Σε κάθε άνθρωπο που ασχολείται με τον SQL Server είναι γνωστό ότι μια database αποτελείται αρχικά από δύο αρχεία. Το ένα είναι το primary data file και είναι αυτό που έχει το .mdf extension και το transaction log file που είναι αυτό που έχει το .ldf extension.

Επίσης σε κάθε database υπάρχει το primary filegroup στο οποίο by default ανήκει το primary data file. Κάθε data file ανήκει σε ένα και μόνο ένα filegroup. Ένα filegroup μπορεί να έχει περισσότερα από ένα data files. Το ή τα log files δεν ανήκουν σε filegroup. Κάθε database μπορεί να έχει 32.767 filegroups. Τέλος τα filegroups είναι λογικές οντότητες τις οποίες χρησιμοποιεί ο SQL Server σε διάφορες περιπτώσεις.

Δυνητικά μια database μπορεί να φτάσει σε μέγεθος τα 524ΤΒ. Το μέγεθος όμως αυτό δεν μπορεί να συντεθεί από ένα μόνο data file, θα πρέπει να έχω περισσότερα (32.767 is the max number of file per database). Το κάθε data file όμως δεν μπορεί να έχει μέγεθος μεγαλύτερο από 16ΤΒ και το κάθε log δεν μπορεί να είναι μεγαλύτερο από 2ΤΒ.

Όλα τα παραπάνω είναι θεωρώ γνωστά στους περισσότερους και ουσιαστικά στους περισσότερους δεν λένε και τίποτα, ειδικά δε σε αυτούς που έχουν databases που είναι κάτω του TB.

Τι είναι αυτό όμως που με ώθησε να γράψω το άρθρο αυτό;

Αυτό που διαπίστωσα τόσο σαν Consultant όσο και Trainer είναι το γεγονός ότι κανείς μα κανείς δεν δίνει την πρέπουσα προσοχή σε αυτά ώστε να κερδίσει είτε σε performance είστε σε maintenance. Ακόμα και αυτοί που έχουν περισσότερα αρχεία ή filegroups συνήθως το έχουν κάνει γιατί έχουν φτιάξει κάποιο table partitioning που υποχρεωτικά πρέπει να έχουν περισσότερα από ένα filegroups και files.

Με την χρήση περισσοτέρων από ένα data file(s) μπορείς να κερδίσεις τα εξής:
  1. Μπορείς να κάνεις partial backup.
    Ο συνηθισμένος τρόπος για τους περισσότερους είναι να κάνουν full backup σε μια database. Παρόλα αυτά όμως υπάρχει και η δυνατότητα του partial backup που αρκετοί από εμάς εφαρμόζουν σε very large databases. Το partial backup γίνεται σε επίπεδο file/filegroup στο οποίο κατά το σχεδιασμό της database έχουμε τοποθετήσει συγκεκριμένα objects όπως tables ή indexes. Αν και είναι κάπως δύσκολο σας backup σενάριο καθώς απαιτεί και transaction log backup εντούτοις είναι αυτό που επιλέγουμε καθώς μπορούμε να ελαχιστοποιήσουμε τους χρόνους που απαιτούνται για backup.

  2. Έχεις καλύτερο έλεγχο στο που θα βάλεις κάθε database object και να βελτιώσεις το I/O performance
    Όπως αναφέρθηκε και παραπάνω με την χρήση πολλαπλών files/filegroups μπορείς να έχεις έλεγχο στο που μπορείς να βάλεις τα tables/indexes. Με αυτό τον τρόπο μπορείς να μοιράσεις τα φορτία I/O που έχεις από ένα disk/disk array σε περισσότερα disk/disk array.
    Ακόμα θα πρέπει να αναφερθεί ότι αν έχω σε ένα filegroup περισσότερα από ένα αρχεία τα οποία αυτά τα έχω σε ξεχωριστό disk/disk array και σε αυτό το filegroup βάλω έναν ή περισσότερους tables τότε θα εκμεταλλευτώ τον round robin algorithm που γίνεται σε επίπεδο extents με αποτέλεσμα να έχω πετύχει έτσι απλά ένα data stripping μεταξύ των disk/disk array στα οποία έχω τοποθετήσει τα data files. Αυτό σημαίνει απλά καλύτερο I/O.

  3. Μπορείς να βοηθηθείς σε περίπτωση disaster/recovery ή μεταφοράς σε άλλο instance.
    Δεν είναι λίγες οι φορές που πρέπει να μεταφέρεις μια βάση από ένα instance σε ένα άλλο instance ή ακόμα και στο ίδιο να αλλάξει το path στο οποίο βρίσκονται τα αρχεία μιας database.
    Σε αυτές τις περιπτώσεις αν για παράδειγμα έχεις μια βάση που είναι 2ΤΒ και θέλεις να την μεταφέρεις θα πρέπει να έχεις ελεύθερο χώρο στο δίσκο που θα την μεταφέρεις που να είναι ίσος ή μεγαλύτερος από το μέγεθος των 2ΤΒ. Όμως κάτι τέτοιο δεν είναι πάντα εύκολο, αλλά αν έχεις πχ σε τρεις διαφορετικούς δίσκους συνολικά την χωρητικότητα αυτή (πχ 1ΤΒ στο έναν και από 600 GB στους άλλους δύο) θα μπορούσες να κάνεις την μεταφορά αυτή αν είχες τέσσερα αρχεία των 512GB. Θα πρέπει να το τονιστεί ότι δεν έχουμε κανένα όφελος (αλλά ούτε και μειονέκτημα) αν έχουμε όλα αυτά τα αρχεία στον ίδιο disk/disk array. Το μόνο πραγματικά μεγάλο όφελος που έχουμε είναι ότι μπορούμε να είμαστε περισσότερο ευέλικτοι στις μετακινήσεις των files εφόσον φυσικά χρειαστούμε να προβούμε σε αυτές.

Προσωπικά χρόνια τώρα έχω επιλέξει να έχω περισσότερα από ένα data files σε κάθε database που δημιουργώ καθώς έχω την δυνατότητα να εκμεταλλευτώ όλα τα παραπάνω ανά πάσα στιγμή.
Αυτό που συνηθίζω είναι να αφήνω μόνο του το primary data file στο primary filegroup στο οποίο ουσιαστικά μένουν τα system objects και όλα τα άλλα objects ανάλογα με την περίπτωση μπαίνουν σε ένα ή περισσότερα filegroups. Για παράδειγμα όλα τα transactional tables μαζί με τους indexes τους τα βάζω σε ξεχωριστά filegroups και σε ένα όλους τους άλλους πίνακες. Αυτό το κάνω πάντα σε databases που έχουν την δυναμική να γίνουν large ή very large databases.

Σας προτρέπω να δοκιμάστε κάτι αντίστοιχο και θα δείτε με διαφορετική εικόνα το τι κάνετε μέχρι τώρα!

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