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

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

Είναι αρκετά σημαντικό για όσους ασχολούνται με τον SQL Server να γνωρίσουν τον τρόπο αυτό αλλά και τα εμπλεκόμενα στοιχεία που συμβάλουν στην υλοποίηση των εργασιών που γίνονται καθώς η γνώση αυτή είναι απαραίτητη για να γίνει ευκολότερο το troubleshooting είτε σε on prem είτε σε azure environment.

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

Session

Ένα session είναι στην ουσία το connection που γίνεται από οποιαδήποτε εφαρμογή που ζητάει στοιχεία από μια database στο SQL Server ή γενικότερα θέλει να εκτελέσει μια ενέργεια σε αυτόν.

Εύκολα μπορούμε να δούμε ποια είναι αυτά τα sessions που είναι ενεργά χρησιμοποιώντας την sys.dm_exec_sessions DMV επισημαίνοντας ότι με τον όρο ενεργά αναφερόμαστε σε αυτά που ακόμα έχουν ανοικτό το connection από την εφαρμογή χωρίς αυτό να σημαίνει ότι εκτελούν απαραίτητα κάτι.

Άξιο αναφοράς είναι επίσης ότι όλα τα sessions που έχουν session_id μικρότερο του 50 είναι αυτά που χρησιμοποιούνται από το SQL Server για τις εσωτερικές του εργασίες. Αυτό όμως δεν είναι πάντα αληθές καθώς αν έχετε ένα πραγματικά μεγάλο σε χρήση SQL Server τότε αυτός θα χρειαστεί για τις εσωτερικές του εργασίες και sessions με session_id μεγαλύτερα του 50. Ο ασφαλέστερος τρόπος για να μπορεί κάποιος να ξεχωρίσει τα internals sessions από τα user sessions είναι στο query που θα εκτελέσει να έχει στο where clause το is_user_process = 0 or 1 (0 : internal , 1 : user).

Η συγκεκριμένη DMV επιστρέφει ένα αρκετά μεγάλο αριθμό από κολώνες που προσφέρουν εξαιρετικές πληροφορίες για τα sessions. Αν θα έπρεπε να επισημανθούν κάποιες είναι σίγουρα οι host_process_id η οποία επιστρέφει το process id (PID) του client που το όνομα του μπορούμε να το αντλήσουμε από την host_name. Επίσης σημαντική είναι η cpu_time που δείχνει το χρόνο που αυτό το session έχει απασχολήσει συνολικά την cpu από την ώρα που έχει συνδεθεί και είναι σε milliseconds και έτσι μπορούμε να εντοπίσουμε αυτά που κάνουμε μεγάλη κατανάλωση της cpu.

Αφού είπαμε για την cpu πρέπει να πούμε και για την μνήμη που το κάθε session έχει καταναλώσει και αυτό το βλέπουμε με την memory_usage. Ο αριθμός που αυτή δείχνει είναι σε σελίδες των 8ΚΒ.

Τέλος είναι η κολώνα που δείχνει το status του session με τις περισσότερο συνήθης τιμές να είναι "Running" που σημαίνει ότι κάτι εκτελεί την ώρα που εκτελέσαμε το DMV αυτό ή "Sleeping" που σημαίνει ότι είναι συνδεδεμένο αλλά δεν κάνει τίποτα κατά την στιγμή της εκτέλεσης της DMV και με τον τρόπο αυτό μπορούμε να βρούμε τα sessions που έχουν μείνει ανοικτά χωρίς να κάνουν τίποτα για μεγάλο χρονικό διάστημα συνδυαστικά φυσικά με την login_time κολώνα.

Request

Ένα request είναι απλά η οποιαδήποτε ενέργεια που κάθε φορά ένα session εκτελεί ή ζητά να εκτελέσει μέσω του SQL Server execution engine. Η sys.dm_exec_requests DMV, είναι αυτή που θα επιστρέψει αυτά που την στιγμή της εκτέλεσης της είναι σε φάση εκτέλεσης.

Προσωπικά την θεωρώ από τις σημαντικότερες DMV καθώς παρέχει πλήθος πληροφοριών που αφορούν το εκάστοτε request όπως πληροφορίες που αφορούν performance, execution plan, locking, transactions, session settings κ.α. που βοηθούν σημαντικά στο troubleshooting.

Ο συνδυασμός της με την sys.dm_exec_sql_text DMF είναι σχεδόν πάντα χρήσιμος καθώς βλέπουμε και το query text.

Task

Ένα task είναι η πραγματική ενέργεια που πρέπει να γίνει από το SQLOS όμως δεν είναι αυτό που την εκτελεί πραγματικά.

Κάθε φορά που ένα request λαμβάνεται από τον SQL Server ένα ή περισσότερα tasks θα δημιουργηθούν με σκοπό να ικανοποιήσουν το request αυτό. O αριθμός των tasks που θα δημιουργηθούν εξαρτάται από το αν για παράδειγμα ένα query θα παραλληλίσει ή όχι.

Για να δούμε τα tasks αυτά χρησιμοποιούμε την sys.dm_os_tasks DMV.

Η συγκεκριμένη DMV μας επιστρέφει πάντα αποτελέσματα ακόμα και αν δεν υπάρχουν ενεργά sessions καθώς υπάρχουν πάντα τα internal sessions του SQL Server. Αυτό σημαίνει ότι θα πρέπει να βλέπουμε την τιμή στην κολώνα session_id ώστε να είμαστε σε θέση να καταλάβουμε αν είναι internal ή user session (είτε με τον "κανόνα" του κάτω από το 50 είτε με join με την DMV που μας επιστρέφει τα sessions και με φίλτρο στο where όπως έχω αναφέρει παραπάνω).

Πραγματικά δυσκολεύομαι να βρω μια λιγότερο χρήσιμη κολώνα από αυτή την DMV. Θα έλεγα ότι οι αγαπημένες μου είναι η task_state που δείχνει την κατάσταση του task με αρκετά ενδιαφέρουσες επιστρεφόμενες τιμές, και τη scheduler_ID που είναι το ID του scheduler για τον οποίο θα μιλήσουμε παρακάτω.

Worker Thread & OS Thread

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

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

O SQL Server έχει προς χρήση τόσα worker threads όσα έχουν ορισθεί με το max worker thread option, το οποίο by default έχει την τιμή μηδέν (0) και σπάνιες είναι οι περιπτώσεις που θα χρειαστεί να το αλλάξουμε. Η συμβουλή μου είναι να μην το αλλάξετε εκτός και αν έχετε σοβαρά εκτιμήσει ότι έχετε τέτοιες περιπτώσεις (THREADPOOL Wait Type να είναι το υψηλότερο σε σχέση με τα άλλα).

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

Φυσικά και υπάρχει DMV που μας δείχνει τα worker threads και η οποία είναι η sys.dm_os_workers μία αρκετά δύσκολη και περίπλοκη στην κατανόηση DMV, και η συμβουλή μου είναι να διαβαστεί προσεκτικά το documentations της καθώς υπάρχουν και στοιχεία που δεν δίνει εξήγηση για αυτά η Microsoft.

Ανέφερα παραπάνω ότι το worker thread είναι και αυτό που κάνει την εκτέλεση. Αυτό δεν είναι ακριβώς σωστό καθώς τα worker threads είναι κομμάτι του SQLOS αλλά η πραγματική εκτέλεση τους γίνεται σε επίπεδο λειτουργικού OS thread και με την sys.dm_os_threads μπορούμε να δούμε την λίστα όλων των SQLOS threads που εκτελούνται κάτω από το SQL Server process (OS level). Ενδιαφέρον σε αυτή έχουν οι κολώνες started_by_sqlserver, status, os_thread_id.

Scheduler

Κάθε φορά που ένα task θέλει processor time ο scheduler λαμβάνει ένα worker thread του οποίο το task εκτελεί. Αυτή είναι και η βασική εργασία του. Κάθε scheduler αντιστοιχίζεται σε ένα logical core.

Για αυτούς που πιστεύουν ότι πρέπει να γράψουν multi-threaded applications για τον SQL Server θα τους απογοητεύσω καθώς αυτός ήδη είναι multi-threaded και δεν χρειάζεται να του πούμε να το κάνει, το κάνει ήδη μέσω των schedulers.

Μπορούμε να δούμε πληροφορίες για τους schedulers μέσω της sys.dm_os_schedulers DMV.

Σε αυτή θα δούμε περισσότερες γραμμές από όσα cores έχουμε καθώς ο SQL Server χρησιμοποιεί δικούς του schedulers για τα internal tasks και αυτά είναι χαρακτηρισμένα σαν HIDDEN ONLINE στην status κολώνα. Επίσης υπάρχει το VISIBLE ONLINE (DAC) για τo Dedicated Administrator Connection για τις περιπτώσεις που έχουμε ένα instance που δεν ανταποκρίνεται και θέλουμε να κάνουμε troubleshooting. Tα πραγματικά cores που έχουμε είναι αυτά που είναι χαρακτηρισμένα σαν VISIBLE ONLINE.

Σε αυτό το DMV ενδιαφέρον έχουν οι εξής κολώνες:

current_workers_count στην οποία βλέπουμε τα worker threads που έχουν ανατεθεί στον συγκεκριμένο scheduler. Ο αριθμός αυτό δείχνει όλα τα worker threads είτε έχουν είτε δεν έχουν κάποιο task να κάνουν.

active_workers_count στην οποία βλέπουμε τα worker threads που είναι ενεργά έχουν δηλαδή κάποιο task να κάνουν χωρίς αυτό να σημαίνει ότι το κάνουν καθώς θα πρέπει να δούμε το status του worker thread αν είναι RUNNING (είναι στην CPU και εκτελείται), RUNNABLE (περιμένει στην ουρά για να μπει για εκτέλεση στην CPU), SUSPENDED (είναι στην waiting list καθώς κάτι περιμένει να γίνει και για αυτό θα πρέπει να δούμε τα wait stats, δεν είναι στην CPU ούτε στην ουρά για αυτή).

work_queue_count στην οποία βλέπουμε insights για το πόσα tasks περιμένουν για ένα ελεύθερο worker thread και αν αυτό το νούμερο είναι μεγάλο μπορεί να σημαίνει CPU pressure.

Putting it all together

Με βάση την παραπάνω λογική ερχόμαστε να αντιμετωπίσουμε τα διάφορα προβλήματα που έχουμε. Ξέρω δεν είναι εύκολο τουλάχιστον στην αρχή, θέλει εμπειρία και κυρίως εξάσκηση σε περιβάλλον lab με συγκεκριμένα σενάρια ώστε να είμαστε σε θέση να ελέγξουμε και να κατανοήσουμε. Όποιος πιστέψει ότι θα τα μάθει καλύτερα στην πράξη δηλαδή πάνω σε παραγωγικό σύστημα έχει ήδη χάσει και χαθεί.

Στο παρακάτω query δεν βάζω συγκεκριμένες κολώνες καθώς κάθε φορά μπορεί να θέλουμε να δούμε διαφορετικά πράγματα. Αυτό που με ενδιαφέρει είναι να δείξω πως όλα τα παραπάνω αναφερόμενα DMVs μπορούν να συνδυαστούν σε ένα query. Πριν καταναλώσετε αυτό όπως είναι θα ήθελα να το εκτελέσετε βήμα - βήμα ώστε να δείτε πραγματικά τι γίνεται σε ένα συγκεκριμένο session/request.

select *
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
inner join sys.dm_os_tasks as t on t.request_id = r.request_id
inner join sys.dm_os_workers as w on w.worker_address = t.worker_address
inner join sys.dm_os_threads as ot on ot.worker_address = w.worker_address
inner join sys.dm_os_schedulers as sh on sh.scheduler_address = w.scheduler_address ;

Αυτό είναι το τελευταίο άρθρο μου για το 2019.

Εύχομαι σε όλες και όλους ένα ευτυχισμένο με υγεία πρώτα και πάνω από όλα 2020.

//antonch