Wskazówka dnia: Identyfikacja blokujących transakcji

Dziś zajmiemy się tematem identyfikacji blokujących się sesji. Systemy bazodanowe w wielu przypadkach są mocno obciążone, a co za tym idzie muszą one zapewniać współbieżność transakcji. W momencie gdy nasze zapytania zwalniają i jest to związane z blokadami warto wiedzieć jak ten fakt zidentyfikować. W ramach dzisiejszej wskazówki pokażemy jakie takie informacje znaleźć.

Aby zobrazować we właściwy sposób schemat działania opisywanego mechanizmy zainicjujmy powstanie blokady na bazie WorldWideImportersDW. W pierwszym oknie zapytania w management studio otwórzmy transakcję i odpytajmy tabelę Dimension.City podając przy tym wskazówkę TABLOCKX, która zapewni ekskluzywny dostęp do tabeli na czas trwania transakcji.

USE WideWorldImportersDW
GO

BEGIN TRAN
SELECT 
	* 
FROM Dimension.City
	WITH (TABLOCKX)

W kolejnym oknie spróbujmy odpytać tą samą tabelę:

SELECT 
	* 
FROM Dimension.City

Jak łatwo zidentyfikować  po powyższym zrzucie ekranowym, sesja o numerze 55 jest blokowana przez sesję o numerze 54. W rzeczywistości niestety nie jesteśmy w tak prosty “wizualny” sposób zidentyfikować blokady. Na szczęście SQL Server udostępnia nam cały szereg metod identyfikacji tego typu problemów. Pierwszym z nich jest procedura sp_who2:

EXEC sp_who2

Rezultat w jasny i prosty sposób pokazuje nam, że dla sesji o numerze 55 kolumna BlkBy wskazuje wprost numer sesji blokującej. Oprócz tego możemy również odczytać m.in Login użytkownika wywołującego konkretne zapytanie. Niestety metoda ta nie pozwala wprost podejrzeć samego zapytania – możemy do tego użyć komendy DBCC INPUTBUFFER przyjmującej w parametrze numer sesji.

DBCC INPUTBUFFER(54)

Teraz mamy komplet podstawowych informacji na temat interesującego nas zdarzenia. Wadą tego podejścia jest to, że DBCC INPUTBUFFER w kolumnue EventInfo zwraca ostatnie zapytanie w ramach sesji, więc nie zawsze będzie tam wyświetlane zapytanie, które w rzeczywistości nas zablokowało. W tym miejscu może pojawić się pytanie czy istnieją inne, bardziej skuteczne sposoby na uzyskanie pożądanych informacji? Oczywiście! Szereg widoków systemowych udostępnia te i inne informacje -jednym z nich jest sys.dm_os_waiting_tasks, który w większości przypadków może służyć do identyfikacji statystyk oczekiwań – jednakże również jest on niejako pomocny w naszym przypadku.

SELECT 
	session_id, 
	wait_duration_ms, 
	wait_type, 
	blocking_session_id,
	resource_description
FROM sys.dm_os_waiting_tasks 
WHERE session_id=55

Oprócz identyfikatora blokującej sesji mamy tu również pole resource_Description, które słownie opsiuje jaki zasób jest zablokowany:

objectlock lockPartition=0 objid=1013578649 subresource=FULL dbid=10 id=lockc3f9db5680 mode=X associatedObjectId=1013578649

Z tego opisu możemy ręcznie “wyciągnąć” pole objid i odpytać widok systemowy sys.objects aby dowiedzieć się,  na zwolnienie blokady którego zasobu oczekujemy:

select
	SCHEMA_NAME(o.schema_id) AS SchemaName,
	OBJECT_NAME(o.object_id) AS TableName
FROM sys.objects AS O
WHERE object_id=1013578649

Jak widać powyższy sposób może się okazać użyteczny – jednakże z całą pewnością nie jest on najlepszy. Najskuteczniejszą w moim mniemaniu opcją w opisywanej sytuacji jest odpytanie  sys.dm_exec_request

select 
	session_id,
	start_time,
	status,
	blocking_session_id,
	wait_type,
	wait_time
 from sys.dm_exec_requests
where session_id=55

Powyższe zapytanie wybiera podstawowe informacje dla sesji 55 takie jak czas rozpoczęcia transakcji jej status, numer sesji blokującej, typ czasu oczekiwania, czas oczekiwania oraz hash tekstu zapytania.

Aby odczytać w zrozumiały dla nas sposób hash zapytania z powyższego zapytania możemy użyć wbudowanej funkcji sys.dm_exec_sql_text tak jak zostało to przedstawione poniżej:

SELECT
	r.session_id,
	r.start_time,
	r.status,
	r.blocking_session_id,
	r.wait_type,
	r.wait_time,
	t.text AS SQL_TEXT
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE session_id=55

Powyższa informacja jest dużo bardziej konkretna i w wielu przypadkach wystarczająca. Oprócz odpytania z wykorzystaniem TSQL możemy użyć wbudowanych w Management Studio narzędzi takich jak Activity Monitor:

Czy też wbudowany raport All Blocking Transactions:

Jednakże powyższe metody są niczym innym jak odpytaniem wspomnianych wcześniej obiektów bazodanowych. Oczywiście istnieje również szereg innych metod identyfikacji sesji blokujących przy użyciuSQL Server Profiler czy Extended Events jednakże są to metody na zupełnie inny post. Mam nadzieję, że niniejsza wskazówka okaże się dla was użyteczna – szczególnie polecam zapoznać się bliżej z sys.dm_exec_requests, który niejednokrotnie będziecie mogli z powodzeniem użyć do tego typu problemów.