Θα επανέλθω στο συγκεκριμένο θέμα μετά από απαίτηση συναδέλφων που είδαν την παρουσίαση που έχω κάνει για το 34ο dotNetZone Event και την οποία θα βρείτε εδώ. Υπήρχαν κάποιες απορίες και ερωτήσεις που φιλοδοξώ να τις απαντήσω με το παρόν post μου. Ας πάρουμε λοιπόν τα πράγματα από την αρχή μιας και διακαείς πόθος όλων μας είναι να έχουμε την τέλεια απόδοση στα queries μας δηλαδή όλα να εκτελούνται σε χρόνο μηδέν και να επιστρέφουν δισεκατομμύρια εγγραφές (καλά πλάκα κάνω :D).

Όμως για όλα αυτά υπάρχουν κάποιες προϋποθέσεις όπως:

1. Tuning queries κάνει αυτός που είναι database developer ΚΑΙ όχι ένας developer γενικά. Δεν σημαίνει ότι όποιος ξέρει να γράφει C++, C#, VB, Java ξέρει και τι σημαίνει query execution. Εξάλλου όλοι πλέον οι developers δεν θέλουν να γράφουν queries μέσα από τις εφαρμογές τους (συμφωνώ και εγώ με αυτό) αλλά να χρησιμοποιούν εργαλεία όπως LINQ to SQL, Entity Framework κ.α σωστά;

2. Αυτός που θα αναλάβει να κάνει την συγκεκριμένη εργασία δεν φτάνει να ξέρει πως τα queries, οι indexes δουλεύουν. Πρέπει να ξέρει την γενικότερη αρχιτεκτονική του SQL Server καθώς επίσης και πως ΟΛΑ του τα κομμάτια παντρεύονται μεταξύ τους.

3. Θα πρέπει να είναι βαθύς γνώστης του σχεδιασμού της βάσης στην οποία θα κάνει tuning το ή τα queries. Όπως επίσης θα πρέπει να ξέρει την ποιότητα και την ποσότητα των δεδομένων τα οποία θα χρησιμοποιήσει για να βελτιώσει την απόδοση στο query του.

Αν εκπληρώνονται οι παραπάνω προϋποθέσεις θα πρέπει να γνωρίζει τα εξής:

1. Τι είναι το Query Performance και πως αυτό μετριέται.

Βασική αρχή στην βελτίωση της απόδοσης ενός query είναι να βρω το τι φταίει και για να γίνει αυτό πρέπει να έχω μια μονάδα μέτρησης για να μπορέσω να εξάγω ένα συμπέρασμα το οποίο θα μου δείξει το δρόμο που θα με οδηγήσει στην επίλυση του προβλήματος. Στον SQL Server έχουμε τρεις βασικές μονάδες μέτρησης αυτές είναι query cost, page reads, query execution time.

Για να τις δούμε όμως λίγο καλύτερα μήπως και βγάλουμε κάποιο συμπέρασμα

Query Cost: Συνήθως είναι η βασική μας μονάδα μέτρησης όταν θέλουμε να συγκρίνουμε την απόδοση ενός ερωτήματος. Είναι μια εσωτερική μετρική του SQL Server η οποία λαμβάνει υπόψη της το πόσο CPU και Ι/Ο χρησιμοποιεί το ερώτημα μας. 'Οσο μικρότερη είναι η τιμή του (σε %) τόσο καλύτερη είναι και η απόδοση στο ερώτημα μας. Αλλά δεν λαμβάνει υπόψη θέματα που αφορούν πχ Waiting Locks ή Resource Contention. Επίσης όταν μέσα στο query μας υπάρχουν user defined functions (UDFs) ή Common Language Runtime (CLR) ρουτίνες αυτές δεν λαμβάνονται υπόψη στον υπολογισμό του query cost και αυτός είναι και ο λόγος που ονομάζεται estimated query cost. Αυτό το βλέπουμε από το Εxecution Plan

Page Reads: Είναι ο αριθμός των σελίδων (8KB/page) που το Storage Engine του SQL Server διαβάζει κατά την διάρκεια εκτέλεσης του query. Αυτό το βλέπουμε εάν στο session στο οποίο εκτελούμε το query μας και πριν την εκτέλεση τους εκτελέσουμε την SET STATISTICS IO ON. To αποτέλεσμα θα είναι το παρακάτω

Από το output καταβαίνουμε ότι έχει κάνει logical reads 27 pages δηλαδή τις διάβασε από την μνήμη (buffer cache, δείτε παλαιότερο post μου). Σε αυτές συμπεριλαμβάνονται και τα data και τα index pages. Με τα physical read και read-ahead reads βλέπουμε πόσα pages διάβασε από τον δίσκο και τις ανέβασε στην buffer cache. Με το Scan count βλέπουμε το πόσες φορές έκανε scan τις σελίδες αυτές για να βγάλει το αποτέλεσμα (εάν δούμε πάνω από 1 σημαίνει ότι κάτι δεν πάει καλά δηλαδή ξαναδιαβάζει τα ίδια data). Με το lob physical reads και lob read-ahead reads βλεπουμε τον αριθμό των σελίδων που χρειάσθηκε να διαβάζει για να μας φέρει δεδομένα τύπου varchar(max), nvarchar(max), varbinary(max), text, image, xml. Αλλά δεν συμπεριλαμβάνεται το CPU Cost όπως επίσης δεν συμμετέχουν οι UDFs και CLR ρουτίνες.

Query Execution Time: Αυτή είναι η ποιο επηρεαζόμενη μετρική, και αυτό διότι μπορεί να έχω locks, resource contention στον server κατά την διάρκεια της εκτέλεσης του query. Αυτή την μετρική την βλέπουμε εάν στο session στο οποίο εκτελούμε το query μας και πριν την εκτέλεση τους εκτελέσουμε την SET STATISTICS ΤΙΜΕ ON. To αποτέλεσμα θα είναι το παρακάτω

Από το output βλέπουμε τους χρόνους που χρειάσθηκε για την προεργασία (parse and compile) και τον χρόνο εκτέλεσης.

2. Να γνωρίζει πως εκτελείτε λογικά ένα query.

Έχω ήδη γράψει για αυτό σε παλαιότερο άρθρο μου το οποίο μπορείτε να βρείτε εδώ.

3. Να μπορεί να διαβάσει και να ερμηνεύσει το Graphical ή μη Execution Plan

Για όσους δεν ξέρουν πώς να το κάνουν αυτό θα του πω απλά ότι διαβάζεται από δεξιά προς τα αριστερά και κάθε σύμβολο σημαίνει μια διαφορετική διαδικασία εκτέλεσης. Υπάρχουν αρκετά τέτοια σύμβολα, τα οποία και μπορείτε να τα βρείτε όλα μέσα στο SQL Server Books Online (BOL). Θα σας δώσω όμως μερικά hints.

a. Εάν βλέπετε παχιά βελάκια τα οποία συνδέουν το ένα task με κάποιο άλλο, αυτό σημαίνει ότι μεγάλος όγκος δεδομένων μεταφέρεται από task σε task. Γενικότερα θέλουμε να βλέπουμε λεπτά βελάκια.

b. Εάν στο query μας έχουμε join ή group by και βλέπουμε ότι αυτό υλοποιείτε με Hash Match (Inner Join) αυτό συχνά σημαίνει ότι δεν υπάρχει ο κατάλληλος index ώστε να γίνει γρηγορότερα η διαδικασία πχ. Index στο foreign key στο join ή στο πεδίο με το οποίο κάνουμε group by.

c. Εάν βλέπουμε το σύμβολο του Sort και το ποσοστό του κόστους που έχει είναι υψηλό, τότε καλό είναι να εξετάζουμε την περίπτωση να γίνει κάποιος Index στo πεδίο αυτό ώστε να απαλοιφή η διαδικασία Sort.

d. Τα μεγάλα excecution plans δεν σημαίνει ότι είναι κακά αλλά καλό είναι να έχω μικρά execution plans και αυτό διότι έχω λιγότερα βήματα εκτέλεσης στο query μου.

e. Επίσης όταν στον execution plan δω σε κάποιο task Table Scan ή Clustered Index Scan σημαίνει ότι δεν υπάρχει Index τέτοιος ώστε να γίνει καλύτερη υλοποίηση εκτέλεσης. (Σημείωση: Αν ο πίνακας σας έχει δεδομένα που είναι κάτω από 8ΜΒ τότε ακόμα και αν υπάρχει index ο SQL Server επιλέγει την συγκεκριμένη υλοποίηση διότι έχει λιγότερο κόστος σε resources και χρόνο).

Βέβαια υπάρχουν και άλλα που πρέπει να γνωρίζουμε αλλά αυτά είναι τα βασικά. Ας αρχίσουμε με αυτά και επιφυλάσσομαι στο μέλλον να γράψω και για τα άλλα