Расшифровываем 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:\MSSQL\DATA\WideWorldImporters_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.