Jika Anda menggunakan laporan proses yang diblokir atau mengumpulkan grafik kebuntuan yang disediakan oleh SQL Server secara berkala, Anda akan menemukan hal-hal seperti ini:
waitresource="HALAMAN: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Terkadang, akan ada lebih banyak informasi dalam XML raksasa yang Anda pelajari (grafik kebuntuan 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, sangat tersebar luas! Saya ingin menggabungkan semuanya, dari DBCC PAGE ke hobt_id ke fungsi %%physloc%% dan %%lockres%% yang tidak berdokumen.
Pertama, mari kita bicara tentang menunggu di kunci PAGE, lalu beralih ke kunci KEY.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Jika permintaan Anda menunggu di kunci PAGE, SQL Server akan memberi Anda alamat halaman itu.
Memecah "HALAMAN: 6:3:70133" kita mendapatkan:
- database_id = 6
- data_file_id = 3
- nomor_halaman = 70133
1.1) Dekripsi database_id
Temukan nama database menggunakan kueri:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOIni publik di SQL Server saya.
1.2) Mencari nama file data - jika Anda tertarik
Kita akan menggunakan data_file_id pada langkah selanjutnya untuk menemukan nama tabel. Anda dapat melompat ke langkah berikutnya, tetapi jika Anda tertarik dengan nama file, Anda dapat menemukannya dengan menjalankan kueri dalam konteks database yang ditemukan, mengganti data_file_id ke dalam kueri ini:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GODi database WideWorldImporters ini adalah file bernama WWI_UserData dan saya mengembalikannya ke C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, Anda memergoki saya meletakkan file di drive sistem! Tidak! Memalukan).
1.3) Dapatkan nama objek dari DBCC PAGE
Kita sekarang tahu bahwa halaman #70133 di datafile 3 milik database WorldWideImporters. Kita dapat melihat isi halaman ini menggunakan HALAMAN DBCC yang tidak berdokumen dan bendera pelacakan 3604.
Catatan: Saya lebih suka menggunakan DBCC PAGE pada salinan yang dipulihkan di suatu tempat di server lain, karena ini adalah barang yang tidak berdokumen. Dalam beberapa kasus, dia (kira-kira penerjemah - tautannya, sayangnya, tidak mengarah ke mana pun, tetapi dilihat dari urlnya, kita berbicara tentang indeks yang difilter).
/* 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
Dengan menggulir ke hasil, Anda dapat menemukan object_id dan index_id.
Hampir selesai! Sekarang Anda dapat menemukan nama tabel dan indeks dengan 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;
GODan di sini kita melihat bahwa menunggu kunci berada 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) Apakah mungkin untuk melihat data pada halaman yang diblokir?
Nuuu, ya. Tapi ... apakah Anda yakin Anda benar-benar membutuhkannya?
Ini lambat bahkan di meja kecil. Tapi ini keren, jadi karena Anda sudah membaca sejauh ini...mari kita bicara tentang %%physloc%%!
%%physloc%% adalah keajaiban tidak berdokumen yang mengembalikan ID fisik untuk setiap entri. Anda dapat gunakan .
Sekarang kami tahu bahwa kami ingin memblokir halaman di Sales.OrderLines, kami dapat melihat semua data dalam tabel ini, yang disimpan di file data #3 di halaman #70133, dengan kueri berikut:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOSeperti yang saya katakan, lambat bahkan di meja kecil. Saya menambahkan NOLOCK ke permintaan 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 ke 25 baris yang diperebutkan oleh kueri kami.
Cukup tentang kunci PAGE. Bagaimana jika kita sedang 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 terkunci sendiri, Anda 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
Ia bekerja persis sama dengan contoh di atas! Kami menemukan nama database menggunakan kueri:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GODalam kasus saya, itu sama .
2.2) Dekripsi hobt_id
Dalam konteks basis data yang ditemukan, Anda perlu menanyakan 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;
GOIni memberi tahu saya bahwa permintaan sedang menunggu di kunci Application.Countries menggunakan indeks PK_Application_Countries.
2.3) Sekarang untuk beberapa sihir %%lockres%% - jika Anda ingin mengetahui entri mana yang dikunci
Jika saya benar-benar ingin mengetahui di baris mana kunci itu diperlukan, saya dapat mengetahuinya dengan menanyakan tabel itu sendiri. Kita dapat menggunakan fungsi %%lockres%% yang tidak berdokumen 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 () karena kunci bisa menjadi masalah. Kami hanya ingin melihat apa yang ada sekarang, dan bukan apa yang ada saat transaksi dimulai - menurut saya konsistensi data tidak penting bagi kami.
Voila, rekor yang kami 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 postingan tentang hal-hal yang paling tidak didokumentasikan yang mungkin Anda sukai:
- Postingan Paul Randal tentang (karena kami memiliki data kami di contoh pertama)
- Pertanyaan tentang StackOverflow tentang (seperti yang kami temukan data dalam contoh kedua). Salah satu jawaban mengarah ke pos .
Sumber: www.habr.com