Είναι γνωστό, εδώ και 7 χρόνια, ότι ο SQL Server Profiler είναι σε κατάσταση απόσυρσης και ότι ο διάδοχος τους είναι τα Extended Events. Με αυτά μπορούμε να κάνουμε πολλά πράγματα που μας βοηθούν στον εντοπισμό και την επίλυση προβλημάτων.

Σε αυτό το άρθρο θα ασχοληθώ με μερικές περιπτώσεις που βλέπω συχνά.

Execution Time outs

Είναι η γνωστή περίπτωση που ένα query τρέχει για περισσότερο χρόνο από αυτό που έχει ορισθεί σαν query time out στη εφαρμογή. Αρκετές φορές είναι δύσκολο να το εντοπίσει κανείς ειδικά αν δεν έχει application logs.

Για να εντοπίσουμε αυτά με XE θα πρέπει να δημιουργήσουμε ένα XE session με τα events

sqlserver.sp_statement_starting και sqlserver.sp_statement_completed (for stored procedures)

sqlserver.sql_statement_starting και sqlserver.sql_statement_completed (for ad-hoc queries)

Αυτό το σενάριο απαιτεί σαν target event pairing και αυτό γιατί αν κάποιο starting event έχει time out δεν θα υπάρχει το αντίστοιχο completed.

Troubleshooting ASYNC_NETWORK_IO

Το ASYNC_NETWORK_IO wait type συμβαίνει ότι ο SQL Server περιμένει την εφαρμογή να καταναλώσει τα δεδομένα τα οποία ζήτησε. Με απλά λόγια η εφαρμογή ζήτησε δεδομένα με ένα query ο SQL Server απάντησε άμεσα αλλά ο κώδικας στην εφαρμογή διαβάζει αυτά γραμμή – γραμμή (πχ data reader)

Για να εντοπίσουμε το θέμα αυτό θα πρέπει να φτιάξουμε ένα XE session με τo event sqlos.wait_info στο οποίο θα βάλουμε filter value NETWORK_IO.

Για target επιλέγουμε histogram καθώς μπορούμε να κάνουμε έρευνα είτε με το client application name είτε με το client host name.

Tracking Errors and Error Handling in Transact-SQL

Aν θέλουμε να έχουμε μια εικόνα για τα errors είτε είναι σε TRY…CATCH block είτε είναι unhandled μπορούμε να δημιουργήσουμε ένα XE Session με το event sqlserver.error_reported και μπορούμε να καταλάβουμε αν είναι handled / unhandled βλέποντας την τιμή της is_intercepted κολώνας.

Για αυτό μπορούμε να χρησιμοποιήσουμε οποιοδήποτε target.

Tracking Recompilations

Είναι γνωστό ότι ο υψηλός αριθμός από query execution plan recompilations δείχνουν προβλήματα απόδοσης και CPU pressure.

Υπάρχουν οι windows performance counters αλλά αν θέλουμε περισσότερες λεπτομέρειες για καλύτερη έρευνα θα πρέπει να φτιάξουμε ένα XE Session με το event sqlserver.sql_statement_recompile το οποίο μπορεί να μας δώσει αυτές.

Για αυτό χρησιμοποιούμε σαν target histogram καθώς μπορούμε να κάνουμε grouping με την κολώνα source_database_id για να βρούμε την database που έχει τα περισσότερα recompilations ή με τα statement/object_id για να βρούμε τα περισσότερό recompiled statements.

Latch Contention in tempdb

Latch contention συμβαίνει στις allocation bitmap pages όταν έχουμε μεγάλο αριθμό από temporary objects που δημιουργούνται/διαγράφονται ταυτόχρονα.

Αυτό δημιουργεί προβλήματα απόδοσης στην tempdb.

Για να ερευνήσουμε το πρόβλημα αυτό δημιουργούμε ένα XE Session με το event latch_suspend_end με βάση τα database_id, file_id, page_id και με predicate to divide_evenly_by_int64.

Για target χρησιμοποιούμε histogram καθώς μπορούμε να κάνουμε group by με τα παραπάνω

Tracking Lock Escalation

Lock escalation συμβαίνει όταν έχουμε πάνω από 5,000 locks να απαιτούνται σε ένα single session ή κάτω από ειδικές συνθήκες μνήμης.

Για να ερευνήσουμε αυτό δημιουργούμε ένα XE Session με το sqlserver.lock_escalation event το οποίο μας δίνει όλες τις πληροφορίες που θέλουμε.

Μπορούμε να χρησιμοποιήσουμε οποιαδήποτε επιλογή για target.

Tracking Database and Object Usage

Το tracking database and objects usage βοηθάει στο να καταλάβουμε δύο σημαντικά στοιχεία.

Πρώτο είναι να δούμε ποια είναι η περισσότερο χρησιμοποιούμενη database και δεύτερο ποια είναι τα περισσότερο χρησιμοποιούμενα objects σε κάθε database.

Αυτή την πληροφορία την χρειαζόμαστε για να αποφασίσουμε αν πρέπει να πάμε την βάση σε γρηγορότερο storage ή αν πρέπει να πάμε σε memory-optimized tables κ.α.

Για αυτό και δημιουργούμε ένα XE Session με το sqlserver.lock_acquired event μπορεί να μας βοηθήσει για το σκοπό αυτό στις περισσότερες περιπτώσεις καθώς ο μεγάλος αριθμός από locks συνήθως δείχνει και την μεγάλη χρήση.

Για να δούμε την χρήση των databases χρησιμοποιούμε σαν target histogram με grouping στο database_id.

Για να δούμε την χρήση των οbject χρησιμοποιούμε σαν target histogram και εστιάζουμε στα SCH_M / SCH_S locks κανόντας grouping με object_id.

Troubleshooting Orphaned Transactions

Orphaned transactions είναι τα open transactions που δεν έχουν γίνει ούτε committed ούτε rolled back και σαν συνέπεια είναι να κρατούνται locks ανοικτά με αποτέλεσμα να έχω πολλά προβλήματα όπως transaction log growth, blocking, και σε κάποιες περιπτώσεις block σε όλο το SQL Server instance.

Για τον εντοπισμό αυτών δημιουργούμε ένα XE Session με τα database_transaction_begin και database_transaction_end events και χρησιμοποιούμε event pairing target.

Χρησιμοποιούμε το tsql_frame action για να εντοπίσουμε την γραμμή του κώδικα που ξεκίνησε ένα orphaned transaction.

Tracking Session-level Wait Stats

Είναι γνωστό ότι μπορούμε να δούμε τα wait stats χρησιμοποιώντας την sys.dm_os_wait_stats DMV που δείχνει aggregated στοιχεία σε instance level. Αυτό είναι σημαντικό γιατί έχουμε την μεγάλη εικόνα αλλά δεν είναι πάντα αυτό που θέλουμε για να κάνουμε fine-grained troubleshooting.

Επίσης είναι γνωστό ότι μπορούμε να δούμε wait stats ανά session χρησιμοποιώντας την sys.dm_exec_session_wait_stats DMV αλλά πάντα δεν είναι εύκολα να συγκεντρώσουμε αυτά τα στοιχεία ειδικά σε SQL Serverς που είναι αρκετά απασχολημένοι με πολλά concurrent database sessions.

Για αυτό και δημιουργούμε ένα XE Session με το sqlos.wait_info event το οποίο μπορεί να κάνει track τα waits σε multiple concurrent sessions με οποιοδήποτε target θέλουμε.

Tracking Problematic Page Splits

Για το τέλος άφησα το γεγονός του page splitting. Ένα γεγονός που συμβαίνει πάντα στο SQL Server και έχω μιλήσει και γράψει αρκετά για αυτό στο παρελθόν. Υπάρχουν καλά (page splits for new allocations) και κακά (mid-page splits) page splits.

Tα mid-page splits δημιουργούν fragmentation και περισσότερα transaction log records εξ’ αιτίας του data movement.

Για να τον εντοπισμό γενικά των page splits η δημιουργία ενός XE Event με την χρήση του sqlserver.page_split event είναι αρκετή αλλά δεν μου δείχνει τα κακά mid-page splits μόνο. Για αυτό βάζουμε στο XE Session και το sqlserver.transaction_log event και κάνουμε tracking το LOP_DELETE_SPLIT operation για να εντοπίσουμε κακά page splits.

Ένα histogram target might είναι στις περιπτώσεις ιδανικό καθώς μπορούμε να κάνουμε grouping είτε database_id (για να βρούμε τις database με τα περιοσσότερα page splits) είτε μέσα σε μια συγκεκριμένη database με το alloc_unit_id (για α βρούμε τους indexes με τα περισσότεραmost page splits).

//antonch