🥇פענוח מפתח ועמוד 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 ייתן לך את הכתובת של אותו עמוד.

לפירוט "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

זה פומבי DB WideWorldImporters ב-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 הבלתי מתועד ודגל המעקב 3604.
הערה: אני מעדיף להשתמש ב-DBCC PAGE על עותק משוחזר מגיבוי איפשהו בשרת אחר, כי זה דבר לא מתועד. במקרים מסוימים, היא עלול לגרום ליצירת dump (משוער. מתרגם - הקישור, למרבה הצער, לא מוביל לשום מקום, אבל אם לשפוט לפי כתובת האתר, אנחנו מדברים על אינדקסים מסוננים).

/* 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. מה אם אנחנו מחכים למנעול KEY?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (hash קסם שניתן לפענח באמצעות %%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%% כדי למצוא ערך התואם ל-hash הקסם.
שימו לב שהשאילתה הזו תסרוק את כל הטבלה, ובטבלאות גדולות זה עשוי להיות לא כיף בכלל:

SELECT
    *
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO

הוספתי את NOLOCK (בעצת קלאוס אשנברנר בטוויטר) כי חסימות יכולות להפוך לבעיה. אנחנו רק רוצים להסתכל על מה שיש עכשיו, ולא מה היה שם כשהעסקה התחילה - אני לא חושב שעקביות הנתונים חשובה לנו.
וואלה, השיא שנלחמנו עליו!

תודות וקריאה נוספת

אני לא זוכר מי תיאר לראשונה הרבה מהדברים האלה, אבל הנה שני פוסטים על הדברים הכי פחות מתועדים שאולי תאהב:

הפוסט של פול רנדל על %%physloc%% ו-sys.fn_PhysLocFormatter (כפי שיש לנו את הנתונים שלנו בדוגמה הראשונה)
שאלה בנושא StackOverflow משתמש ב-%%lockres%% (כפי שמצאנו את הנתונים בדוגמה השנייה). אחת התשובות מובילה לפוסט גרנט פריצ'י על %%lockres%%, נכתב עוד ב-2010.

מקור: www.habr.com

פענוח מפתח ו-Page WaitResource במנעולים ומנעולים