गतिरोध और तालों में कुंजी और पृष्ठ प्रतीक्षा संसाधन को समझें

यदि आप अवरुद्ध प्रक्रिया रिपोर्ट का उपयोग करते हैं या समय-समय पर SQL सर्वर द्वारा प्रदान किए गए गतिरोध ग्राफ़ एकत्र करते हैं, तो आपको इस तरह की चीज़ों का सामना करना पड़ेगा:

waitresource='पेज: 6:3:70133'

waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“

कभी-कभी, उस विशाल XML में अधिक जानकारी होगी जिसका आप अध्ययन करते हैं (डेडलॉक ग्राफ़ में संसाधनों की एक सूची होती है जो आपको ऑब्जेक्ट और इंडेक्स के नाम ढूंढने में मदद करती है), लेकिन हमेशा नहीं।

यह पाठ आपको उन्हें समझने में मदद करेगा।

यहां जो भी जानकारी है वह इंटरनेट पर विभिन्न स्थानों पर है, वह बस बहुत वितरित है! मैं डीबीसीसी पेज से लेकर hobt_id से लेकर गैर-दस्तावेज %%physloc%% और %%lockres%% फ़ंक्शंस तक, यह सब एक साथ रखना चाहता हूँ।

सबसे पहले, पेज लॉक पर प्रतीक्षा के बारे में बात करते हैं, और फिर कुंजी लॉक पर आगे बढ़ते हैं।

1) waitresource='पेज: 6:3:70133' = डेटाबेस_आईडी: फाइलआईडी: पेजनंबर

यदि आपका अनुरोध पृष्ठ लॉक पर प्रतीक्षा कर रहा है, तो SQL सर्वर आपको उस पृष्ठ का पता देगा।

"पेज: 6:3:70133" को तोड़ने पर हमें मिलता है:

• डेटाबेस_आईडी = 6
• डेटा_फ़ाइल_आईडी = 3
• पेज_नंबर = 70133

1.1) डिक्रिप्ट डेटाबेस_आईडी

क्वेरी का उपयोग कर डेटाबेस का नाम खोजें:

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

यह सार्वजनिक है डीबी वाइडवर्ल्डइम्पोर्टर्स मेरे 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

वाइडवर्ल्डइम्पोर्टर्स डेटाबेस में यह WWI_UserData नामक एक फ़ाइल है और मैंने इसे C:MSSQLDATAWideWorldImporters_UserData.ndf पर पुनर्स्थापित कर दिया है। (उफ़, आपने मुझे सिस्टम ड्राइव पर फ़ाइलें डालते हुए पकड़ लिया! नहीं! यह शर्मनाक है)।

1.3) DBCC पृष्ठ से वस्तु का नाम प्राप्त करें

अब हम जानते हैं कि डेटाफाइल 70133 में पेज #3 वर्ल्डवाइडइम्पोर्टर्स डेटाबेस से संबंधित है। हम इस पेज की सामग्री को गैर-दस्तावेजी DBCC पेज और ट्रेस फ़्लैग 3604 का उपयोग करके देख सकते हैं।
नोट: मैं किसी अन्य सर्वर पर किसी पुनर्स्थापित प्रतिलिपि पर डीबीसीसी पेज का उपयोग करना पसंद करता हूं, क्योंकि यह अप्रलेखित सामग्री है। कुछ मामलों में, वह एक डंप में परिणाम हो सकता है (लगभग। अनुवादक - लिंक, दुर्भाग्य से, कहीं नहीं जाता है, लेकिन 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

और यहां हम देखते हैं कि लॉक पर प्रतीक्षा Sales.OrderLines तालिका के PK_Sales_OrderLines इंडेक्स पर थी।

ध्यान दें: SQL सर्वर 2014 और इसके बाद के संस्करण में, ऑब्जेक्ट का नाम अनिर्दिष्ट DMO sys.dm_db_database_page_allocations का उपयोग करके भी पाया जा सकता है। लेकिन आपको डेटाबेस में प्रत्येक पेज को क्वेरी करना होगा, जो बड़े डेटाबेस के लिए बहुत अच्छा नहीं लगता है, इसलिए मैंने डीबीसीसी पेज का उपयोग किया।

1.4) क्या अवरुद्ध किए गए पृष्ठ पर डेटा देखना संभव है?

नुउउ, हाँ। लेकिन... क्या आप निश्चित हैं कि आपको वास्तव में इसकी आवश्यकता है?
छोटी मेजों पर भी यह धीमा है। लेकिन यह बहुत अच्छा है, इसलिए जब से आपने इसे पढ़ा है...आइए %%physloc%% के बारे में बात करते हैं!

%%physloc%% जादू का एक अनियंत्रित टुकड़ा है जो प्रत्येक प्रविष्टि के लिए एक भौतिक आईडी देता है। आप उपयोग कर सकते हैं SQL सर्वर 2008 और उच्चतर में sys.fn_PhysLocFormatter के साथ %%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 पंक्तियाँ लौटाती है जिसके लिए हमारी क्वेरी लड़ी थी।

पेज लॉक के बारे में बहुत हो गया। क्या होगा यदि हम एक कुंजी लॉक की प्रतीक्षा कर रहे हैं?

2) Waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (मैजिक हैश जिसे %%lockres%% के साथ डिक्रिप्ट किया जा सकता है यदि आप वास्तव में चाहते हैं)

यदि आपकी क्वेरी किसी इंडेक्स प्रविष्टि को लॉक करने का प्रयास करती है और अपने आप लॉक हो जाती है, तो आपको एक बिल्कुल अलग प्रकार का पता मिलता है।
"6:72057594041991168 (ce52f92a058c)" को भागों में तोड़ने पर, हमें मिलता है:

• डेटाबेस_आईडी = 6
• hobt_id = 72057594041991168
• मैजिक हैश = (ce52f92a058c)

2.1) डिक्रिप्ट डेटाबेस_आईडी

यह बिल्कुल ऊपर दिए गए उदाहरण की तरह ही काम करता है! हम क्वेरी का उपयोग करके डेटाबेस का नाम ढूंढते हैं:

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

यह मुझे बताता है कि अनुरोध PK_Application_Countries इंडेक्स का उपयोग करके एप्लिकेशन.कंट्रीज़ लॉक पर प्रतीक्षा कर रहा था।

2.3) अब कुछ %%lockres%% जादू के लिए - यदि आप यह पता लगाना चाहते हैं कि कौन सी प्रविष्टि लॉक की गई थी

यदि मैं वास्तव में जानना चाहता हूं कि किस पंक्ति पर लॉक की आवश्यकता थी, तो मैं तालिका से ही पूछताछ करके पता लगा सकता हूं। हम मैजिक हैश से मेल खाने वाली प्रविष्टि ढूंढने के लिए अनिर्दिष्ट %%lockres%% फ़ंक्शन का उपयोग कर सकते हैं।
ध्यान दें कि यह क्वेरी संपूर्ण तालिका को स्कैन करेगी, और बड़ी तालिकाओं पर यह बिल्कुल भी मज़ेदार नहीं हो सकता है:

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

मैंने NOLOCK जोड़ा (ट्विटर पर क्लॉस एशेनब्रेनर की सलाह पर) क्योंकि ताले एक समस्या हो सकते हैं। हम केवल यह देखना चाहते हैं कि अभी क्या है, न कि यह देखना कि लेन-देन शुरू होने के समय क्या था - मुझे नहीं लगता कि डेटा स्थिरता हमारे लिए महत्वपूर्ण है।
वोइला, वह रिकॉर्ड जिसके लिए हमने लड़ाई लड़ी!

आभार और आगे पढ़ना

मुझे याद नहीं है कि इनमें से कई चीजों का वर्णन सबसे पहले किसने किया था, लेकिन यहां सबसे कम दस्तावेज वाली चीजों के बारे में दो पोस्ट हैं जो आपको पसंद आ सकती हैं:

• पॉल रैंडल की पोस्ट के बारे में %%physloc%% और sys.fn_PhysLocFormatter (जैसा कि हमारे पास पहले उदाहरण में हमारा डेटा है)
• स्टैक ओवरफ्लो के बारे में प्रश्न %%lockres%% का उपयोग करना (जैसा कि हमें दूसरे उदाहरण में डेटा मिला)। उत्तरों में से एक पोस्ट की ओर ले जाता है %%lockres%% के बारे में ग्रांट फ्रिची ने 2010 में लिखा था.

स्रोत: www.habr.com