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.

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

blockingsessions

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:

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.

inputbuffer

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.

sys_dm_os_waiting_tasks

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

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:

sys-objects

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

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.

sys_dm_exec_requests

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:

sys-dm_exec_request_sql_handle

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:

activity_monitor

Czy też wbudowany raport All Blocking Transactions:

standardreports

allblockingtransactions

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.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

2 Comments

  1. Gość

    Tak trochę nie na temat, ale nie mogłem się powstrzymać 🙂 Na Wasz blog trafiłem niedawno, a to dzięki Piotrowi Ziuziańskiemu i jego stronie msbifun.pl. Obie strony, i Wasza i Piotra są bardzo interesujące i pożyteczne. Obie wypełniają pewną niszę w obszarze średnio zaawansowanego SQL-a, pozwalającą bezproblemowo zagłębić się w zagadnienia bardziej skomplikowane niż klasyczne pobieranie danych z tabel. Może się mylę, ale z moich obserwacji wynika, że na rynku książek z zakresu baz danych brakuje pozycji obejmujących zagadnienia średniozaawansowane. Z jednej strony jest wręcz nadmiar podręczników opisujących podstawy SQL-a, czyli SELECT, FROM, WHERE, GROUP BY, itp., a z drugiej strony jest potem od razu przeskok do bardzo zaawansowanych książek dla programistów i administratorów baz danych. Wyraźnie brakuje czegoś pośredniego, co umożliwiłoby średnio zaawansowanego SQL-owcowi, nawet zwykłemu hobbyście (takiemu jak ja) rozwijanie swoich zainteresowań wykraczających poza typowe wyrażenia “SELECT kolumna FROM tabela”, i jednocześnie nie zniechęcającego się zbyt skomplikowanymi rzeczami przeznaczonymi dla bazodanowych programistów “wagi ciężkiej”. Uważam że Wasza strona bardzo dobrze spełnia to zadanie, i cieszę się że na nią trafiłem. Jak pewnie domyślacie się, jestem i będę jej stałym czytelnikiem. Pozdrawiam serdecznie.

    Reply
  2. Adrian ChodkowskiAdrian Chodkowski (Post author)

    Hej, dziękuję bardzo za miłe słowa. Zgadzam się, że polska blogosfera związana z szeroko pojętymi technologiami bazodanowymi i około bazodanowymi póki co raczkuje. Wydaje mi się, że dzielenie się własną wiedzą właśnie w taki sposób z czasem stanie się coraz popularniejsze i będzie można o czymś poczytać w rodzimym języku. Jeśli chodzi o poziom wiedzy to zawsze było tak, że opisów tematów podstawowych jest całe gro w sieci, podobnie jak tematów zaawansowanych, brakuje natomiast tego pośredniego szczebla. Chcemy mieć w tym swoją cząstkę i opisywać tematy zarówno te proste, średnio zaawansowane jak i te najbardziej skomplikowane. W niedługim czasie blog czekają nowe wpisy i parę nowości także zapraszam do częstych wizyt! Dziękuję za ten komentarz i pozdrawiam.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *