Clever Geek Handbook

Mendekripsi Kunci dan Halaman WaitResource dalam kebuntuan dan kunci

Jika Anda menggunakan laporan proses yang diblokir atau mengumpulkan grafik kebuntuan yang disediakan oleh SQL Server dari waktu ke waktu, Anda akan menemukan hal-hal berikut:



waitresource = “HALAMAN: 6: 3: 70133“



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


Terkadang, akan ada lebih banyak informasi dalam XML raksasa yang sedang Anda pelajari (grafik yang mengalami deadlock berisi daftar sumber daya yang membantu Anda mengetahui nama objek dan indeks), tetapi tidak selalu.



Teks ini akan membantu Anda menguraikannya.



Semua informasi yang ada di sini ada di Internet di berbagai tempat, itu sangat didistribusikan! Saya ingin menggabungkan semuanya - dari DBCC PAGE hingga hobt_id dan fungsi %% physloc %% dan %% lockres %% yang tidak berdokumen.



Pertama, mari kita bicara tentang menunggu kunci PAGE, lalu lanjutkan ke kunci KEY.



1) waitresource = “PAGE: 6: 3: 70133” = Database_Id: FileId: PageNumber



Jika kueri Anda menunggu di kunci PAGE, SQL Server akan memberi Anda URL halaman itu.



Menguraikan "HALAMAN: 6: 3: 70133" kita mendapatkan:



  • database_id = 6
  • data_file_id = 3
  • page_numer = 70133




1.1) Dekripsi database_id



Mari cari nama database menggunakan query: 

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


Ini adalah database WideWorldImporters publik di SQL Server saya.



1.2) Mencari nama file data - jika Anda tertarik



Kami akan menggunakan data_file_id di langkah selanjutnya untuk menemukan nama tabel. Anda bisa langsung ke langkah berikutnya, tetapi jika Anda tertarik dengan nama file, Anda dapat menemukannya dengan menjalankan kueri dalam konteks DB yang ditemukan, menggantikan data_file_id dalam kueri ini: 

USE WideWorldImporters;
GO
SELECT 
    name, 
    physical_name
FROM sys.database_files
WHERE file_id = 3;
GO


Dalam database WideWorldImporters, ini adalah file bernama WWI_UserData dan saya mengembalikannya ke C: \ MSSQL \ DATA \ WideWorldImporters_UserData.ndf. (Ups, Anda memergoki saya memasukkan file ke disk sistem! Tidak! Itu canggung).



1.3) Dapatkan nama objek dari DBCC PAGE



Sekarang kita tahu bahwa halaman # 70133 dalam file data 3 milik database WorldWideImporters. Kita dapat melihat isi halaman ini menggunakan HALAMAN DBCC tidak berdokumen dan bendera pelacakan 3604.

Catatan: Saya lebih suka menggunakan HALAMAN DBCC pada cadangan yang dipulihkan dari cadangan di suatu tempat di server lain, karena ini adalah hal yang tidak berdokumen. Dalam beberapa kasus, ini dapat menyebabkan pembuatan dump ( komentar penerjemah - tautan, sayangnya, tidak mengarah ke mana pun, tetapi dilihat dari url, kita berbicara tentang indeks-tersaring ). 

/* 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


Gulir ke bawah ke hasil, Anda dapat menemukan object_id dan index_id.



Hampir siap! Sekarang Anda dapat menemukan tabel dan nama indeks menggunakan kueri: 

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


Dan sekarang kita melihat bahwa penantian di kunci ada di indeks PK_Sales_OrderLines dari tabel Sales.OrderLines.



Catatan: Di SQL Server 2014 dan yang lebih baru, nama objek juga dapat ditemukan menggunakan sys.dm_db_database_page_allocations DMO yang tidak berdokumen. Tetapi Anda harus menanyakan setiap halaman dalam database, yang tidak terlihat keren untuk database besar, jadi saya menggunakan DBCC PAGE.



1.4) Dapatkah Anda melihat data di halaman yang diblokir?



Nuuu, ya. Tapi ... apakah kamu yakin benar-benar membutuhkannya?

Ini lambat bahkan di meja kecil. Tapi ini agak keren, jadi karena Anda telah membaca sejauh ini ... mari kita bicara tentang %% physloc %%!



%% physloc %% adalah bagian sihir tak berdokumen yang mengembalikan id fisik untuk setiap entri. Anda dapat menggunakan %% physloc %% bersama dengan sys.fn_PhysLocFormatter di SQL Server 2008 dan yang lebih baru .



Sekarang kita tahu bahwa kita ingin memblokir halaman di Sales.OrderLines, kita dapat melihat semua data dalam tabel ini, yang disimpan di file data # 3 di halaman # 70133, menggunakan query berikut: 

Use WideWorldImporters;
GO
SELECT 
    sys.fn_PhysLocFormatter (%%physloc%%),
    *
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GO




Seperti yang saya katakan - lambat bahkan di meja kecil. Saya menambahkan NOLOCK ke kueri karena kami masih tidak memiliki jaminan bahwa data yang ingin kami lihat persis sama dengan saat kunci ditemukan - sehingga kami dapat melakukan pembacaan kotor dengan aman.

Tapi, hore, kueri mengembalikan saya 25 baris yang sama dengan yang diperjuangkan kueri kami.



Cukup tentang kunci PAGE. Bagaimana jika kita menunggu kunci KUNCI?



2) waitresource = “KEY: 6: 72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (hash ajaib yang dapat didekripsi dengan %% lockres %% jika Anda benar-benar menginginkannya)





Jika kueri Anda mencoba mengunci entri indeks dan mengunci dirinya sendiri, Anda akan mendapatkan jenis alamat yang sama sekali berbeda.

Memecah "6: 72057594041991168 (ce52f92a058c)" menjadi beberapa bagian, kita mendapatkan:

  • database_id = 6
  • hobt_id = 72057594041991168
  • hash ajaib = (ce52f92a058c)




2.1) Dekripsi database_id



Cara kerjanya sama persis dengan contoh di atas! Temukan nama database menggunakan query: 

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


Dalam kasus saya, ini adalah database WideWorldImporters yang sama .



2.2) Dekripsi hobt_id



Dalam konteks database yang ditemukan, Anda perlu menjalankan kueri ke sys.partitions dengan beberapa gabungan, yang akan membantu menentukan nama tabel dan indeks ... 

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


Itu memberitahu saya bahwa permintaan itu menunggu di Application.Countries mengunci menggunakan indeks PK_Application_Countries.



2.3) Sekarang beberapa %% lockres %% ajaib - jika Anda ingin mengetahui record mana yang dikunci



Jika saya benar-benar ingin tahu di baris mana kunci itu diperlukan, saya bisa mengetahuinya dengan menanyakan tabel itu sendiri. Kita bisa menggunakan fungsi %% lockres %% yang tidak terdokumentasi untuk menemukan entri yang cocok dengan hash ajaib.

Perhatikan bahwa kueri ini akan memindai seluruh tabel, dan pada tabel besar ini mungkin tidak menyenangkan sama sekali: 

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


Saya menambahkan NOLOCK ( atas saran Klaus Aschenbrenner di twitter ) karena pemblokiran bisa menjadi masalah. Kami hanya ingin melihat apa yang ada di sana sekarang, dan bukan apa yang ada di sana saat transaksi dimulai - menurut saya konsistensi data tidak penting bagi kami.

Voila, rekor yang kita perjuangkan!





Ucapan Terima Kasih dan Bacaan Lebih Lanjut



Saya tidak ingat siapa yang pertama kali mendeskripsikan banyak dari hal-hal ini, tetapi berikut adalah dua pos tentang hal-hal yang paling sedikit terdokumentasi yang mungkin Anda sukai:



More articles:


All Articles