ProHoster > Блог > администрация > Дешифриране на ключ и Page WaitResource при блокировки и блокировки

Дешифриране на ключ и Page WaitResource при блокировки и блокировки

Ако използвате доклада за блокиран процес или периодично събирате графиките на блокиране, предоставени от SQL Server, ще срещнете неща като това:

waitresource=“СТРАНИЦА: 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" получаваме:

  • база_данни_id = 6
  • ИД_на_файл_с_данни = 3
  • номер_на_страница = 70133

1.1) Дешифрирайте база_данни_ид

Намерете името на базата данни, като използвате заявката:

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

Публично е DB WideWorldImporters на моя SQL сървър.

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 и флаг за проследяване 3604.
Забележка: Предпочитам да използвам DBCC PAGE на възстановено копие някъде на друг сървър, защото това са недокументирани неща. В някои случаи тя може да доведе до изхвърляне (прибл. преводач - връзката, за съжаление, не води до никъде, но съдейки по url-а, говорим за филтрирани индекси).

/* 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.
Дешифриране на ключ и Page WaitResource при блокировки и блокировки
Почти готово! Сега можете да намерите имената на таблицата и индекса със заявка:

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 WaitResource при блокировки и блокировки
Стига за заключванията на PAGE. Ами ако чакаме заключване на КЛЮЧ?

2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (магически хеш, който може да бъде дешифриран с %%lockres%%, ако наистина искате)

Ако вашата заявка се опита да заключи запис в индекса и се заключи сама, вие получавате напълно различен тип адрес.
Разбивайки „6:72057594041991168 (ce52f92a058c)“ на части, получаваме:

  • база_данни_id = 6
  • hobt_id = 72057594041991168
  • магически хеш = (ce52f92a058c)

2.1) Дешифрирайте база_данни_ид

Работи точно както в горния пример! Намираме името на базата данни с помощта на заявката:

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

В моя случай е същото DB 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 (по съвет на Клаус Ашенбренер в Twitter), защото ключалките могат да бъдат проблем. Ние просто искаме да погледнем какво има сега, а не какво е имало, когато транзакцията е започнала - не мисля, че последователността на данните е важна за нас.
Ето, рекордът, за който се борихме!
Дешифриране на ключ и Page WaitResource при блокировки и блокировки

Благодарности и допълнително четене

Не помня кой пръв описа много от тези неща, но ето две публикации за най-малко документираните неща, които може да ви харесат:

Източник: www.habr.com

Купете надежден хостинг за сайтове с DDoS защита, VPS VDS сървъри 🔥 Купете надежден уеб хостинг със защита от DDoS атаки, VPS VDS сървъри | ProHoster