Jak śledzić blokowanie, które zdarza się krócej niż sekundę - SQL Server

Próbuję rozwiązać problem blokowania, który występuje przez mniej niż sekundę. Aplikacja OLTP jest bardzo wrażliwa i musi mieć czas odpowiedzi mniejszy niż 200 ms dla niektórych transakcji zgodnie z ustaloną umową SLA. Wystąpiły pewne problemy z eskalacją blokady w nowej wersji kodu, które udało nam się rozwiązać, zmniejszając rozmiar partii w aktualizacjach. Nawet przy małej wielkości partii podejrzewamy, że nowy sp blokuje te same wiersze, które aktualizują transakcje OLTP.

Muszę znaleźć blokowaną sesję i zasób, na który czeka. Zgodnie z moim rozumieniem „próg zablokowanego procesu” można ustawić na co najmniej 1 sekundę, więc nie spowoduje to zablokowania blokady.

Eksperymentuję z wydarzeniami wait_info i wait_completed x.

Czy jest jakiś inny sposób, aby to wyśledzić. Dzięki

Ponieważ jesteś szczególnie zainteresowany blokowaniem, a nie ogólnym oczekiwaniem, locks_lock_waitswydarzenie rozszerzone wydaje się bardziej odpowiednie.

Przy włączonym filtrze increment >= 200

CREATE EVENT SESSION [locks_lock_waits] ON SERVER 
ADD EVENT sqlserver.locks_lock_waits(
        ACTION(sqlserver.sql_text)
            WHERE  ( [sqlserver].[is_system] = 0
                     AND [increment] >= 200
                     AND [counter] <= 1000 ) 
    )
ADD TARGET package0.ring_buffer;

GO

ALTER EVENT SESSION [locks_lock_waits]  
ON SERVER  STATE = start;  

Powyższe gromadzi instrukcje czekające na blokady przez określony czas, ale nie podaje określonego zasobu blokady.

Nigdy nie korzystałem z tego wydarzenia i nie mam wglądu w to, ile narzutu spowodowałoby to na twoim serwerze produkcyjnym.

Znalazłem ten film na ten temat. To zdecydowanie zaleca filtrowanie w countercelu zmniejszenia liczby zebranych zdarzeń i zrobiłem to powyżej.

Wspomina także o starym, nieudokumentowanym poleceniu

dbcc lock(StallReportThreshold, 200) -- 200 is threshold in ms

Który (jeśli flaga śledzenia 3605 jest włączona) zrzuca ograniczone informacje, takie jak poniżej, do dziennika błędów SQL Server.

Proces 53 czekał 6844 ms na S lock na RID: wynik 2: 1: 120: 2: OKWAIT

Wspominam o tym mimochodem, ponieważ i tak dłuższe wydarzenia byłyby zdecydowanie lepsze, ponieważ są one udokumentowane i mają znacznie większą moc.

Jeśli chcesz zablokować, dostępnych jest kilka rozszerzonych wydarzeń:

lock_acquired
lock_released
lock_escalation

Pierwsze dwa zdarzenia mają durationkolumnę (w mikrosekundach), którą można filtrować według progów. Mają też resource_descriptionakcję, która da ci szczegółowe informacje na temat zaangażowanych zasobów.

W lock_escalationzdarzeniu jest także statementakcja, którą można dodać, aby zebrać instrukcję T-SQL, która uruchomiła eskalację blokady. Ma również escalation_cause. Oto przykładowa sesja:

CREATE EVENT SESSION [locking] ON SERVER 
ADD EVENT sqlserver.lock_acquired( SET collect_resource_description = (1) ),
ADD EVENT sqlserver.lock_escalation( SET collect_statement = (1) ),
ADD EVENT sqlserver.lock_released( SET collect_resource_description = (1) )
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Podejrzewam, że istnieje prawdopodobnie powód, dla którego nie można ustawić progu raportu zablokowanego procesu na mniej niż sekundę: blokowanie jest w RDBMS całkowicie normalne - silnik bazy danych musi blokować zasoby, aby je chronić. Chociaż nie ma oficjalnej definicji, kiedy blokowanie staje się blokowaniem, blokowanie tykania w czasie poniżej sekundy wydaje mi się normalne.