คีย์ถอดรหัสและ Page WaitResource ในการล็อกตายและการล็อก

หากคุณใช้รายงานกระบวนการที่ถูกบล็อกหรือรวบรวมกราฟการชะงักงันที่จัดเตรียมโดย 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: หมายเลขหน้า

หากคำขอของคุณกำลังรอการล็อกหน้า SQL Server จะให้ที่อยู่ของหน้านั้นแก่คุณ

แยก "PAGE: 6:3:70133" เราได้รับ:

• ฐานข้อมูล_id=6
• data_file_id = 3
• page_number = 70133

1.1) ถอดรหัสฐานข้อมูล_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

ตอนนี้เราทราบแล้วว่าหน้า #70133 ใน datafile 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) เป็นไปได้ไหมที่จะเห็นข้อมูลบนเพจที่ถูกบล็อก?

นู๋ ค่ะ แต่...คุณแน่ใจหรือว่าคุณต้องการมันจริงๆ?
มันช้าแม้บนโต๊ะขนาดเล็ก แต่มันก็ค่อนข้างเจ๋ง ดังนั้นเมื่อคุณอ่านมาถึงตรงนี้แล้ว...เรามาพูดถึง %%physloc%% กันดีกว่า!

%%physloc%% เป็นเวทมนตร์ที่ไม่มีเอกสารซึ่งส่งคืน ID ทางกายภาพสำหรับแต่ละรายการ คุณสามารถใช้ได้ %%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)” ออกเป็นส่วนๆ เราจะได้:

• ฐานข้อมูล_id=6
• hobt_id = 72057594041991168
• แฮชวิเศษ = (ce52f92a058c)

2.1) ถอดรหัสฐานข้อมูล_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 (ตามคำแนะนำของ Klaus Aschenbrenner บนทวิตเตอร์) เพราะการล็อคอาจเป็นปัญหาได้ เราแค่ต้องการดูสิ่งที่มีอยู่ตอนนี้ ไม่ใช่สิ่งที่มีเมื่อเริ่มทำธุรกรรม - ฉันไม่คิดว่าความสอดคล้องของข้อมูลมีความสำคัญสำหรับเรา
Voila บันทึกที่เราต่อสู้เพื่อ!

กิตติกรรมประกาศและอ่านเพิ่มเติม

ฉันจำไม่ได้ว่าใครเป็นคนอธิบายสิ่งเหล่านี้เป็นคนแรก แต่นี่คือสองโพสต์เกี่ยวกับสิ่งที่มีเอกสารน้อยที่สุดที่คุณอาจชอบ:

• โพสต์ของ Paul Randall เกี่ยวกับ %%physloc%% และ sys.fn_PhysLocFormatter (ตามที่เรามีข้อมูลในตัวอย่างแรก)
• คำถามเกี่ยวกับ StackOverflow เกี่ยวกับ ใช้ %%lockres%% (ตามที่เราพบข้อมูลในตัวอย่างที่สอง) หนึ่งในคำตอบนำไปสู่การโพสต์ ให้ Fritchey เขียนเกี่ยวกับ %%lockres%% ในปี 2010.

ที่มา: will.com