Ако използвате доклада за блокиран процес или периодично събирате графиките на блокиране, предоставени от 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Публично е на моя 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.
Почти готово! Сега можете да намерите имената на таблицата и индекса със заявка:
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%% е недокументирана част от магия, която връща физически идентификатор за всеки запис. можеш да използваш .
Сега, след като знаем, че искахме да блокираме страницата в 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. Ами ако чакаме заключване на КЛЮЧ?
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В моя случай е същото .
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 (), защото ключалките могат да бъдат проблем. Ние просто искаме да погледнем какво има сега, а не какво е имало, когато транзакцията е започнала - не мисля, че последователността на данните е важна за нас.
Ето, рекордът, за който се борихме!
Благодарности и допълнително четене
Не помня кой пръв описа много от тези неща, но ето две публикации за най-малко документираните неща, които може да ви харесат:
- Публикацията на Пол Рандал за (както имаме нашите данни в първия пример)
- Въпрос в StackOverflow относно (както намерихме данните във втория пример). Един от отговорите води до публикацията .
Източник: www.habr.com