🥇Розшифровуємо Key та Page WaitResource у дідлоках та блокуваннях

Якщо ви користуєтеся звітом про блокування (blocked process report) або збираєте графи дідлоків, що надаються SQL Server'ом, періодично ви будете стикатися з такими штуками:

waitresource=“PAGE: 6:3:70133“

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

Іноді, в тому гігантському XML, який ви вивчаєте, буде більше інформації (графи дідлок містять список ресурсів, який допомагає дізнатися імена об'єкта та індексу), але не завжди.

Цей текст допоможе вам розшифрувати їх.

Вся інформація, яка тут є, є в інтернеті в різних місцях, вона просто розподілена! Я хочу зібрати все разом — від DBCC PAGE до hobt_id і до недокументованих %% physloc%% та %%lockres%% функцій.

Спочатку поговоримо про очікування на PAGE-блокування, а потім перейдемо до KEY-блокування.

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

Якщо ваш запит чекає на PAGE-блокуванні, SQL Server надасть вам адресу цієї сторінки.

Розбиваючи «PAGE: 6:3:70133» ми отримуємо:

database_id = 6
data_file_id = 3
page_numer = 70133

1.1) Розшифровуємо database_id

Знайдемо ім'я бази даних за допомогою запиту:

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

Це загальнодоступна БД WideWorldImporters на моєму SQL Server.

1.2) Шукаємо ім'я файлу даних - якщо вам цікаво

Ми збираємося використовувати data_file_id на наступному етапі, щоб знайти ім'я таблиці. Ви можете перейти до наступного кроку, але якщо ви зацікавлені в імені файлу, ви можете знайти його, виконавши запит у контексті знайденої БД, підставивши data_file_id в цей запит:

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

У БД WideWorldImporters це файл, названий WWI_UserData і він відновлений у мене в C:MSSQLDATAWideWorldImporters_UserData.ndf. (Упс, ви спіймали мене за тим, як я кладу файли на диск із системою! Ні! Незручно вийшло).

1.3) Отримуємо ім'я об'єкта з DBCC PAGE

Тепер ми знаємо, що сторінка #70133 у файлі даних 3 належить БД WorldWideImporters. Ми можемо подивитися на вміст цієї сторінки за допомогою недокументованого DBCC PAGE та trace-прапора 3604.
Примітка: я волію використовувати копію DBCC PAGE на відновленій з бекапу десь на іншому сервері, тому що це недокументована штука. У деяких випадках вона може призводити до створення дампи (прим. перекладача — посилання, на жаль, веде в нікуди, але судячи з url, мова про filtered-індекси).

/* 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

Промотавши до результатів, можна знайти object_id та index_id.

Майже готово! Тепер можна знайти імена таблиці та індексу за допомогою запиту:

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

І ось бачимо, що очікування на блокуванні було на індексі PK_Sales_OrderLines таблиці Sales.OrderLines.

Примітка: у SQL Server 2014 та вище ім'я об'єкта також можна знайти за допомогою недокументованого DMO sys.dm_db_database_page_allocations. Але вам доведеться вимагати кожну сторінку в БД, що виглядає не дуже круто для великих баз даних, тому я використовувала DBCC PAGE.

1.4) А чи можна побачити дані на тій сторінці, яка була заблокована?

Ну, так. Але... ви впевнені, що вам це точно потрібно?
Це повільно навіть на невеликих таблицях. Але це начебто прикольно, тому, якщо вже ви дочитали до цього моменту… давайте поговоримо про %%physloc%%!

%%physloc%% — це недокументований шматочок магії, який повертає фізичний ідентифікатор для кожного запису. Ви можете використовувати %%physloc%% разом з sys.fn_PhysLocFormatter в SQL Server 2008 і вище.

Тепер, коли ми знаємо, що ми хотіли накласти блокування на сторінку в Sales.OrderLines, то можемо подивитися всі дані в цій таблиці, які зберігаються у файлі даних #3 на сторінці #70133, за допомогою такого запиту:

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

Як я й казала, це повільно навіть на крихітних таблицях. Я додала до запиту NOLOCK тому що у нас все одно немає жодних гарантій, що дані, на які ми хочемо глянути, точно ті ж, що були в момент, коли було виявлено блокування, тож спокійно можемо робити брудні читання.
Але, ура, запит повертає мені ті самі 25 рядків, за які наш запит і боровся

Досить про PAGE-блокування. Що якщо ми чекаємо на KEY-блокування?

2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (чарівний хеш, який можна розшифрувати за допомогою %%lockres%%, якщо ви точно цього хочете)

Якщо ваш запит намагається накласти блокування на запис в індексі і виявляється заблокованим сам, ви отримуєте зовсім інший тип адреси.
Розбивши “6:72057594041991168 (ce52f92a058c)” на частини, ми отримуємо:

database_id = 6
hobt_id = 72057594041991168
чарівний хеш = (ce52f92a058c)

2.1) Розшифровуємо database_id

Це працює так само, як і з прикладом вище! Знаходимо ім'я БД за допомогою запиту:

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

У моєму випадку - це все та ж БД WideWorldImporters.

2.2) Розшифровуємо hobt_id

У контексті знайденої БД, потрібно виконати запит до sys.partitions з парою джойнів, які допоможуть визначити імена таблиці та індексу.

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

Він каже мені, що запит чекав на блокування Application.Countries, використовуючи індекс PK_Application_Countries.

2.3) Тепер трохи магії %%lockres%% — якщо ви хочете з'ясувати, який запис було заблоковано

Якщо я дійсно хочу дізнатися, на якому рядку потрібне було блокування, я можу з'ясувати це за допомогою запиту до самої таблиці. Ми можемо використовувати недокументовану функцію %%lockres%%, щоб знайти запис, що збігається з чарівним хешом.
Врахуйте, що цей запит скануватиме всю таблицю, і на великих таблицях це може бути зовсім не весело:

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

Я додала NOLOCK (за порадою Klaus Aschenbrenner у твіттері) тому що блокування можуть стати проблемою. Ми ж хочемо просто глянути, що там зараз, а не що там було, коли почалася транзакція — не думаю, що узгодженість даних нам важлива.
Вуаля, запис за який ми боролися!

Подяки та подальше читання

Не пам'ятаю хто першим описав багато з цих штук, але ось дві посади про найменш документовані штукенції, які можуть вам сподобатися:

Пост Paul Randal про %%physloc%% та sys.fn_PhysLocFormatter (як ми наші дані в першому прикладі)
Питання на StackOverflow про використання %%lockres%% (Як ми знайшли дані у другому прикладі). Одна з відповідей веде на посаду Grant Fritchey про %%lockres%%, написаний ще в 2010.

Джерело: habr.com

Розшифровуємо Key та Page WaitResource у дідлоках та блокуваннях