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

Το γιατί έχεις locks είναι κυρίως το πώς έχει γραφτεί η εφαρμογή και έχω εξηγήσει αρκετά στο SQL Night με θέμα το Concurrency in SQL Server.

Σαν DBA με πάνω από 250 instances και 3000 databases καθημερινά αντιμετωπίζω τέτοια θεματάκια τα οποία πρέπει να εντοπίσω ώστε να τα δώσω για τις απαραίτητες διορθώσεις στις εφαρμογές ή να προβώ στις ενέργειες που χρειάζεται να γίνουν ώστε να διασφαλίσω την ομαλή λειτουργία των συστημάτων.

Για το λόγο αυτό έχω διάφορα εργαλεία με τα οποία παλεύω τα θηρία.

Για να μπορέσω να βρω την αιτία που έχω locking θα πρέπει να είμαι σε θέση να εντοπίσω το session που αρχικά είναι υπεύθυνο για αυτό καθώς μπορεί να έχω μια αλυσίδα από session που το ένα κάνει lock το άλλο.

Το query αυτό είναι τα παρακάτω

select    s.session_id
    ,    r.status
    ,    s.login_name
    ,    s.host_name
    ,    s.login_time
    ,    r.start_time
    ,    s.last_request_start_time
    ,    s.last_request_end_time
    ,    ( select t.text AS [text()] for xml path(''), type ) as sql_text
    ,    case
            when r.Session_ID is null then 'Session does not have an open request, maybe due to an uncommitted transaction'
            when r.Wait_Type is not null then 'Session is currently has a '+ r.Wait_Type +' wait.'
            when r.Status = 'Runnable' then 'Session is currently waiting for CPU time.'
            when r.Status = 'Suspended' then 'Session has been suspended by the scheduler.'
            else 'Session is currently in a '+ r.Status + ' status.'
        end as blocking_reason
    ,    ( select 'kill ' + cast (s.session_id as nvarchar(10)) as [text()] for xml path(''), type ) as kill_cmd
from sys.dm_exec_sessions as s
left outer join  sys.dm_exec_requests as r on s.session_id=r.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) as t
where s.is_user_process=1
and s.session_id in ( select distinct (Blocking_Session_ID)
                      from sys.dm_exec_requests 
                      where Blocking_Session_ID <> 0
                      and Blocking_Session_ID Not In ( select session_id from sys.dm_exec_requests where Blocking_Session_ID <> 0 ) )

Για να εξηγήσω όμως λίγο τι κάνει αυτό ;

Αρχικά το συγκεκριμένο βασίζεται στην χρήση τριών γνωστών DMV των

sys.dm_exec_sessions που επιστρέφει τα sessions που έχω πάνω στο instance και με ενδιαφέρουν μόνο αυτά που είναι user sessions για αυτό και υπάρχει τα φίλτρο στο where s.is_user_process=1.

sys.dm_exec_requests το οποίο επιστρέφει τις ενεργές (αυτές που εκτελούνται την δεδομένη χρονική στιγμή) και για αυτό το λόγο γίνεται και left join με το sys.dm_exec_sessions με σκοπό πάντα να έχω όλα τα user sessions είτε κάνουν κάτι είτε όχι.

Sys.dm_exec_sql_text DMV function ώστε να μπορώ να βλέπω τα statements που τα ενεργά session εκτελούν την δεδομένη χρονική στιγμή.

Από αυτά τα τρία DMVs έχω επιλέξει συγκεκριμένα fields που προσωπικά θέλω να βλέπω ώστε να έχω την εικόνα εκτέλεσης

Και φτάνω στο "δύσκολο" τμήμα που είναι στο δεύτερο τμήμα του where clause.

Επειδή με ενδιαφέρει να βρω μόνο το αρχικό session που έχει μπλοκάρει τα επόμενα για αυτό το λόγο υπάρχει ένα διπλό subquery που βρίσκει το session που δεν έχει κάποιο άλλο session που το μπλοκάρει.

Στο query σαν τεμπέλης έχω βάλει και το kill command. Αν αποφασίσεις να κάνεις kill το session αυτό θα πρέπει να τρέξεις πάλι το query ώστε να διασφαλίσεις ότι δεν υπάρχει άλλο που δημιουργεί πρόβλημα.

Καλό θα είναι και για αυτό τα έχω βάλει να φαίνονται να δεις τους χρόνους που το συγκεκριμένο έχει ώστε να ξέρεις αν είναι κάτι που τρέξει αρκετή ώρα ή απλά είναι στιγμιαίο που είναι και το αναμενόμενο.

/*antonch*/