🥇رمزگشایی کلید و منبع انتظار صفحه در بن بست ها و قفل ها

اگر از گزارش فرآیند مسدود شده استفاده کنید یا نمودارهای بن بست ارائه شده توسط 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: Page Number

اگر درخواست شما روی قفل PAGE منتظر است، SQL Server آدرس آن صفحه را به شما می دهد.

با تجزیه "PAGE: 6:3:70133" دریافت می کنیم:

database_id = 6
data_file_id = 3
صفحه_شماره = 70133

1.1) رمزگشایی database_id

با استفاده از پرس و جو نام پایگاه داده را بیابید:

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 در datafile 3 متعلق به پایگاه داده WorldWideImporters است. می‌توانیم با استفاده از DBCC PAGE غیرمستند و پرچم 3604 به محتویات این صفحه نگاه کنیم.
توجه: من ترجیح می‌دهم از DBCC PAGE روی یک نسخه بازیابی شده در سرور دیگری استفاده کنم، زیرا این موارد غیرمستند است. در برخی موارد، او ممکن است منجر به تخلیه شود (تقریبا مترجم - متاسفانه پیوند به جایی نمی رسد، اما با قضاوت بر اساس آدرس اینترنتی، ما در مورد نمایه های فیلتر شده صحبت می کنیم).

/* 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 کافی است. اگر منتظر یک قفل کلید باشیم چه؟

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

در مورد من هم همینطور است 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 را اضافه کردم (به توصیه کلاوس اشنبرنر در توییتر) زیرا قفل ها می توانند مشکل ساز باشند. ما فقط می‌خواهیم به آنچه در حال حاضر وجود دارد و نه آنچه در زمان شروع تراکنش وجود داشت نگاه کنیم - فکر نمی‌کنم ثبات داده‌ها برای ما مهم باشد.
Voila، رکوردی که برای آن جنگیدیم!

قدردانی و مطالعه بیشتر

یادم نیست اولین بار چه کسی بسیاری از این چیزها را توصیف کرد، اما در اینجا دو پست در مورد کمترین مستنداتی که ممکن است دوست داشته باشید وجود دارد:

پست پل راندال در مورد %%physloc%% و sys.fn_PhysLocFormatter (همانطور که داده های خود را در مثال اول داریم)
سوال در مورد StackOverflow در مورد با استفاده از %%lockres%% (همانطور که داده ها را در مثال دوم یافتیم). یکی از پاسخ ها به پست منتهی می شود Grant Fritchey حدود %%lockres%% در سال 2010 نوشته شده است.

منبع: www.habr.com

رمزگشایی کلید و صفحه WaitResource در بن بست ها و قفل ها