Rozszyfrowujemy Key i Page WaitResource w deadlockach i blokadach

Jeśli korzystasz z raportu o zablokowanych procesach (blocked process report) lub zbierasz informacje o deadlockach dostarczane przez SQL Server, co jakiś czas napotkasz takie rzeczy:

waitresource="PAGE: 6:3:70133"

waitresource="KEY: 6:72057594041991168 (ce52f92a058c)"

Czasami, w tym gigantycznym XML-u, który analizujesz, będzie więcej informacji (kolumny deadlocków zawierają listę zasobów, która pomaga poznać nazwy obiektu i indeksu), ale nie zawsze.

Ten tekst pomoże ci je rozszyfrować.

Wszystkie informacje, które tu są, dostępne są w różnych miejscach w internecie, po prostu są mocno rozproszone! Chcę zebrać je wszystkie — od DBCC PAGE do hobt_id i niedokumentowanych funkcji %%physloc%% i %%lockres%%.

Najpierw porozmawiajmy o oczekiwaniach na blokadach PAGE, a następnie przejdźmy do blokad KEY.

1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber

Jeśli twoje zapytanie czeka na blokadzie PAGE, SQL Server poda ci adres tej strony.

Rozkładając „PAGE: 6:3:70133” otrzymujemy:

• database_id = 6
• data_file_id = 3
• page_number = 70133

1.1) Rozszyfrowujemy database_id

Znajdziemy nazwę bazy danych za pomocą zapytania:

SELECT 
    name 
FROM sys.databases 
WHERE database_id=6;
GO

To jest publiczna Baza danych WideWorldImporters na moim SQL Server.

1.2) Szukamy nazwy pliku danych — jeśli jesteś zainteresowany

Zamierzamy użyć data_file_id w następnym kroku, aby znaleźć nazwę tabeli. Możesz przejść do następnego kroku, ale jeśli interesuje cię nazwa pliku, możesz ją znaleźć, wykonując zapytanie w kontekście znalezionej Bazy Danych, podstawiając data_file_id do tego zapytania:

USE WideWorldImporters;
GO
SELECT 
    name, 
    physical_name
FROM sys.database_files
WHERE file_id = 3;
GO

W bazie danych WideWorldImporters plik nosi nazwę WWI_UserData i jest zapisany na C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, przyłapałeś mnie na wrzucaniu plików na dysk z systemem! Nie! Niezręcznie to wyszło).

1.3) Uzyskujemy nazwę obiektu z DBCC PAGE

Teraz wiemy, że strona #70133 w pliku danych 3 należy do bazy danych WorldWideImporters. Możemy zobaczyć zawartość tej strony za pomocą niedokumentowanego DBCC PAGE i flagi śladu 3604.
Uwaga: wolę używać DBCC PAGE na przywróconej kopii z backupu gdzieś na innym serwerze, ponieważ to niedokumentowana opcja. W niektórych przypadkach może prowadzić do utworzenia zrzutu (przyp. tłumacza — link prowadzi donikąd, ale sądząc po adresie URL, chodzi o indeksy filtrowane.).

/* This trace flag makes DBCC PAGE output go to our Messages tab
instead of the SQL Server Error Log file */
DBCC TRACEON (3604);
GO
/* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
DBCC PAGE ('WideWorldImporters',3,70133,2);
GO

Przewijając do wyników, można znaleźć object_id i index_id.

Prawie gotowe! Teraz możesz znaleźć nazwy schematu i indeksu za pomocą zapytania:

USE WideWorldImporters;
GO
SELECT 
    sc.name as schema_name, 
    so.name as object_name, 
    si.name as index_name
FROM sys.objects as so 
JOIN sys.indexes as si on 
    so.object_id=si.object_id
JOIN sys.schemas AS sc on 
    so.schema_id=sc.schema_id
WHERE 
    so.object_id = 94623380
    and si.index_id = 1;
GO

I oto widzimy, że oczekiwanie na blokadzie miało miejsce na indeksie PK_Sales_OrderLines tabeli Sales.OrderLines.

Uwaga: w SQL Server 2014 i nowszych nazwę obiektu można również znaleźć za pomocą niedokumentowanego DMO sys.dm_db_database_page_allocations. Ale będziesz musiał zapytać każdą stronę w bazie danych, co nie wygląda zbyt dobrze w przypadku dużych baz danych, dlatego użyłem DBCC PAGE.

1.4) Czy można zobaczyć dane na stronie, która była zablokowana?

Cóż, tak. Ale… czy na pewno tego potrzebujesz?
To jest wolne nawet na małych tabelach. Ale w sumie to jest dość ciekawe, więc, skoro dotarłeś do tego momentu… porozmawiajmy o %%physloc%%!

%%physloc%% to niedokumentowany kawałek magii, który zwraca fizyczny identyfikator dla każdego rekordu. Możesz użyć %%physloc%% razem z sys.fn_PhysLocFormatter w SQL Server 2008 i nowszych.

Teraz, gdy wiemy, że chcieliśmy nałożyć blokadę na stronę w Sales.OrderLines, możemy zobaczyć wszystkie dane w tej tabeli, które są przechowywane w pliku danych #3 na stronie #70133, za pomocą następującego zapytania:

Use WideWorldImporters;
GO
SELECT 
    sys.fn_PhysLocFormatter (%%physloc%%),
    *
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GO

Jak już mówiłem – to jest wolne nawet na malutkich tabelach. Dodałem do zapytania NOLOCK, ponieważ i tak nie mamy żadnych gwarancji, że dane, na które chcemy spojrzeć, są takie same, jakie były w momencie, gdy wykryto blokadę – więc spokojnie możemy robić brudne odczyty.
Ale, hurra, zapytanie zwraca dokładnie te 25 wierszy, o które nasza prośba walczyła.

Dość o blokadach PAGE. Co jeśli czekamy na blokadę KEY?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (magiczny hash, który można zdekodować za pomocą %%lockres%%, jeśli naprawdę tego chcesz)

Jeśli twoje zapytanie próbuje nałożyć blokadę na rekord w indeksie i zostaje zablokowane, otrzymujesz zupełnie inny typ adresu.
Rozdzielając “6:72057594041991168 (ce52f92a058c)” na części, otrzymujemy:

• database_id = 6
• hobt_id = 72057594041991168
• magiczny hash = (ce52f92a058c)

2.1) Dekodujemy database_id

To działa dokładnie tak samo, jak w powyższym przykładzie! Znajdujemy nazwę bazy danych za pomocą zapytania:

SELECT 
    name 
FROM sys.databases 
WHERE database_id=6;
GO

W moim przypadku to wciąż ta sama Baza danych WideWorldImporters.

2.2) Rozszyfrowujemy hobt_id

W kontekście znalezionej bazy danych należy wykonać zapytanie do sys.partitions z parą joinów, które pomogą określić nazwy tabeli i indeksu…

USE WideWorldImporters;
GO
SELECT 
    sc.name as schema_name, 
    so.name as object_name, 
    si.name as index_name
FROM sys.partitions AS p
JOIN sys.objects as so on 
    p.object_id=so.object_id
JOIN sys.indexes as si on 
    p.index_id=si.index_id and 
    p.object_id=si.object_id
JOIN sys.schemas AS sc on 
    so.schema_id=sc.schema_id
WHERE hobt_id = 72057594041991168;
GO

Mówi mi, że zapytanie czekało na blokadzie Application.Countries, używając indeksu PK_Application_Countries.

2.3) Teraz trochę magii %%lockres%% — jeśli chcesz dowiedzieć się, która rekord została zablokowana

Jeśli naprawdę chcę wiedzieć, na którym wierszu potrzebna była blokada, mogę to ustalić za pomocą zapytania do samej tabeli. Możemy użyć nieudokumentowanej funkcji %%lockres%%, aby znaleźć rekord, który pasuje do magicznego hasha.
Weź pod uwagę, że to zapytanie będzie skanować całą tabelę, a w przypadku dużych tabel może być to naprawdę uciążliwe:

SELECT
    *
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO

Dodałam NOLOCK (na radę Klausa Aschenbrennera na Twitterze) ponieważ blokady mogą być problemem. Chcemy po prostu zobaczyć, co się tam teraz dzieje, a nie co tam było, gdy rozpoczęła się transakcja — nie sądzę, że spójność danych jest dla nas ważna.
Voilà, rekord, o który walczyliśmy!

Podziękowania i dalsza lektura

Nie pamiętam, kto pierwszy opisał wiele z tych rzeczy, ale oto dwa posty o najmniej udokumentowanych aspektach, które mogą Ci się spodobać:

• Post Paula Randala o %%physloc%% i sys.fn_PhysLocFormatter (jak przekazaliśmy nasze dane w pierwszym przykładzie)
• Pytanie na StackOverflow o używaniu %%lockres%% (jak znaleźliśmy dane w drugim przykładzie). Jedna z odpowiedzi prowadzi do postu Granta Fritcheya o %%lockres%%, napisanego jeszcze w 2010 roku.

Źródło: habr.com