Diagnosis dan penyelesaian permintaan yang diblokir pada SQL Server - ApsaraDB RDS

Deskripsi masalah

Permintaan diblokir pada instance ApsaraDB RDS for SQL Server.

Penyebab

Sebuah aplikasi sering membaca atau menulis data ke tabel atau sumber daya. Jika banyak permintaan diblokir, pernyataan dijalankan dengan kecepatan rendah.

Identifikasi

Untuk mengidentifikasi masalah pemblokiran pada instance RDS, lakukan langkah-langkah berikut:

Jalankan pernyataan berikut untuk memantau sys.sysprocesses secara berkala guna mendapatkan informasi pemblokiran:
```
WHILE 1 = 1
BEGIN
    SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
    WAITFOR DELAY '00:00:01';
END;
```
Catatan
Anda dapat menentukan interval pemantauan. Dalam contoh ini, digunakan 00:00:01.
Gambar berikut menunjukkan contoh keluaran:
Catatan
Kolom blocked menunjukkan session ID dari header blokir. Kolom waitresource menunjukkan sumber daya yang ditunggu oleh sesi yang diblokir. Untuk informasi lebih lanjut tentang parameter dalam keluaran, lihat sys.sysprocesses (Transact-SQL).

Jalankan pernyataan berikut untuk memantau tampilan sys.dm_tran_locks dan sys.dm_os_waiting_tasks guna mendapatkan informasi pemblokiran:

WHILE 1 = 1
Begin
SELECT db.name DBName,
       tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
                                    tl.resource_type,
                                    h1.TEXT AS RequestingText,
                                    h2.TEXT AS BlockingText,
                                    tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
WAITFOR DELAY '00:00:01';
END;

Gambar berikut menunjukkan contoh keluaran:

Tabel berikut menjelaskan parameter dalam keluaran:

Parameter	Deskripsi
`DBName`	Nama database.
`request_session_id`	ID sesi dalam permintaan saat ini. Sesi tersebut adalah sesi yang diblokir.
`blocking_session_id`	ID sesi dari header blokir.
`BlockedObjectName`	Objek yang dikelola oleh sesi yang diblokir.
`resource_type`	Jenis sumber daya yang ditunggu oleh sesi.
`RequestingText`	Pernyataan yang dijalankan dalam sesi. Pernyataan tersebut adalah pernyataan yang diblokir.
`BlockingText`	Pernyataan yang dijalankan dalam sesi header blokir.
`request_mode`	Mode kunci yang diminta oleh sesi.

Saran optimasi

Lakukan langkah-langkah berikut untuk optimasi:

Hentikan sesi di header blokir untuk menyelesaikan masalah pemblokiran.
Periksa apakah ada transaksi yang tidak dikomit dalam waktu lama. Jika ada, komit transaksi tersebut sesegera mungkin.
Jika query diblokir karena kunci bersama dan aplikasi Anda mengizinkan pembacaan kotor, gunakan petunjuk query WITH (NOLOCK). Sebagai contoh, jalankan pernyataan SELECT * FROM table WITH (NOLOCK); untuk query tersebut. Dengan cara ini, query tidak akan mengajukan permintaan kunci untuk mencegah masalah pemblokiran.
Periksa logika aplikasi untuk mengakses sumber daya secara berurutan.

Referensi

Untuk informasi lebih lanjut tentang cara menyelesaikan masalah pemblokiran dalam situasi darurat, lihat Apa yang harus saya lakukan jika permintaan diblokir pada instance ApsaraDB RDS for SQL Server?.
Anda dapat menentukan metrik kinerja dan membuat aturan peringatan di konsol ApsaraDB RDS untuk mengidentifikasi dan menyelesaikan masalah kinerja sesegera mungkin. Untuk informasi lebih lanjut, lihat Pemantauan dan Peringatan.
Untuk informasi lebih lanjut tentang kebijakan optimasi kinerja, seperti optimasi indeks, optimasi query, dan optimasi penyimpanan, lihat Optimasi dan Diagnosis Kinerja.