Μια συνηθισμένη ημέρα στη δουλεία είναι αρκετά ενδιαφέρουσα. Ιδιαίτερα όταν έχεις να προσέχεις μεγάλο αριθμό από SQL Server instance και databases που γίνονται access από εφαρμογές που μπορεί να χρησιμοποιούνται από χιλιάδες αλλά και εκατομμύρια χρήστες.
Όσο καλά να είναι όλα φτιαγμένα πάντα μα πάντα θα υπάρξει αυτό που θα σε κάνει να σηκώσεις τα μανίκια για να βρεις αρχικά τι είναι και έπειτα να δώσεις λύση.
Από τα συνηθισμένα πράγματα είναι το locking και εξαιτίας αυτού να υπάρχουν processes που περιμένουν κάποιο άλλο να τελειώσει και το οποίο έχει δημιουργήσει διάφορα locks. Δεν είναι κακό πράγμα το locking, αλλά συμβαίνει πάντα όταν δεν το θέλεις και κυρίως πρέπει να το εντοπίσεις με σκοπό να δεις πως μπορείς και τι μπορείς να κάνεις για να το διορθώσεις.
Αυτό που θέλεις όμως είναι να έχεις κάτι στα χέρια σου που να σε βοηθάει στον εντοπισμό αλλά και κάπως να σε ειδοποιεί.
Performance Monitor
Αγαπημένο εργαλείο είναι το Performance Monitor στο οποίο μπορείς να βλέπεις τον αριθμό των Blocked Processes. Συνήθως στο Performance monitor για να κάνουμε αυτή την παρατήρηση επιλέγουμε από το SQLServer:General Statistics object τον Processes blocked counter.
Χρήσιμο όμως είναι να βλέπουμε και τον αριθμό των deadlocks (SQLServer:Locks object, Number of Deadlocks/sec counter) αλλά και το πόσο χρόνο έχω σε locks (SQLServer:Locks object, Lock Wait Time (ms)).
Όταν θέλεις να μετρήσεις κάτι τέτοιο πρέπει να βάλεις τους μετρητές κάθε φορά που εκτελείς το Performance Monitor αλλά επειδή θέλεις αυτό να το έχεις έτοιμο προς χρήση υπάρχει μια απλή και όμορφη λύση.
Από το Start>Run εκτελούμε το perfmon /sys και μας ανοίγει το να ορίσουμε μετρητές. Επιλέγουμε τους παραπάνω και από το File menu επιλέγουμε Save Settings As και δίνουμε ένα όνομα για το αρχείο που θα αποθηκευτεί. Το file extension είναι .PerfmonCfg.
SQL Server Agent Alert
Ένας καλός φίλος είναι πάντα τα SQL Server alerts τα οποία σε συνδυασμό με το Database Mail μπορούν σχεδόν άμεσα να σε ειδοποιούν για αυτό που τα έχεις φτιάξει.
Έτσι μπορούμε να φτιάξουμε ένα alert πάνω στον counter Processed blocked του SQLServer:General Statistics όπως ακριβώς φαίνεται στην εικόνα παρακάτω απλά θα πρέπει να ορίσουμε τους notified operators στο Response Page του alert.
Βέβαια με αυτόν τον τρόπο θα γεμίσουμε email σε ένα παραγωγικό heavily transactional instance. Παρόλα αυτά όμως μπορούμε να αυξήσουμε τον αριθμό σε τέτοιο έναν που να είναι αυτό που το σύστημα αρχίζει πραγματικά να καταρρέει, αλλά αυτό θέλει monitoring.
SQL Server Profiler/Trace Blocked Process report
Ακόμα ένας καλός φίλος ( αν χρησιμοποιηθεί σωστά) είναι τα SQL Server Trace / Profiler. Φυσικά μεταξυ των δύο επιλέγουμε τα Traces καθώς απαιτούν λιγότερους πόρους σε σχέση με τον Profiler. Παρόλα αυτά όμως χρησιμοποιούμε τον Profiler για να γλυτώσουμε την δημιουργία του script που χρειάζεται για την ενεργοποίηση των SQL Server Traces.
Κάτι τέτοιο γίνεται με τον εξής τρόπο:
- Ανοίγουμε τον SQL Server Profiler και επιλέγουμε τον server που θέλουμε
- Δημιούργουμε ένα blank trace από την επιλογή των trace templates
- Σε αυτό επιλέγουμε το event και τις κολώνες όπως φαίνονται στην παρακάτω εικόνα.
- Στο General tab επιλέγουμε το Save to file και επιλέγουμε δίσκο και όνομα αρχείου. Επίσης καλό είναι κρατήσουμε το Set maximum file size στα 5ΜΒ, όπως επίσης να επιλέξουμε τα Enable file rollover, Server processes trace data.
- Πατάμε Run και σταματάμε αμέσως το trace καθώς θέλουμε να το script αυτού.
- Αυτό βγαίνει εύκολα με το να επιλέγουμε από το menu File > Export > Script Trace definition > For SQL Server 2005 - SQL11 και να δώσουμε ένα όνομα αρχείου για το .sql που θα δημιουργηθεί.
Στο σημείο αυτό θα πρέπει να επισημανθεί ότι ακόμα και να αφήσουμε το profiler να τρέχει δεν θα δούμε αποτελέσματα καθώς λείπει το βήμα ενεργοποίησης του Blocked Processes Report το οποίο θα δούμε παρακάτω.
Επίσης στο script αυτό θα πρέπει να κάνουμε μια μικρή προσθήκη καθώς δεν θέλουμε αυτό να εκτελείτε συνέχεια αλλά για ένα χρονικό διάστημα που συνήθως πρέπει να είναι μικρό αλλά πάνω από 5 λεπτά. Για αυτό και το script θα πρέπει να γίνει όπως παρακάτω. Τέλος θα πρέπει να αλλάξουμε το όνομα και την τοποθεσία του αρχείου όπως τονίζει και το σχόλιο που υπάρχει μέσα στο script.
/****************************************************/
/* Created by: SQL Server 2014 Profiler */
/* Date: 10/01/2016 15:09:52 */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
declare @DateTime datetime
set @DateTime = DATEADD(mi,5,getdate()); /* Run for five minutes */
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'D:\traces\BlockedSessionsReports.trc', @maxfilesize, @DateTime
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 3, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
Extended Events
Επειδή ο Profiler είναι προς αντικατάσταση σε μελλοντικές εκδόσεις και πάντα ζητούμενο είναι να υπάρχει η μικρότερη δυνατή κατανάλωση πόρων έχουμε από τον SQL Server 2008 R2 στην διάθεση μας τα extented events. Γενικότερα μιλώντας αυτά είναι το μέλλον και προσωπικά προτείνω την χρήση τους ανεπιφύλακτα καθώς παρέχουν περισσότερη πληροφόριση. Για να φτιάξω ένα τέτοιο θα πρέπει να το κάνω είτε μέσα από τον SSMS είτε με το παρακάτω script
CREATE EVENT SESSION [blocked_process_report] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
ACTION(sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.client_pid,
sqlserver.database_name,
sqlserver.plan_handle,
sqlserver.session_id,
sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'D:\traces\BlockedProcess.xel',max_file_size=(50))
WITH (EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS)
GOActivate Blocked Process Report
Η ενεργοποίηση του Blocked Process Report μπορεί να γίνει είτε μέσα από τον SSMS είτε με το script που ακολουθεί. Όταν η τιμή στο server option αυτό είναι μηδέν (0) δεν είναι ενεργοποιημένο. Θα πρέπει να βάλουμε μια τιμή που να είναι μεγαλύτερη από 5 seconds καθώς μια τιμή μικρότερη δημιουργεί θέματα performance.
Η αλλαγή μέσα από τον SSMS μπορεί να γίνει αφού κάνουμε δεξί κλικ properties στο όνομα του SQL Server και στο Advanced page στα Miscellaneous υπάρχει το option "Blocked Process Threshold" στο οποίο ορίζουμε την τιμή του.
Φυσικά υπάρχει και το script που προανέφερα και στο οποίο έχω ορίσει την τιμή στα 10 seconds.
exec sp_configure 'show advanced options', 1;
go
reconfigure
go
exec sp_configure 'blocked process threshold (s)', 10;
go
reconfigure
go
Για όσους θέλουν να διαβάσουν περισσότερα για αυτό το option μπορεί να το κάνει μέσα από τα BOL στο link
αυτό.
Όμως οφείλω να αντιγράψω κάτι από αυτά και εδώ με σκοπό την επισήμανση τους
"Blocked process threshold uses the deadlock monitor background thread to walk through the list of tasks waiting for a time greater than or multiples of the configured threshold. The event is generated once per reporting interval for each of the blocked tasks.
The blocked process report is done on a best effort basis. There is no guarantee of any real-time or even close to real-time reporting."Αν και μπορεί κάποιος να θεώρήσει με αυτά που διαβάζει παραπάνω ότι δεν είναι άξιο σημασίας θα του έλεγα να συνεχίσει να διαβάζει το άρθρο αυτό και να κάνει την υλοποιηση και μετά να το αξιολογήσει σαν option.
Gathering Report Data
Το επόμενο βήμα είναι να συγκεντρωθούν τα δεδομένα. Στα χέρια μας έχουμε δύο λύσεις τα extended events και τα traces. Προτειμάμε τα extented events γιατί έχουν μικρότερο resouce footprint σε σχέση με τα traces αλλά και μικρότερο διαχειριστικό effort.
Διαβάζουμε τα δεδομένα είτε μέσα από τον SSMS είτε μέσω της sys.fn_xe_file_target_read_file εφόσον έχουμε διαλέξει τα extented evnts.