فك تشفير المفتاح والصفحة WaitResource في حالات الجمود والأقفال

إذا كنت تستخدم تقرير عملية محظورة أو جمعت رسومًا بيانية عن حالة توقف تام يوفرها 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 عنوان URL لتلك الصفحة.



تفصيل "PAGE: 6: 3: 70133" نحصل على:

• معرّف قاعدة البيانات = 6
• data_file_id = 3
• page_numer = 70133

1.1) فك تشفير database_id

لنجد اسم قاعدة البيانات باستخدام الاستعلام:

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

هذه هي قاعدة بيانات 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: \ MSSQL \ DATA \ WideWorldImporters_UserData.ndf. (عفوًا ، لقد ضبطتني أضع ملفات على قرص النظام! لا! كان الأمر محرجًا).

1.3) احصل على اسم الكائن من DBCC PAGE

نحن نعلم الآن أن الصفحة رقم 70133 في ملف البيانات 3 تنتمي إلى قاعدة بيانات WorldWideImporters. يمكننا إلقاء نظرة على محتويات هذه الصفحة باستخدام صفحة DBCC غير موثقة وعلامة التتبع 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) هل يمكنك رؤية البيانات على الصفحة التي تم حظرها؟

Nuuu ، نعم. لكن ... هل أنت متأكد أنك بحاجة إليه حقًا؟

إنه بطيء حتى على الطاولات الصغيرة. لكن هذا رائع نوعًا ما ، لذا بما أنك قرأت هذا الآن ... فلنتحدث عن ٪٪ 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)" إلى أجزاء ، نحصل على:

• معرّف قاعدة البيانات = 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 على Twitter ) لأن الحظر يمكن أن يكون مشكلة. نريد فقط أن نرى ما هو موجود الآن ، وليس ما كان موجودًا عند بدء المعاملة - لا أعتقد أن اتساق البيانات مهم بالنسبة لنا.

فويلا ، الرقم القياسي الذي ناضلنا من أجله!

شكر وتقدير ومزيد من القراءة

لا أتذكر من الذي وصف العديد من هذه الأشياء لأول مرة ، ولكن إليك منشورتان حول الأشياء الأقل توثيقًا التي قد تعجبك:

• منشور Paul Randal على ٪٪ physloc ٪٪ و sys.fn_PhysLocFormatter (كما نفعل مع بياناتنا في المثال الأول)
• سؤال على StackOverflow حول استخدام ٪٪ lockres ٪٪ (كما وجدنا البيانات في المثال الثاني). تذهب إحدى الإجابات إلى منشور Grant Fritchey حول ٪٪ lockres ٪٪ ، والذي تمت كتابته مرة أخرى في عام 2010 .