Είναι γνωστό σε όλους πλέον, ότι για να κάνουνε troubleshooting σε ένα SQL Server instance ή μία database θα ξεκινήσουν να ξετυλίγουν το κουβάρι του προβλήματος χρησιμοποιώντας τα wait types. Η γνώση αυτών είναι απαραίτητο εφόδιο που πρέπει όλοι όσοι εργάζονται με τον SQL Server, να έχουν.

Σε αυτό το άρθρο μου θα μιλήσω για τα ποιο σημαντικά wait types που αφορούν την CPU καθώς αυτή αποτελεί ένα από τα βασικά στοιχεία που συντελούν στην απόδοση του SQL Server.

CXPACKET

Το πιο συνηθισμένο wait type που βλέπει κανείς στον SQL Server είναι το CXPACKET. Είναι επίσης ένα από τα πιο παρεξηγημένα wait types και έχω δει πολλές φορές να γίνονται λάθος ρυθμίσεις ή χειρισμοί με αυτό. Αυτό που πρέπει όλοι να καταλάβουν είναι ότι το συγκεκριμένο wait type δεν είναι απαραίτητα κακό. Τουναντίον θα έλεγα ότι στις περισσότερες περιπτώσεις είναι καλό καθώς τα ερωτήματά μας εκτελούνται ταχύτερα.

Από τον SQL Server 2016 SP2 και τον SQL Server 2017 CU3, υπήρξαν ορισμένες αλλαγές στον τρόπο χειρισμού του CXPACKET και εμφανίστηκε ακόμα ένα wait type αυτό του CXCONSUMER.

Τι είναι το CXPACKET wait type;

Ένα CXPACKET εμφανίζεται κάθε φορά που εκτελείται ένα ερώτημα παράλληλα αντί σειριακά. Τα παράλληλα ερωτήματα μπορούν να έχουν ένα πλεονέκτημα απόδοσης σε σύγκριση με τα σειριακά ερωτήματα, εάν η εργασία μπορεί να διαιρεθεί μεταξύ πολλαπλών worker threads. Το πλεονέκτημα είναι μεγαλύτερο για τα ερωτήματα που επιστρέφουν μεγάλα σύνολα αποτελεσμάτων.

Τα ερωτήματα που επιστρέφουν μόνο μερικές γραμμές επωφελούνται πολύ λιγότερο από παραλληλισμό, και σε πολλές περιπτώσεις ο παραλληλισμός μπορεί να επιβραδύνει αυτά τα ερωτήματα.

Αυτό δεν σημαίνει ότι θα πρέπει να απενεργοποιήσετε τον παραλληλισμό καθώς πολλά από τα SQL Server instances που έχουμε είναι μεικτού φορτίου εργασίας με συνήθως πολλά μικρά ερωτήματα αλλά και μεγάλα που χρησιμοποιούνται για reporting ή analytics.

Τα παράλληλα ερωτήματα χρησιμοποιούν πολλαπλά worker threads για την εκτέλεση τους. (δείτε το άρθρο μου αυτό για να καταλάβετε τι είναι το worker thread). Μαζί με αυτά δημιουργείται για την εκτέλεση της ζητούμενης εργασίας ένα ακόμα thread το 0 (zero) Thread όπως ονομάζεται. Η δουλειά του thread αυτού είναι ο συντονισμός της εργασίας των άλλων worker threads. Όσο το 0 thread είναι σε αναμονή για την ολοκλήρωση της εργασίας των άλλων worker threads, θα καταγραφεί CXPACKET wait type.

Μόλις ο SQL Server Query Optimizer αποφασίσει να χρησιμοποιήσει ένα execution plan που χρησιμοποιεί παραλληλισμό, θα δείτε CXPACKET wait types να εμφανίζονται. Αυτό μπορεί να είναι εντελώς φυσιολογικό και δεν συντρέχει λόγος ανησυχίας όταν αναμένονται ερωτήματά που εκτελούνται παράλληλα. Σε αυτές τις περιπτώσεις, μπορείτε να αγνοήσετε το CXPACKET.

Υπάρχουν, ωστόσο, και οι περιπτώσεις όπου δεν θέλετε να χρησιμοποιήσετε παραλληλισμό ή όταν o παραλληλισμός επηρεάζει αρνητικά την απόδοση των ερωτημάτων σας.

Το CXPACKET σχετίζεται άμεσα με τις ρυθμίσεις παραλληλισμού που ο SQL Server έχει και μπορούμε να τις επηρεάσουμε σχετικά εύκολα αν πάμε Server Properties > Advanced > Parallelism. Εκεί θα βρούμε τις ρυθμίσεις για το παραλληλισμό οι οποίες είναι οι  Cost Threshold for Parallelism & Max Degree of Parallelism.

Cost Threshold for Parallelism

Με τo Cost Threshold for Parallelism ορίζεται το όριο κόστους με το οποίο ένα ερώτημα θα εκτελείται παράλληλα, δηλαδή εάν ένα σειριακό ερώτημα έχει ένα κόστος υψηλότερο από την τιμή που έχει ρυθμιστεί τότε ο Query Optimizer δημιουργήσει ένα parallel execution plan για την εκτέλεση του ερωτήματος. Από προεπιλογή, η ρύθμιση έχει την τιμή 5 και μπορεί να ρυθμιστεί ώστε να έχει μια τιμή μεταξύ 0 και 32.767.

Max Degree of Parallelism

Με το Max Degree of Parallelism ορίζεται ο αριθμός schedulers (cores) που θα χρησιμοποιούνται κατά την εκτέλεση ενός παραλληλισμού. Από προεπιλογή, ο αριθμός αυτός είναι 0, πράγμα που σημαίνει ότι όλοι οι διαθέσιμοι schedulers μπορούν να χρησιμοποιηθούν όταν εκτελείται ένα παράλληλο execution plan.

Από τον SQL Server 2016 και στις νεότερες αυτού εκδόσεις, μπορείτε να ρυθμίσετε τις ρυθμίσεις παραλληλισμού σε επίπεδο database από το Database Properties > Options > Database Scoped Configurations>Max DOP option με προεπιλεγμένη τιμή αυτή του 0. Η δυνατότητα ήταν κάτι που ζητούσαμε πολλά χρόνια καθώς σε ένα SQL Server instance έχουμε πολλές database με διαφορετικές ανάγκες παραλληλισμού πχ ένα SQL Server instance με SharePoint και άλλες databases.

Θα πρέπει όμως να επισημανθεί όμως ο τρόπος με τον οποίο ο SQL Server επιλέγει το τι θα χρησιμοποιήσει. Με απλά λόγια θα χρησιμοποιήσει την τιμή του MAXDOP που έχει ορισθεί στην database εφόσον η τιμή αυτή έχει ορισθεί σε κάτι διαφορετικό από την προεπιλεγμένη τιμή 0, αλλιώς θα χρησιμοποιηθεί η τιμή που υπάρχει στο instance. Αυτό σημαίνει ότι αν το MAXDOP instance είναι 4 και το MAXDOP database είναι 0 θα χρησιμοποιηθούν 4 schedulers και όχι όλοι που αρκετοί πιστεύουν.

Η ερώτηση που συχνά δέχομαι είναι για την ιδανική τιμή του MAXDOP. Την απάντηση μπορείτε να την πάρετε στο KB2806535.

Διαχείριση του CXPACKET wait type

Όπως ανέφερα και παραπάνω θα πρέπει να είστε παραπάνω από σίγουροι ότι το CXPACKET σας δημιουργεί πρόβλημα για να ασχοληθείτε μαζί του.

Η συχνότερη λύση που κυκλοφορεί στο internet συμβουλεύει να ορισθεί η τιμή 1 στο MAXDOP. Δεν είναι η καλύτερη ιδέα για την πλειονότητα των περιπτώσεων. Ναι θα ελαχιστοποιηθεί η εμφάνιση του αλλά θα υπάρχουν ερωτήματα που εκτελούνταν παράλληλα θα εκτελούνται πλέον σειριακά με αποτέλεσμα να διαρκούν περισσότερο χρόνο.

Ο σωστός τρόπος αντιμετώπισης είναι να ρυθμιστούν κατάλληλα τα Cost Threshold for Parallelism & Max Degree of Parallelism options. Σίγουρα δεν το ευκολότερο καθώς θα πρέπει το query να ελεγχθεί ως προς την συμπεριφορά του σειριακά και παράλληλα, αλλά σε αυτές τις περιπτώσεις θα ξεκινήσετε με αυτά που εκτελούνται συχνότερα.

Skewed Workloads

Η περίπτωση που πρέπει πραγματικά να εξεταστεί είναι αυτή των skewed workloads όπως ονομάζεται.

Όπως εύκολα μπορείτε να καταλάβετε από την παρακάτω εικόνα skewed workload έχουμε όταν τα worker thread δεν κάνουν τον ίδιο ποσό εργασίας, με συνέπεια το 0 thread να περιμένει να ολοκληρωθεί αυτό που διαρκεί περισσότερο. Αυτό συμβαίνει συνήθως όταν δεν έχουμε σωστά ενημερωμένα στατιστικά στους indexes μας, για αυτό φροντίστε να έχετε πάντα σωστά ενημερωμένα στατιστικά.

 

Find Skewed Workload Το πως μπορεί κάποιος να δει αν έχει αυτό το φαινόμενο είναι απλό. Στο execution plan στον operator που παραλληλίζει με F4 βλέπουμε τα properties και στο Misc κάνουμε expand το Actual Number of Rows όπου θα δούμε το πόσες γραμμές διάβασε κάθε worker thread. To 0 thread σωστά δεν θα δείχνει γραμμές. Επίσης θα πρέπει να γίνει κατανοητό ότι δεν μιλάμε για μικρές διαφορές όπως φαίνεται και στην παρακάτω εικόνα, αλλά για σημαντικές διαφορές, και μην περιμένετε να σας πω νούμερα ή ποσοστά καθώς δεν υπάρχουν τέτοια αλλά αξιολογήστε την κάθε περίπτωση.

CXCONSUMER

Όπως ανέφερα και παραπάνω από τον SQL Server 2016 SP2 και SQL Server 2017 CU3, η Microsoft προχώρησε σε μια αλλαγή που αφορά τον τρόπο με τον οποίο καταγράφετε ο παραλληλισμός των ερωτημάτων, με σκοπό να κάνει ευκολότερο το προσδιορισμό του συγκεκριμένου wait type ως προς το πότε είναι προβληματικό ή όχι.

Από όσα παραπάνω έχω περιγράψει ο παραλληλισμός αποτελείται από δύο μέρη: τους producers και τους consumers. To Zero Thread είναι ο producer και όλα τα άλλα worker thread οι consumers που εκτελούν την εργασία.

Πριν από την αλλαγή αυτή ήταν αδύνατο να καταλάβουμε αν οι consumers περιμένουν τους producers ή το αντίθετο καθώς όλα μαζί καταγράφονταν σαν CXPACKET.

Με την αλλαγή αυτή πλέον έχουμε το CXCONSUMER που συμβαίνει κάθε φορά που ένα consumer thread περιμένει το producer να του στείλει εργασία. Συνήθως δεν το αξιολογούμε στα ευρήματα μας καθώς πλέον το CXPACKET είναι ξεκάθαρο.

Είναι ξεκάθαρο διότι πλέον δεν περιέχει τους χρόνους των consumers και ναι μεν δείχνει ότι υπάρχει παραλληλισμός αλλά και ότι οι υψηλοί χρόνοι αναμονής υποδεικνύουν σαφέστερα πιθανό ζήτημα σχετικά με τις λειτουργίες παραλληλισμού, όπως για παράδειγμα τα thread έχουν προβλήματα με buffers ή thread synchronization. Αυτό δεν αλλάζει όμως σε τίποτα τα όσα παραπάνω έχω αναφέρει καθώς και τους τρόπους δράσης για την αντιμετώπιση του υψηλού παραλληλισμού.

SOS_SCHEDULER_YIELD

To SOS_SCHEDULER_YIELD είναι ένα wait type που εμφανίζεται συχνά όπως και το CXPACKET και δεν υποδεικνύει απαραίτητα ότι υπάρχει πρόβλημα στο SQL Server instance. Εμφανίζεται μόλις ξεκινήσετε την εκτέλεση ερωτημάτων στο SQL Server και σχετίζεται στενά με το scheduling του.

Πως δουλεύει ένας SCHEDULER;

Όπως έχω περιγράψει στο άρθρο μου αυτό κάθε ερώτημα διαιρείται σε ένα η περισσότερα worker threads. Τα οποία εκτελούνται στον εκάστοτε ανατιθέμενο scheduler που στην ουσία είναι το ανατιθέμενο cpu core.

Κάθε φορά στο core μπορεί να εκτελείται ένα μόνο worker thread για συγκεκριμένο quantum (4ms).

Όπως έχω ήδη πει στο παραπάνω άρθρο το SQLOS υπάρχει καθώς ο SQL Server θέλει ένα διαφορετικό τρόπο διαχείρισης των threads (cooperative non-preemptive) για να διασφαλίσει την βέλτιστη απόδοση των διαθέσιμων πόρων.

Τα thread που περιμένουν στην ουρά για κάνουν χρήση του core και η ουρά αυτή είναι γνωστή σαν Runnable Queue και όσο είναι σε αυτή το status τους είναι RUNNABLE. Από την στιγμή που θα μπουν για εκτέλεση στο core αυτό γίνεται RUNNING.

Λόγο του ότι το SQLOS θέλει να πετύχει την βέλτιστη χρήση, κάθε thread που είναι στο core δηλαδή σε κατάσταση RUNNING αν περιμένει για κάποιο resources εκτός αυτού (core) δεν το αφήνει να ολοκληρώσει το χρόνο του quantum και το μεταφέρει σε μία λίστα γνωστή σαν Waiter List και το status του είναι SUSPENDED μέχρι το resources που περιμένει να είναι διαθέσιμο. Όταν το resource γίνει διαθέσιμο τότε μεταφέρει το συγκεκριμένο thread στην ουρά για να συνεχίσει την εκτέλεση του.

Αυτός ο κύκλος αποτυπώνεται στην παρακάτω εικόνα αλλά θα πρέπει να επισημάνω ότι κάθε νέο worker thread πάντα ξεκινάει από την waiter list μέχρι να βρεθεί διαθέσιμο core.

Τι είναι το SOS_SCHEDULER_YIELD wait type;

Αν στην περίπτωση που δεν υπάρχει κάποιο resource άλλο που χρειάζεται, το thread εκτελείται για όσο ορίζει το quantum και με την λήξη αυτή μεταφέρεται πάλι στην Runnable Queue. Αυτή είναι η στιγμή που δημιουργείται ένα SOS_SCHEDULER_YIELD wait type.

Διαχείριση του SOS_SCHEDULER_YIELD wait type

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

Ανησυχητικό αρχίζει να γίνεται όταν υπάρχει πολύ μεγάλος αριθμό από wait time σε σχέση με τα συνηθισμένα και συνδυάστηκα με μεγάλο αριθμό από waiting task, δηλαδή βλέπουμε τις κολώνες wait_time_ms και waiting_task_count αντίστοιχα από την sys.dm_os_wait_stats DMV.

Σε μια τέτοια περίπτωση θα έχετε ένα αρκετά απασχολημένο SQL Server Instance που θα υποφέρει από CPU pressure/usage που μπορεί να οδηγήσει σε spinlock contentions.

Μια παρατήρηση για τα συνηθισμένα.

Αν δεν έχετε baseline, και δεν συγκεντρώνετε σε αυτό τα wait types/stats δεν πρόκειται ποτέ να βγάλετε σοβαρό συμπέρασμα και πάντα θα αναρωτιέστε αν αυτό που βλέπετε είναι καλό ή όχι διότι το SOS_SCHEDULER_YIELD δεν καταγράφεται στην sys._dm_os_waiting_tasks DMV.

Η αντίδραση σε αυτές τις περιπτώσεις είναι να δείτε τα queries που κάνουν το μεγαλύτερο CPU usage ή αν έχουμε Query Store δείτε τα Top Resource Consuming Queries ή δείτε τα αποτελέσματα της sys.dm_exec_query_stats για να βρείτε τα cpu expensive queries.

THREADPOOL

Σπάνιες πρέπει να είναι οι περιπτώσεις που θα έχετε αυτό το wait type, καθώς αν το έχετε συχνά τότε έχετε πολλά προβλήματα με κυριότερο ένα unresponsive SQL Server instance.<\p>

Τι είναι το THREADPOOL wait type;

Ο SQL Server έχει ένα worker thread pool από το οποίο βρίσκει το πρώτο διαθέσιμο worker thread στο οποίο ανατίθεται το εκάστοτε task που δημιουργείται από ένα request για να γίνει η εκτέλεση του.

Στην περίπτωση που δεν υπάρχει διαθέσιμο worker thread και έχει καλύψει το μέγιστο αριθμό των διαθέσιμων threads που το pool έχει τότε αυτό περιμένει στην ουρά μέχρι να βρεθεί κάποιο διαθέσιμο.

Αυτή είναι περίπτωση που θα καταγραφεί ένα τέτοιο wait type.

Max Worker Threads

O SQL Server έχει προς χρήση τόσα worker threads όσα έχουν ορισθεί με το max worker thread option, το οποίο by default έχει την τιμή μηδέν (0).

Ο τρόπος με τον οποίο ο SQL Server υπολογίζει κατά την εκκίνηση του service το πόσα worker thread θα έχει διαθέσιμα βγαίνει από τον εξής τύπο υπολογισμού max worker thread = 512 + ( (number of logical processors - 4 ) * 16 ) για τα x64 systems με την επισήμανση ότι για όσα έχουν <=4 logical cores το max worker thread = 512.

Μπορείτε να βρείτε το πόσα έχει το SQL Server instance με το παρακάτω ερώτημα

SELECT max_workers_count FROM sys.dm_os_sys_info;

Διαχείριση του THREADPOOL wait type

Η συνηθέστερη συμβουλή που θα βρείτε στο internet είναι αυτή που σας προτρέπει να αυξήσετε τον αριθμό των max worker threads. Αυτό όμως σημαίνει ότι πρέπει να έχετε διαθέσιμη μνήμη καθώς για κάθε ένα χρειάζονται 2048ΚΒ μνήμης. Αν δεν έχετε μνήμη τότε μπαίνει ακόμα ένα πρόβλημα στην εξίσωση.

Το συγκεκριμένο wait type δεν είναι εύκολο στην διαχείριση του και ο λόγος είναι όταν συμβαίνει το instance δεν απαντάει σε κανέναν ερώτημα. Για αυτό θα πρέπει να έχετε την δυνατότητα να συνδεθείτε κάνοντας χρήση του Dedicated Administrator Connection (DAC) για να μπορέσετε να βρείτε την αιτία.

Μία από τις αιτίες για την εμφάνιση του είναι ο παραλληλισμός των ερωτημάτων συνδυαστικά με τον αριθμό των cores και το πως έχει ορισθεί το MAXDOP & Cost Threshold of Parallelism.

Άλλη μια ακόμα αιτία είναι ταυτόχρονη και μεγάλη αύξηση των αριθμών των χρηστών που συνδέονται στον SQL Server και εκτελούν ταυτόχρονα ερωτήματα ή υπάρχουν εφαρμογές που δημιουργούν πολλαπλά connections στον SQL Server.

Τέλος ακόμα μια αιτία είναι ο αριθμός των locks που υπάρχουν σε μια database σε συνδυασμό με το χρόνο που αυτά παραμένουν ενεργά.

Γενικότερα όταν δείτε αυτό το wait type πρέπει να σημάνει συναγερμός και να διερευνηθούν οι παραπάνω αιτίες.

Επίλογος

Ελπίζω με όλα τα παραπάνω να έριξα λίγο φως στο τι σημαίνουν κάποια πράγματα στον SQL Server και να έγινε κατανοητό ότι το προϊόν είναι από τα αρκετά ενδιαφέροντα και περίπλοκα προϊόντα που υπάρχουν.

Αν σας άρεσε αυτό το άρθρο ή όχι πείτε το μου με τα σχόλια σας από κάτω.

Καλή Χρονιά σε όλες και όλους.

//Antonios Chatzipavlis