Регистрация

[Microsoft SQL Server, Администрирование баз данных] Расшифровываем Key и Page WaitResource в дедлоках и блокировках (перевод)

Ответить на тему
Автор Сообщение
news_bot ®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

Создавать темы news_bot ® написал(а)
20-Окт-2020 02:31
Цитировать

Если вы пользуетесь отчётом о блокировках (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 в твиттере) потому что блокировки могут стать проблемой. Мы же хотим просто глянуть что там сейчас, а не что там было, когда началась транзакция — не думаю, что согласованность данных нам важна.
Вуаля, запись за которую мы боролись!

Благодарности и дальнейшее чтение
Не помню кто первым описал многие из этих штук, но вот два поста о самых наименее документированных штукенциях, которые могут вам понравиться:
===========
 Источник:
habr.com
===========
 ===========
 Автор оригинала: Kendra Little
===========Похожие новости: Теги для поиска: #_microsoft_sql_server, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_sql_server, #_blocking, #_locking, #_microsoft_sql_server, #_administrirovanie_baz_dannyh (
Администрирование баз данных
)
Профиль  ЛС 
Показать сообщения:     
Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 22-Ноя 19:38
Часовой пояс: UTC + 5