Калі вы карыстаецеся справаздачай аб блакіроўках (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Гэта агульнадаступная на маім 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%% - гэта недакументаваны кавалачак магіі, які вяртае фізічны ідэнтыфікатар для кожнага запісу. Вы можаце выкарыстоўваць .
Цяпер, калі мы ведаем, што мы хацелі накласці блакаванне на старонку ў 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У маім выпадку - гэта ўсё тая ж .
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 () таму што блакіроўкі могуць стаць праблемай. Мы ж хочам проста зірнуць што там зараз, а не што там было, калі пачалася транзакцыя - не думаю, што ўзгодненасць дадзеных нам важная.
Вуаля, запіс за якую мы змагаліся!
Падзякі і далейшае чытанне
Не памятаю хто першым апісаў шматлікія з гэтых штук, але вось два паста аб самых найменш дакументаваных штукенциях, якія могуць вам спадабацца:
- Пост Paul Randal аб (як мы нашы дадзеныя ў першым прыкладзе)
- Пытанне на StackOverflow аб (як мы знайшлі дадзеныя ў другім прыкладзе). Адзін з адказаў вядзе на пасаду .
Крыніца: habr.com