Jeśli korzystasz z raportu o zablokowanych procesach lub okresowo zbierasz wykresy zakleszczeń dostarczane przez SQL Server, napotkasz takie rzeczy:
waitresource="STRONA: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Czasami w tym gigantycznym pliku XML, który studiujesz, będzie więcej informacji (wykresy zakleszczeń zawierają listę zasobów, która pomaga znaleźć nazwy obiektów i indeksów), ale nie zawsze.
Ten tekst pomoże Ci je rozszyfrować.
Wszystkie informacje, które są tutaj, są w Internecie w różnych miejscach, są po prostu bardzo rozproszone! Chcę to wszystko połączyć, od DBCC PAGE do hobt_id do nieudokumentowanych funkcji %%physloc%% i %%lockres%%.
Najpierw porozmawiajmy o oczekiwaniach na blokady PAGE, a następnie przejdźmy do blokad KEY.
1) waitresource="STRONA: 6:3:70133" = Database_Id: FileId: PageNumber
Jeśli twoje żądanie czeka na blokadę PAGE, SQL Server poda ci adres tej strony.
Rozbijając „PAGE: 6:3:70133” otrzymujemy:
- identyfikator_bazy danych = 6
- id_pliku_danych = 3
- numer_strony = 70133
1.1) Odszyfruj identyfikator_bazy_danych
Znajdź nazwę bazy danych za pomocą zapytania:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOTo jest publiczne na moim serwerze SQL.
1.2) Szukam nazwy pliku danych - jeśli jesteś zainteresowany
W następnym kroku użyjemy data_file_id, aby znaleźć nazwę tabeli. Możesz po prostu przejść do następnego kroku, ale jeśli interesuje Cię nazwa pliku, możesz go znaleźć, uruchamiają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;
GOW bazie danych WideWorldImporters jest to plik o nazwie WWI_UserData i mam go przywrócony do C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, przyłapałeś mnie na umieszczaniu plików na dysku systemowym! Nie! To krępujące).
1.3) Pobierz nazwę obiektu z DBCC PAGE
Teraz wiemy, że strona #70133 w pliku danych 3 należy do bazy danych WorldWideImporters. Możemy przejrzeć zawartość tej strony za pomocą nieudokumentowanej strony DBCC PAGE i flagi śledzenia 3604.
Uwaga: Wolę używać DBCC PAGE na przywróconej kopii gdzieś na innym serwerze, ponieważ jest to nieudokumentowana rzecz. W niektórych przypadkach ona (około. tłumacz - link niestety prowadzi donikąd, ale sądząc po adresie URL, mówimy o filtrowanych indeksach).
/* 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żesz znaleźć identyfikator_obiektu i identyfikator_indeksu.
Prawie skończone! Teraz możesz znaleźć nazwy tabel i indeksów 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;
GOI tutaj widzimy, że oczekiwanie na blokadę dotyczyło indeksu PK_Sales_OrderLines tabeli Sales.OrderLines.
Uwaga: w SQL Server 2014 i nowszych nazwach obiektów można również szukać za pomocą nieudokumentowanego DMO sys.dm_db_database_page_allocations. Ale musisz zapytać każdą stronę w bazie danych, co nie wygląda zbyt fajnie w przypadku dużych baz danych, więc użyłem DBCC PAGE.
1.4) Czy można zobaczyć dane na stronie, która została zablokowana?
Nuu, tak. Ale... czy na pewno tego potrzebujesz?
Jest powolny nawet na małych stołach. Ale to całkiem fajne, więc skoro doczytałeś tak daleko... porozmawiajmy o %%physloc%%!
%%physloc%% to nieudokumentowana magia, która zwraca fizyczny identyfikator dla każdego wpisu. możesz użyć .
Teraz, gdy wiemy, że chcieliśmy zablokować stronę w Sales.OrderLines, możemy przejrzeć wszystkie dane w tej tabeli, która jest przechowywana w pliku danych nr 3 na stronie nr 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%'
GOJak powiedziałem, jest powolny nawet na małych stołach. Dodałem NOLOCK do żądania, ponieważ nadal nie mamy gwarancji, że dane, które chcemy przejrzeć, są dokładnie takie same, jak w momencie wykrycia blokady - więc możemy bezpiecznie wykonywać brudne odczyty.
Ale, hurra, zapytanie zwraca mi dokładnie 25 wierszy, o które walczyło nasze zapytanie.
Dość o blokadach PAGE. A co jeśli czekamy na KLUCZ?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magiczny skrót, który można odszyfrować za pomocą %%lockres%%, jeśli naprawdę chcesz)
Jeśli twoje zapytanie próbuje zablokować wpis indeksu i zostaje zablokowane samo, otrzymasz zupełnie inny typ adresu.
Dzieląc „6:72057594041991168 (ce52f92a058c)” na części, otrzymujemy:
- identyfikator_bazy danych = 6
- hobt_id = 72057594041991168
- magiczny skrót = (ce52f92a058c)
2.1) Odszyfruj identyfikator_bazy_danych
Działa dokładnie tak samo jak w powyższym przykładzie! Nazwę bazy danych znajdujemy za pomocą zapytania:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOW moim przypadku jest tak samo .
2.2) Odszyfruj hobt_id
W kontekście znalezionej bazy danych musisz wysłać zapytanie do sys.partitions z kilkoma sprzężeniami, 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;
GOMówi mi, że żądanie czekało na blokadę Application.Countries przy użyciu indeksu PK_Application_Countries.
2.3) Teraz trochę %%lockres%% magii - jeśli chcesz dowiedzieć się, który wpis został zablokowany
Jeśli naprawdę chcę wiedzieć, w którym wierszu była potrzebna blokada, mogę się tego dowiedzieć, wysyłając zapytanie do samej tabeli. Możemy użyć nieudokumentowanej funkcji %%lockres%%, aby znaleźć wpis pasujący do magicznego skrótu.
Zauważ, że to zapytanie przeskanuje całą tabelę, a na dużych tabelach może to wcale nie być zabawne:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Dodałem NOLOCK (), ponieważ zamki mogą stanowić problem. Chcemy po prostu przyjrzeć się temu, co jest teraz, a nie temu, co było, gdy transakcja się rozpoczęła – nie sądzę, aby spójność danych była dla nas ważna.
Voila, 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 rzeczach, które mogą Ci się spodobać:
- Post Paula Randala dot (ponieważ mamy nasze dane w pierwszym przykładzie)
- Pytanie na StackOverflow o (jak znaleźliśmy dane w drugim przykładzie). Jedna z odpowiedzi prowadzi do wpisu .
Źródło: www.habr.com