Σήμερα λέω να συνεχίσουμε την σειρά των BI post με ένα πρακτικό θέμα. Το θέμα αυτό αφορά την βασική εργασία που ένα SSIS package περιέχει στο 99,9999% των υλοποιήσεων του και δεν είναι άλλη από το Data Flow Task.
Εισαγωγή
Για όσους έρχονται για πρώτη φορά σε επαφή με το σπορ αυτό, απλά να πω ότι το συγκεκριμένο task μας δίνει την δυνατότητα να υλοποιήσουμε μια εργασία που διαβάζει από κάποια πηγή (source data source) και μετατρέπει τα δεδομένα αυτά σε μορφή τέτοια που να είναι συμβατή από τη πηγή που αυτά θα τοποθετηθούν (destination data source). Με άλλα λόγια υλοποιούμε μια Extract Transform Load (ETL) λύση.
Στα προηγούμενα post της σειράς αυτής έχω αναφερθεί σε αρκετά πράγματα τα οποία θα πρέπει να λάβουμε υπόψη στον σχεδιασμό και την υλοποίηση μιας τέτοια λύσης. Σε αυτό όμως θα πάμε ένα βήμα παραπέρα και θα δούμε μερικά properties που το συγκεκριμένο task έχει με τα οποία μπορούμε να «παίξουμε» ώστε να κάνουμε αυτό να εκτελείται καλύτερα με όσο το δυνατό μικρότερη κατανάλωση πόρων.
Εν αρχή είναι το query optimization
Όλα ξεκινάνε από το query με το οποίο θα διαβάσουμε τα δεδομένα από το data source. Το SELECT * είναι ο εχθρός μας και το WHERE είναι ο φίλος μας. Με αυτό θέλω να επισημάνω για ακόμα μια φορά ότι φέρνουμε τα δεδομένα τα οποία πραγματικά χρειαζόμαστε τόσο σε πλήθος εγγραφών όσο και αριθμό πεδίων. Αυτό το query θα πρέπει να το κάνουμε optimize όσο το δυνατόν καλύτερα και οι λόγοι είναι προφανείς, και έχουν εξηγηθεί στα προηγούμενα post αλλά μια επανάληψη δεν κάνει κακό, ταχύτητα, κατανάλωση πόρων, I/O.
Ταξινομήσεις σε επίπεδο database
Επίσης κατ’ επανάληψη έχω επισημάνει ότι εφόσον θέλω τα δεδομένα μου να διαβάζονται ταξινομημένα (sorted) αυτό θα πρέπει να γίνεται με την χρήση της ORDER BY στο query με το οποίο διαβάζω αυτά. Είναι σαφέστερα καλύτερο από το να κάνω αυτό με το SSIS Sort transformation task. Αρκετά transformation task για να λειτουργήσουν θέλουν τα δεδομένα που παίρνουν σαν input να είναι ταξινομημένα όπως για παράδειγμα το Merge και Merge Join.
Εάν έχω ήδη ταξινομημένα (από το query) τα δεδομένα μου και θέλω να αποφύγω ταξινομήσεις που χρειάζονται σε ενδιάμεσα transformations (όπως τα merge/merge join) τότε θα πρέπει να ορίσω το IsSorted property σε true στο Data Flow task output σε συνδυασμό με το να ορίσω το sort key column(s) με το οποίο αυτό είναι ταξινομημένο για να καταλάβουν αυτά ότι είναι ήδη ταξινομημένα τα δεδομένα μου.
Data flow Buffer optimization
Λίγες γραμμές παραπάνω έγραψα ότι το SELECT * είναι εχθρός και το WHERE φίλος μας. Εκτός από τους λόγους που είπα υπάρχει ακόμα ένας που άπτεται με την εκτέλεση του Data Flow task. Σε αυτό υπάρχει ένας buffer στον οποίο μπαίνουν οι εγγραφές που πρόκειται να επεξεργαστούν. Είναι φρόνιμο να βελτιστοποιήσω το buffer αυτό. Η βελτιστοποίηση/παραμετροποίηση του γίνεται με την χρήση δύο properties που υπάρχουν στο Data Flow Task και τα οποία είναι τα DefaultBufferSize και DefaultBufferMaxRows. Με το πρώτο ορίζω πόσο θα είναι το μέγεθος του buffer (default 10ΜΒ, max 100MB) και με το δεύτερο ορίζω το πόσες γραμμές θα έχει αυτό (default max 10.000). Πώς όμως θα το κάνω αυτό;
Για να απαντηθεί το ερώτημα αυτό θα πρέπει να δούμε λίγο πως λειτουργούν. Σε κάθε data flow task με το που αυτό θα ξεκινήσει το πρώτο πράγμα που γίνεται είναι να γίνει ο υπολογισμό του μεγέθους του buffer. Αυτό γίνεται με το να γίνει εκτίμηση του μεγέθους της γραμμής των δεδομένων (record length) που διαβάζονται, έπειτα πολλαπλασιάζει αυτό με τον αριθμό των γραμμών που έχει ορισθεί στο DefaultBufferMaxRows property ώστε να βρει τον βασικό χώρο που πρέπει ο buffer να έχει. Αν αυτό ξεπερνάει αυτό που έχει ορισθεί στο DefaultBufferSize τότε μειώνονται οι γραμμές που θα χωράνε σε αυτό. Αντίστοιχα αν είναι μικρότερο μεγαλώνει ο αριθμός των εγγραφών. Αν είναι ανάμεσα στο min του buffer size και στο DefaultBufferSize τότε ο buffer γίνεται τόσος όσο είναι το γινόμενο του εκτιμώμενου record length με το DefaultBufferMaxRows.
Μετά από αυτή την σύντομη εξήγηση του πως αυτά τα δύο properties συνεργάζονται δεν είναι, νομίζω,δύσκολο να καταλάβεις κανείς την παραπάνω φράση μου για το SELECT * και το WHERE. Θέλω το record length να είναι όσο το δυνατό το μικρότερο αλλά θέλω να φέρνω δεδομένα που μου χρειάζονται καθώς έτσι θα έχω την δυνατότητα να επεξεργαστώ περισσότερες εγγραφές και με την ελάχιστη μνήμη.
Για να βρει κάποιος τις ιδανικές τιμές για τα properties αυτά η λύση είναι μία. Αφήνουμε για αρχή τις default τιμές και ενεργοποιούμε το logging στο task επιλέγοντας το BufferSizeTuning event με το οποίο μπορούμε να δούμε πόσες εγγραφές χωράνε στον buffer.
Δύο παρατηρήσεις σημαντικές.
· Επειδή σε ένα SSIS package το σύνηθες είναι να έχω περισσότερα από ένα data flow tasks άρα και περισσότερα buffers καλό θα είναι να έχω μικρό αριθμό από μεγάλα σε μέγεθος παρά πολλά σε αριθμό αλλά μικρά.
· Εξαιτίας του παραπάνω είμαι υποχρεωμένος να τονίζω ότι μην τα παραξηλώσετε με το μέγεθος και πάτε στο άλλο άκρο καθώς έτσι είναι σχεδόν σίγουρο ότι θα έχετε disk paging και αυτό είναι ακόμα χειρότερο. Μη ξεχνιόμαστε ο buffer μνήμη είναι. Για να δείτε αν έχετε κάνει κάτι τέτοιο μπορείτε μέσα από το perfmon.exe να κάνετε monitor το μετρητή Buffer spooled.
Parallel Execution
Η παράλληλη εκτέλεση είναι γνωστό σε όλους ότι μπορεί να βελτιώσει το performance ιδιαίτερα τώρα που έχουμε ισχυρούς και με πολλούς πυρήνες επεξεργαστές. Για την υποστήριξη της δυνατότητας αυτής υπάρχουν δύο properties το ένα σε επίπεδο package και το άλλο στο Data Flow task.
Σε επίπεδο package υπάρχει το MaxConcurrentExecutables με το οποίο ορίζουμε πόσα task θα μπορούν να τρέχουν ταυτόχρονα. Η αρχική τιμή είναι -1 και όπως λένε τα BOL «means the number of physical or logical processors plus 2.»
Αυτό που είναι σε επίπεδο Data Flow task είναι το EngineThreads το οποίο ορίζει τον αριθμό των threads που θα τρέχουν παράλληλα.
Other Properties
Εκτός από τα παραπάνω properties υπάρχουν τα
· BufferTempStoragePath με το οποίο ορίζω το temp storage για τα buffer data.
· BLOBTempStoragePath με το οποίο ορίζω το αντίστοιχο path για τις πιθανές BLOB columns
Σε κάθε περίπτωση αν δεν δώσω τιμές σε αυτά παίρνουν τις τιμές που υπάρχουν στα environment variables TEMP και TMP. Καλό είναι αν έχω μεγάλη χρήση να αλλάξω τα path αυτά σε ξεχωριστούς γρήγορους δίσκους.
· RunInOptimizedMode ένα property το οποίο είναι by default ενεργοποιημένο (true) και το οποίο αυτό που κάνει είναι να πετάει έξω από την εκτέλεση του data flow task οτιδήποτε είναι άχρηστο όπως κολώνες που δεν χρησιμοποιούνται, outputs που δεν χρησιμοποιούνται, components κλπ. ΠΡΟΣΟΧΗ ΞΕΡΩ ΟΤΙ ΤΩΡΑ ΘΑ ΒΡΕΘΕΙ ΚΑΠΟΙΟΣ ΚΑΙ ΘΑ ΜΟΥ ΠΕΙ ΟΤΙ ΑΦΟΥ ΥΠΑΡΧΕΙ ΑΥΤΟ ΓΙΑΤΙ ΠΧ ΝΑ ΜΗ ΧΡΗΣΙΜΟΠΟΙΩ ΤΗΝ SELECT *. Η απάντηση μου είναι απλή γιατί θα πρέπει να γίνονται όλα αυτόματα και ακόμα γιατί θα πρέπει να το βάλω να κάνει αυτή την διαδικασία που θέλει χρόνο ενώ μπορώ να το κάνω μόνος μου και να έχω τον έλεγχο.
Επίλογος
Με τα παραπάνω μπορώ να πετύχω ένα αξιόλογο performance, όμως επειδή το σύνηθες είναι να έχω και transformations μέσα σε ένα data flow task θα πρέπει να φροντίσω και αυτά να είναι optimized. Να είμαστε καλά και θα τα δούμε και αυτά ξεχωριστά.
/*antonch*/
SQLschool.gr Old 