Bagikan melalui

Memahami dan mengatasi masalah pemblokiran

  • Artikel

Berlaku untuk:Database Azure SQLDatabase SQL di Fabric

Artikel ini menjelaskan pemblokiran di database Azure SQL Database dan Fabric SQL, dan menunjukkan cara memecahkan masalah dan mengatasi pemblokiran.

Tujuan

Dalam artikel ini, istilah koneksi mengacu pada satu sesi log-on database. Setiap koneksi muncul sebagai ID sesi (SPID), atau session_id di banyak DMV. Masing-masing SPID ini sering disebut sebagai proses, meskipun bukan konteks proses terpisah dalam arti biasa. Sebaliknya, setiap SPID terdiri dari sumber daya server dan struktur data yang diperlukan untuk melayani permintaan koneksi tunggal dari klien tertentu. Satu aplikasi klien mungkin memiliki satu atau beberapa koneksi. Dari perspektif Azure SQL Database, tidak ada perbedaan antara beberapa koneksi dari satu aplikasi klien pada satu komputer klien dan beberapa koneksi dari beberapa aplikasi klien atau beberapa komputer klien; mereka atomik. Satu koneksi dapat memblokir koneksi lain, terlepas dari sumber klien.

Untuk informasi tentang pemecahan masalah kebuntuan, lihat Menganalisis dan mencegah kebuntuan di database Azure SQL Database dan Fabric SQL.

Catatan

Konten ini difokuskan pada Azure SQL Database. Azure SQL Database didasarkan pada versi stabil terbaru mesin database Microsoft SQL Server, sehingga banyak konten serupa meskipun opsi dan alat pemecahan masalah mungkin berbeda. Untuk informasi selengkapnya tentang pemblokiran di SQL Server, lihat Memahami dan mengatasi masalah pemblokiran SQL Server. Database Fabric SQL berbagi banyak fitur dengan Azure SQL Database. Untuk informasi selengkapnya tentang pemantauan performa, lihat Memantau database SQL di Microsoft Fabric.

Memahami pemblokiran

Pemblokiran adalah karakteristik yang tidak dapat dihindari dari setiap sistem manajemen database relasional (RDBMS) dengan konkurensi berbasis kunci. Pemblokiran di Azure SQL Database terjadi ketika satu sesi memegang kunci pada sumber daya tertentu dan sesi kedua (SPID) mencoba untuk mendapatkan jenis kunci yang bertentangan pada sumber daya yang sama. Biasanya, jangka waktu di mana SPID pertama mengunci sumber tersebut relatif singkat. Ketika sesi pemilik melepaskan kunci, koneksi kedua kemudian bebas untuk mendapatkan kuncinya sendiri pada sumber daya tersebut dan melanjutkan pemrosesan. Perilaku ini normal, dan dapat terjadi berkali-kali sepanjang hari tanpa efek yang nyata pada performa sistem.

Setiap database baru di Azure SQL Database memiliki pengaturan database read committed snapshot (RCSI) yang diaktifkan secara default. Pemblokiran antara sesi membaca data dan sesi menulis data diminimalkan dalam RCSI, yang menggunakan versi baris untuk meningkatkan keserentakan. Namun, pemblokiran dan kebuntuan masih dapat terjadi dalam database di Azure SQL Database karena:

  • Kueri yang mengubah data mungkin saling memblokir.
  • Kueri mungkin dijalankan pada tingkat isolasi yang dapat meningkatkan risiko pemblokiran. Tingkat isolasi dapat ditentukan dalam string koneksi aplikasi, petunjuk kueri, atau pernyataan SET dalam Transact-SQL.
  • RCSI mungkin dinonaktifkan, menyebabkan database menggunakan kunci bersama (S) untuk melindungi pernyataan SELECT yang dijalankan di bawah tingkat isolasi yang diterapkan baca. Ini dapat meningkatkan pemblokiran dan kebuntuan.

Tingkat isolasi snapshot juga diaktifkan secara default untuk database baru di Azure SQL Database. Isolasi snapshot adalah tingkat isolasi berbasis baris tambahan yang menyediakan konsistensi tingkat transaksi untuk data dan menggunakan versi baris untuk memilih baris yang akan diperbarui. Untuk menggunakan isolasi snapshot, kueri atau koneksi harus secara eksplisit mengatur tingkat isolasi transaksi mereka ke SNAPSHOT. Ini hanya dapat dilakukan ketika isolasi rekam jepret diaktifkan untuk database.

Anda dapat mengidentifikasi apakah isolasi RCSI dan/atau snapshot diaktifkan dengan Transact-SQL. Sambungkan ke database Anda di Azure SQL Database dan jalankan kueri berikut:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Jika RCSI diaktifkan, is_read_committed_snapshot_on kolom mengembalikan nilai 1. Jika isolasi rekam jepret diaktifkan, snapshot_isolation_state_desc kolom mengembalikan nilai AKTIF.

Durasi dan konteks transaksi kueri menentukan berapa lama kuncinya disimpan, dan efeknya pada kueri lain. Pernyataan SELECT yang dijalankan di bawah RCSI tidak memperoleh kunci bersama (S) pada data yang sedang dibaca, dan oleh karena itu jangan memblokir transaksi yang memodifikasi data. Untuk pernyataan INSERT, UPDATE, dan DELETE, kunci ditahan selama kueri, baik untuk konsistensi data maupun untuk memungkinkan kueri digulung balik jika perlu.

Untuk kueri yang dijalankan dalam transaksi eksplisit, jenis kunci dan durasi yang menahan kunci ditentukan oleh jenis kueri, tingkat isolasi transaksi, dan apakah petunjuk kunci digunakan dalam kueri. Untuk deskripsi penguncian, petunjuk kunci, dan tingkat isolasi transaksi, lihat artikel berikut ini:

Saat penguncian dan pemblokiran berlanjut ke titik di mana ada efek yang merugikan pada performa sistem, itu karena salah satu alasan berikut:

  • SPID mengunci seperangkat sumber daya untuk jangka waktu yang lama sebelum melepaskannya. Jenis pemblokiran ini selesai sendiri dari waktu ke waktu tetapi dapat menyebabkan penurunan performa.

  • SPID mengunci seperangkat sumber daya dan tidak pernah melepaskannya. Jenis pemblokiran ini tidak menyelesaikan sendiri dan mencegah akses ke sumber daya yang terpengaruh tanpa batas waktu.

Pada skenario pertama, situasinya bisa sangat berubah-ubah karena SPID yang berbeda menyebabkan pemblokiran pada sumber daya yang berbeda dari waktu ke waktu, menciptakan target yang bergerak. Masalah pada situasi ini sulit dipecahkan menggunakan SQL Server Management Studio untuk mempersempit masalah ke kueri individual. Sebaliknya, situasi kedua menghasilkan sebuah keadaan konsisten yang dapat lebih mudah didiagnosis.

Penguncian yang dioptimalkan

Penguncian yang dioptimalkan adalah fitur baru pada mesin database yang secara drastis mengurangi penggunaan memori kunci dan jumlah kunci yang diperlukan secara bersamaan untuk proses menulis. Penguncian yang dioptimalkan menggunakan dua komponen utama: Penguncian ID Transaksi (TID) (juga digunakan dalam fitur-fitur lain dari penerapan versi baris) dan Kunci Setelah Kualifikasi (LAQ). Ini tidak memerlukan konfigurasi tambahan.

Artikel ini membahas perilaku Mesin Database tanpa penguncian yang dioptimalkan pada saat ini.

Untuk informasi selengkapnya dan untuk mempelajari di mana penguncian yang dioptimalkan tersedia, lihat Penguncian yang dioptimalkan.

Aplikasi dan pemblokiran

Mungkin ada kecenderungan untuk fokus pada penyetelan sisi server dan masalah platform saat menghadapi masalah pemblokiran. Namun, hanya memperhatikan database mungkin tidak mengarah pada resolusi, hal ini dapat menghabiskan waktu dan energi yang seharusnya lebih baik diarahkan untuk memeriksa aplikasi klien dan kueri yang dikirimkannya. Tidak peduli tingkat visibilitas apa yang diekspos aplikasi mengenai panggilan database yang dilakukan, masalah pemblokiran sering memerlukan pemeriksaan pernyataan SQL yang tepat yang dikirimkan oleh aplikasi, dan perilaku tepat aplikasi mengenai pembatalan kueri, manajemen koneksi, mengambil semua baris hasil, dan sebagainya. Jika alat pengembangan tidak mengizinkan kontrol eksplisit atas manajemen koneksi, pembatalan kueri, batas waktu kueri, pengambilan hasil, dan sebagainya, memblokir masalah mungkin tidak dapat diselesaikan. Potensi ini harus diperiksa secara ketat sebelum memilih alat pengembangan aplikasi untuk Azure SQL Database, terutama untuk lingkungan OLTP dengan performa sensitif.

Perhatikan performa database selama fase desain dan konstruksi database dan aplikasi. Khususnya, konsumsi sumber daya, tingkat isolasi, dan panjang jalur transaksi harus dievaluasi untuk setiap kueri. Setiap kueri dan transaksi harus seefisien mungkin. Disiplin manajemen koneksi yang baik harus dijalankan. Tanpa itu, aplikasi dapat tampak memiliki performa yang dapat diterima pada jumlah pengguna yang rendah, tetapi performanya mungkin menurun secara signifikan saat jumlah pengguna meningkat.

Dengan aplikasi dan desain kueri yang tepat, Azure SQL Database mampu mendukung ribuan pengguna secara simultan di satu server, dengan sedikit pemblokiran.

Catatan

Untuk panduan pengembangan aplikasi lainnya, lihat Memecahkan masalah konektivitas dan kesalahan lain dan Penanganan Kesalahan Sementara.

Memecahkan masalah pemblokiran

Terlepas dari situasi pemblokiran apa pun, metode untuk pemecahan masalah penguncian yang digunakan adalah sama. Pemisahan logis inilah yang menentukan sisa komposisi artikel ini. Konsepnya adalah menemukan pemblokir utama dan mengidentifikasi apa yang dilakukan oleh kueri tersebut dan mengapa kueri itu memblokir. Setelah kueri bermasalah diidentifikasi (yaitu apa yang mengunci untuk waktu yang lama), langkah berikutnya adalah menganalisis dan menentukan mengapa pemblokiran terjadi. Setelah kami memahami alasannya, kami kemudian dapat membuat perubahan dengan mendesain ulang kueri dan transaksi.

Langkah-langkah dalam pemecahan masalah:

  1. Identifikasi sesi pemblokiran utama (pemblokir utama)

  2. Temukan kueri dan transaksi yang menyebabkan pemblokiran (apa yang mengunci untuk jangka waktu yang lama)

  3. Analisis/pahami mengapa pemblokiran berkepanjangan terjadi

  4. Atasi masalah pemblokiran dengan mendesain ulang kueri dan transaksi

Sekarang mari kita dalami dan membahas cara menentukan sesi pemblokiran utama dengan tangkapan data yang sesuai.

Mengumpulkan informasi pemblokiran

Untuk mengatasi kesulitan pemecahan masalah pemblokiran, administrator database dapat menggunakan skrip SQL yang terus memantau keadaan penguncian dan pemblokiran dalam database dia Azure SQL Database. Untuk mengumpulkan data ini, pada dasarnya ada dua metode.

Yang pertama adalah mengkueri objek manajemen dinamis (DMOs) dan menyimpan hasilnya sebagai perbandingan secara berkala. Beberapa objek yang direferensikan dalam artikel ini adalah tampilan manajemen dinamis (DMV) dan beberapa di antaranya adalah fungsi manajemen dinamis (DMF). Metode kedua adalah menggunakan XEvents untuk mengetahui apa yang sedang berjalan.

Mengumpulkan informasi dari kantor kendaraan bermotor (DMV)

Merujuk pada DMV untuk mengatasi masalah pemblokiran dengan tujuan untuk mengidentifikasi SPID (ID sesi) di kepala rantai pemblokiran dan Pernyataan SQL-nya. Carilah SPID korban yang sedang diblokir. Jika ada SPID yang diblokir oleh SPID lain, maka selidiki SPID yang memiliki sumber (SPID pemblokiran). Apakah SPID milik pemilik tersebut juga diblokir? Anda dapat mengikuti rantai untuk menemukan pemblokir utama dan kemudian menyelidiki mengapa pemblokir tersebut mempertahankan kuncinya.

Pastikan untuk menjalankan setiap skrip ini dalam database target di Azure SQL Database.

  • Perintah sp_who dan sp_who2 adalah perintah yang lebih lama untuk menampilkan semua sesi saat ini. DMV sys.dm_exec_sessions menampilkan lebih banyak data dalam set hasil yang lebih mudah untuk dikueri dan difilter. Anda dapat menemukan sys.dm_exec_sessions di inti kueri lain.

  • Jika Anda sudah memiliki sesi tertentu yang telah teridentifikasi, Anda dapat menggunakan DBCC INPUTBUFFER(<session_id>) untuk menemukan pernyataan terakhir yang dikirimkan oleh sesi. Hasil serupa dapat ditampilkan dengan fungsi manajemen dinamis (DMF) sys.dm_exec_input_buffer dalam set hasil yang lebih mudah untuk dikueri dan difilter, yang menyajikan session_id dan request_id. Misalnya, untuk menampilkan kueri terbaru yang dikirimkan session_id 66 dan request_id 0:

SELECT * FROM sys.dm_exec_input_buffer (66,0);

  • Lihat kolom blocking_session_id di sys.dm_exec_requests. Ketika blocking_session_id = 0, sesi tidak diblokir. Meskipun sys.dm_exec_requests hanya mencantumkan permintaan yang saat ini dijalankan, koneksi apa pun (aktif atau tidak) tercantum di sys.dm_exec_sessions. Bangun dari gabungan bersama antara sys.dm_exec_requests dan sys.dm_exec_sessions ini di kueri berikutnya.

  • Jalankan kueri sampel ini untuk menemukan kueri yang dieksekusi secara aktif dan teks batch SQL atau teks buffer input saat ini menggunakan sys.dm_exec_sql_text atau sys.dm_exec_input_buffer DMV. Jika data yang dikembalikan oleh bidang textsys.dm_exec_sql_text adalah NULL, kueri saat ini tidak dijalankan. Dalam hal ini, event_info bidang sys.dm_exec_input_buffer berisi string perintah terakhir yang diteruskan ke mesin SQL. Kueri ini juga dapat digunakan untuk mengidentifikasi sesi yang memblokir sesi lain, termasuk daftar session_id yang diblokir per session_id.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;
  • Jalankan kueri sampel yang lebih rumit ini, yang disediakan oleh Dukungan Microsoft, untuk mengidentifikasi kepala rantai pemblokiran beberapa sesi, termasuk teks kueri sesi yang terlibat dalam rantai pemblokiran.
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];
  • Rujuk sys.dm_os_waiting_tasks yang ada di lapisan thread/tugas SQL. Ini menampilkan informasi tentang permintaan jenis tunggu SQL apa yang saat ini sedang berjalan. Seperti sys.dm_exec_requests, hanya permintaan aktif yang ditampilkan oleh sys.dm_os_waiting_tasks.

Catatan

Untuk informasi lebih lanjut tentang jenis tunggu termasuk statistik tunggu agregat dari waktu ke waktu, lihat DMV sys.dm_db_wait_stats. DMV ini menampilkan statistik tunggu agregat untuk database saat ini saja.

  • Gunakan DMV sys.dm_tran_locks untuk informasi yang lebih terperinci tentang kunci apa yang telah ditempatkan oleh kueri. DMV ini dapat mengembalikan data dalam jumlah besar dalam database produksi, dan berguna untuk mendiagnosa kunci apa yang saat ini dipegang.

Karena INNER JOIN pada sys.dm_os_waiting_tasks, kueri berikut membatasi output dari sys.dm_tran_locks hanya untuk permintaan yang saat ini diblokir, status tunggu, dan penguncian:

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';
  • Dengan DMV, menyimpan hasil kueri dari waktu ke waktu menyediakan titik data yang memungkinkan Anda meninjau pemblokiran selama interval waktu tertentu untuk mengidentifikasi pemblokiran atau tren yang bertahan.

Mengumpulkan informasi dari Extended Events

Selain informasi sebelumnya, sering kali perlu untuk mengambil jejak aktivitas di server untuk menyelidiki masalah pemblokiran di Azure SQL Database. Misalnya, jika sesi menjalankan beberapa pernyataan dalam transaksi, hanya pernyataan terakhir yang dikirimkan yang diwakili. Namun, salah satu pernyataan sebelumnya mungkin menjadi alasan kunci masih ditahan. Jejak memungkinkan Anda melihat semua perintah yang dijalankan oleh sesi dalam transaksi saat ini.

Ada dua cara untuk memperoleh jejak di SQL Server; Extended Events (XEvents) dan Profiler Traces. Namun, SQL Server Profiler adalah teknologi pelacakan yang sudah usang dan tidak didukung untuk Azure SQL Database. Extended Events adalah teknologi pelacakan yang lebih baru yang memungkinkan lebih banyak fleksibilitas dan lebih sedikit efek pada sistem yang diamati, dan antarmukanya diintegrasikan ke dalam SQL Server Management Studio (SSMS).

Lihat dokumen yang menjelaskan cara menggunakan Extended Events New Session Wizard di SQL Server Management Studio. Namun untuk database Azure SQL, SQL Server Management Studio menyediakan subfolder Extended Events di bawah setiap database di Object Explorer. Gunakan wizard sesi Extended Events untuk menangkap kejadian yang berguna ini:

  • Kesalahan Kategori:

    • Perhatian
    • Kesalahan_dilaporkan
    • Peringatan Eksekusi

  • Kategori Peringatan:

    • Predikat_join_hilang

  • Kategori Eksekusi:

    • Rpc_selesai
    • Rpc_memulai
    • Batch_SQL_Selesai
    • Mulai_pengelompokan_Sql

  • Kategori: deadlock_monitor

    • laporan_kebuntuan_xml_database

  • Sesi kategori

    • Koneksi_yang_Ada
    • Masuk
    • Keluar

Catatan

Untuk informasi terperinci tentang kebuntuan, lihat Menganalisis dan mencegah kebuntuan di database Azure SQL Database dan Fabric SQL.

Mengidentifikasi dan mengatasi skenario pemblokiran umum

Dengan memeriksa informasi sebelumnya, Anda dapat menentukan penyebab sebagian besar masalah pemblokiran. Sisa artikel ini adalah diskusi tentang cara menggunakan informasi ini untuk mengidentifikasi dan menyelesaikan beberapa skenario pemblokiran umum. Diskusi ini mengasumsikan Anda telah menggunakan skrip pemblokiran (seperti disebutkan sebelumnya) untuk mengumpulkan informasi tentang SPID yang memblokir dan telah menangkap aktivitas aplikasi menggunakan sesi XEvent.

Menganalisis data yang diblokir

  • Periksa output dari DMV sys.dm_exec_requests dan sys.dm_exec_sessions untuk menentukan kepala dari rantai pemblokiran, menggunakan blocking_these dan session_id. Ini paling jelas mengidentifikasi permintaan mana yang diblokir dan mana yang memblokir. Lihat lebih jauh ke sesi yang diblokir dan diblokir. Apakah ada kesamaan atau akar untuk rantai pemblokiran? Mereka kemungkinan berbagi tabel yang sama, dan satu atau lebih sesi yang terlibat dalam rantai pemblokiran sedang melakukan operasi penulisan.

  • Periksa output DMV sys.dm_exec_requests dan sys.dm_exec_sessions untuk informasi tentang SPID di bagian depan rantai pemblokiran. Cari bidang berikut:

    • sys.dm_exec_requests.status
      Kolom ini memperlihatkan status permintaan tertentu. Biasanya, status tidur menunjukkan bahwa SPID telah menyelesaikan eksekusi dan sedang menunggu aplikasi untuk mengirimkan kueri atau batch lain. Status dapat dijalankan atau sedang berjalan menunjukkan bahwa SPID sedang memproses kueri. Tabel berikut ini memberikan penjelasan singkat tentang berbagai nilai status.
    Status Makna
    Latar belakang SPID menjalankan tugas latar belakang, seperti deteksi kebuntuan, penulis log, atau pos pemeriksaan.
    Tidur SPID tidak sedang berjalan. Ini biasanya menunjukkan bahwa SPID sedang menunggu perintah dari aplikasi.
    Berlari SPID saat ini berjalan dengan penjadwalan.
    Dapat dijalankan SPID berada dalam antrean siap dijalankan penjadwal dan menunggu untuk mendapatkan waktu penjadwalan.
    Ditangguhkan SPID sedang menunggu sumber, seperti kunci atau kait.

    • sys.dm_exec_sessions.open_transaction_count
      Bidang ini memberi tahu Anda jumlah transaksi terbuka dalam sesi ini. Jika nilai ini lebih besar dari 0, SPID berada dalam transaksi terbuka dan mungkin memegang kunci yang diperoleh oleh pernyataan apa pun dalam transaksi.

    • sys.dm_exec_requests.open_transaction_count
      Sama seperti itu, bidang ini memberi tahu Anda jumlah transaksi terbuka dalam permintaan ini. Jika nilai ini lebih besar dari 0, SPID berada dalam transaksi terbuka dan mungkin memegang kunci yang diperoleh oleh pernyataan apa pun dalam transaksi.

    • sys.dm_exec_requests.wait_type, wait_time, dan last_wait_type
      Jika sys.dm_exec_requests.wait_type adalah NULL, permintaan saat ini tidak menunggu apa pun dan nilai last_wait_type menunjukkan wait_type terakhir yang ditemui permintaan. Untuk informasi selengkapnya tentang sys.dm_os_wait_stats dan deskripsi jenis tunggu yang paling umum, lihat sys.dm_os_wait_stats. Nilai wait_time dapat digunakan untuk menentukan apakah permintaan membuat kemajuan. Saat kueri terhadap tabel sys.dm_exec_requests mengembalikan nilai di kolom wait_time yang kurang dari nilai wait_time dari kueri sebelumnya sys.dm_exec_requests, ini menunjukkan bahwa kunci sebelumnya telah diperoleh dan dirilis, dan sekarang sedang menunggu kunci baru (dengan asumsi wait_time tidak bernilai nol). Ini dapat diverifikasi dengan membandingkan wait_resource antara output sys.dm_exec_requests yang menampilkan sumber daya yang permintaannya sedang menunggu.

    • sys.dm_exec_requests.wait_resource Bidang ini menunjukkan sumber daya yang ditunggu oleh permintaan yang terblokir. Tabel berikut ini mencantumkan wait_resource format umum dan artinya:

    Sumber daya Format Contoh Penjelasan
    Tabel DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 Dalam hal ini, database ID 5 adalah database sampel pub dan objek ID 261575970 adalah tabel judul dan 1 adalah indeks berkluster.
    Halaman DatabaseID:FileID:PageID HALAMAN: 5:1:104 Dalam hal ini, ID database 5 is pubs, ID file 1 adalah file data utama, dan halaman 104 adalah halaman milik tabel judul. Untuk mengidentifikasi object_id yang dimiliki halaman, gunakan fungsi manajemen dinamis sys.dm_db_page_info, dengan memasukkan DatabaseID, FileId, dan PageId dari wait_resource.
    Kunci DatabaseID:Hobt_id (Nilai hash untuk kunci indeks) KUNCI: 5:72057594044284928 (3300a4f361aa) Dalam hal ini, ID database 5 pubs, dan Hobt_ID 72057594044284928 sesuai dengan index_id 2 untuk object_id 261575970 (tabel judul). Gunakan tampilan katalog sys.partitions untuk mengaitkan hobt_id ke index_id dan object_idtertentu. Tidak ada cara untuk membalikkan hash kunci indeks menjadi nilai kunci tertentu.
    Baris DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 Dalam hal ini, ID database 5 pubs, ID file 1 adalah file data utama, halaman 104 adalah halaman milik tabel judul, dan slot 3 menunjukkan posisi baris di halaman.
    Mengompilasi DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 Dalam hal ini, ID database 5 pubs, ID file 1 adalah file data utama, halaman 104 adalah halaman milik tabel judul, dan slot 3 menunjukkan posisi baris di halaman.
    • sys.dm_tran_active_transactions DMV sys.dm_tran_active_transactions berisi data tentang transaksi terbuka yang dapat digabungkan ke DMV lain untuk gambaran lengkap transaksi menunggu penerapan atau putar kembali. Gunakan kueri berikut untuk menampilkan informasi tentang transaksi terbuka, yang digabungkan dengan DMV lain termasuk sys.dm_tran_session_transactions. Pertimbangkan status transaksi saat ini, transaction_begin_time, dan data situasional lainnya untuk mengevaluasi apakah itu bisa menjadi sumber pemblokiran.
    SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

    • Kolom lainnya

      Kolom lainnya di ys.dm_exec_sessions dan sys.dm_exec_request dapat memberikan wawasan tentang akar masalah juga. Kegunaannya bervariasi tergantung pada keadaan masalah. Misalnya, Anda dapat menentukan apakah masalah hanya terjadi dari klien tertentu (nama host), pada pustaka jaringan tertentu (net_library), ketika batch terakhir yang diajukan oleh SPID adalah last_request_start_time di sys.dm_exec_sessions, berapa lama permintaan telah berjalan menggunakan start_time di sys.dm_exec_requests, dan sebagainya.

Skenario pemblokiran umum

Tabel di bawah ini memetakan gejala umum untuk kemungkinan penyebabnya.

Kolom Waittype, Open_Tran, dan Status merujuk ke informasi yang dikembalikan oleh sys.dm_exec_request. Kolom lain mungkin dikembalikan oleh sys.dm_exec_sessions. Kolom "Selesai?" menunjukkan apakah pemblokiran diselesaikan sendiri atau tidak, atau apakah sesi harus dimatikan melalui perintah KILL. Untuk informasi selengkapnya, lihat KILL.

Skenario Waittype Open_Tran Kedudukan Terselesaikan? Gejala Lain
1 TIDAK NULL >=0 dapat dijalankan Ya, saat pertanyaan selesai. Di kolom sys.dm_exec_sessions, reads, cpu_time, dan/atau memory_usage meningkat seiring waktu. Durasi kueri menjadi lama saat selesai.
2 NULL >0 tidur Tidak, tapi SPID bisa dihentikan. Sinyal peringatan mungkin terlihat dalam sesi Kejadian yang Diperluas untuk SPID ini, menunjukkan batas waktu kueri habis atau pembatalan telah terjadi.
3 NULL >=0 dapat dijalankan Tidak. Tidak akan diselesaikan hingga klien mengambil semua baris atau menutup koneksi. SPID dapat dibunuh, tetapi dapat memakan waktu hingga 30 detik. Jika open_transaction_count = 0, dan SPID memegang kunci sementara tingkat isolasi transaksi default (READ COMMITTED), ini kemungkinan penyebabnya.
4 Bervariasi >=0 siap dijalankan Tidak. Tidak teratasi sampai klien membatalkan kueri atau menutup koneksi. SPID dapat dibunuh, tetapi mungkin memakan waktu hingga 30 detik. Kolom hostname di sys.dm_exec_sessions untuk SPID di ujung rantai pemblokiran sama dengan salah satu dari SPID yang diblokirnya.
5 NULL >0 pemulihan Ya. Sinyal perhatian mungkin terlihat dalam sesi Extended Events untuk SPID ini, menunjukkan bahwa waktu untuk kueri telah habis atau kueri telah dibatalkan, atau mungkin pernyataan rollback telah dikeluarkan.
6 NULL >0 tidur Akhirnya. Ketika Windows menentukan sesi tidak lagi aktif, koneksi Azure SQL Database rusak. Nilai last_request_start_time dalam sys.dm_exec_sessions jauh lebih awal dari waktu saat ini.

Skenario pemblokiran terperinci

  1. Pemblokiran yang disebabkan oleh kueri yang berjalan normal dengan waktu eksekusi yang lama

    Resolusi: Solusi untuk jenis masalah pemblokiran ini adalah mencari cara untuk mengoptimalkan kueri. Sebenarnya, kelas masalah pemblokiran ini hanya bisa menjadi masalah performa, dan mengharuskan Anda untuk mengejarnya seperti itu. Untuk informasi tentang pemecahan masalah kueri tertentu yang berjalan lambat, lihat Cara memecahkan masalah kueri yang berjalan lambat di SQL Server. Untuk informasi selengkapnya, lihat Memantau dan Menyelaraskan Performa.

    Laporan-laporan dari Penyimpanan Kueri di SQL Server Management Studio juga merupakan alat yang sangat direkomendasikan dan berharga untuk mengidentifikasi kueri-kueri yang paling mahal, serta rencana-rencana eksekusi yang kurang optimal. Silakan tinjau juga Query Performance Insight.

    Jika kueri hanya melakukan operasi SELECT, pertimbangkan menjalankan pernyataan tersebut di bawah isolasi snapshot jika diaktifkan di database Anda, khususnya apabila RCSI telah dinonaktifkan. Seperti saat RCSI diaktifkan, kueri yang membaca data tidak memerlukan kunci bersama (S) pada tingkat isolasi snapshot. Selain itu, isolasi snapshot memberikan konsistensi tingkat transaksi untuk semua pernyataan-pernyataan dalam transaksi yang terdiri dari beberapa pernyataan secara eksplisit. Isolasi rekam jepret mungkin sudah diaktifkan di database Anda. Isolasi snapshot mungkin juga digunakan dengan kueri yang melakukan modifikasi, tetapi Anda harus menangani konflik pembaruan.

    Jika Anda memiliki kueri jangka panjang yang memblokir pengguna lain dan tidak dapat dioptimalkan, pertimbangkan untuk memindahkannya dari lingkungan OLTP ke sistem pelaporan khusus, replika database baca-saja yang sinkron.

  2. Pemblokiran yang disebabkan oleh SPID yang sedang tidak aktif yang memiliki transaksi yang belum dikomit

    Jenis pemblokiran ini sering kali dapat diidentifikasi oleh SPID yang sedang tidur atau menunggu perintah, tetapi tingkat berlapis transaksinya (@@TRANCOUNT, open_transaction_count dari sys.dm_exec_requests) lebih besar dari nol. Ini dapat terjadi jika aplikasi mengalami batas waktu kueri, atau mengeluarkan perintah pembatalan tanpa mengeluarkan jumlah pernyataan ROLLBACK dan/atau COMMIT yang diperlukan. Saat SPID menerima batas waktu kueri atau pembatalan, SPID mengakhiri kueri dan batch saat ini, tetapi tidak secara otomatis mengembalikan atau melakukan transaksi. Aplikasi ini bertanggung jawab untuk hal ini, karena Azure SQL Database tidak dapat mengasumsikan bahwa seluruh transaksi harus digulung kembali karena satu kueri dibatalkan. Batas waktu atau pembatalan kueri muncul sebagai sinyal perhatian (ATTENTION) untuk SPID dalam sesi Extended Event.

    Untuk menunjukkan transaksi eksplisit yang belum dilakukan, ajukan kueri berikut:

    CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

    Kemudian, jalankan kueri ini di jendela yang sama:

    SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

    Output kueri kedua menunjukkan bahwa tingkat bersarang transaksi adalah satu. Semua kunci yang diperoleh dalam transaksi masih akan ditahan sampai transaksi dikonfirmasi atau digulirkan kembali. Jika aplikasi secara eksplisit membuka dan melakukan transaksi, komunikasi atau kesalahan lain dapat meninggalkan sesi dan transaksinya dalam keadaan terbuka.

    Gunakan skrip sebelumnya dalam artikel ini berdasarkan sys.dm_tran_active_transactions untuk mengidentifikasi transaksi yang saat ini belum dikomit di seluruh instans.

    Resolusi:

    • Selain itu, kelas masalah pemblokiran ini juga dapat menjadi masalah kinerja, dan mengharuskan Anda untuk menanganinya sebagai masalah kinerja. Jika waktu eksekusi kueri dapat berkurang, waktu tunggu kueri habis atau pembatalan tidak akan terjadi. Penting bahwa aplikasi dapat menangani skenario batas waktu atau pembatalan jika muncul, tetapi Anda mungkin juga mendapat manfaat dari memeriksa performa kueri.

    • Aplikasi harus secara tepat mengelola tingkat penumpukan transaksi, atau dapat menyebabkan masalah pemblokiran setelah pembatalan kueri dengan demikian. Anggap:

      • Dalam penanganan kesalahan pada aplikasi klien, jalankan IF @@TRANCOUNT > 0 ROLLBACK TRAN setelah terjadi kesalahan apa pun, bahkan jika aplikasi klien tidak merasa ada transaksi yang terbuka. Memeriksa transaksi terbuka diperlukan karena prosedur tersimpan yang dipanggil selama batch mungkin memulai transaksi tanpa sepengetahuan aplikasi klien. Kondisi tertentu, seperti membatalkan kueri, mencegah prosedur dijalankan melewati pernyataan saat ini, jadi bahkan jika prosedur memiliki logika untuk memeriksa IF @@ERROR <> 0 dan membatalkan transaksi, kode putar kembali ini tidak dijalankan dalam kasus seperti itu.
      • Jika pengumpulan koneksi digunakan dalam aplikasi yang membuka koneksi dan menjalankan beberapa kueri sebelum merilis koneksi kembali ke kumpulan, seperti aplikasi berbasis Web, menonaktifkan kumpulan koneksi untuk sementara mungkin membantu meringankan masalah sampai aplikasi klien dimodifikasi untuk menangani kesalahan dengan tepat. Dengan menonaktifkan penggunaan koneksi, melepaskan koneksi menyebabkan pemutusan fisik dari koneksi Azure SQL Database, yang mengakibatkan server membatalkan transaksi yang masih terbuka.
      • Gunakan SET XACT_ABORT ON untuk koneksi, atau dalam prosedur tersimpan yang telah memulai transaksi dan tidak sedang membersihkan setelah terjadi kesalahan. Jika terjadi kesalahan run-time, pengaturan ini membatalkan transaksi terbuka dan mengembalikan kontrol kepada klien. Untuk informasi selengkapnya, tinjau SETXACT_ABORT.

    Catatan

    Koneksi tidak diatur ulang sampai digunakan kembali dari kumpulan koneksi, jadi ada kemungkinan bahwa pengguna dapat membuka transaksi dan kemudian melepaskan koneksi ke kumpulan koneksi, tetapi mungkin tidak digunakan kembali selama beberapa detik, selama waktu itu transaksi akan tetap terbuka. Jika koneksi tidak digunakan kembali, transaksi dibatalkan ketika waktu koneksi habis dan dihapus dari kumpulan koneksi. Dengan demikian, optimal bagi aplikasi klien untuk membatalkan transaksi dalam penghandel kesalahan transaksi atau gunakan SET XACT_ABORT ON untuk menghindari potensi penundaan ini.

    Perhatian

    Setelah SET XACT_ABORT ONpernyataan T-SQL setelah pernyataan yang menimbulkan kesalahan tidak dijalankan. Ini dapat mempengaruhi alur yang dimaksudkan dari kode yang ada.

  3. Pemblokiran karena SPID di mana aplikasi klien terkaitnya tidak mengambil semua baris hasil sampai selesai.

    Setelah mengirim kueri ke server, semua aplikasi harus segera mengambil semua baris hasil hingga selesai. Jika aplikasi tidak mengambil semua baris hasil, kunci bisa tertinggal di tabel, sehingga memblokir pengguna lain. Jika Anda menggunakan aplikasi yang secara transparan mengirimkan pernyataan SQL ke server, aplikasi harus mengambil semua baris hasil. Jika tidak (dan jika tidak dapat dikonfigurasi untuk melakukannya), Anda mungkin tidak dapat mengatasi masalah pemblokiran. Untuk menghindari masalah, Anda dapat membatasi aplikasi yang berperilaku buruk pada pelaporan atau database pendukung keputusan, terpisah dari database OLTP utama.

    Dampak dari skenario ini berkurang ketika read committed snapshot diaktifkan di database, yang merupakan konfigurasi default di Azure SQL Database. Pelajari selengkapnya di bagian Memahami pemblokiran artikel ini.

    Catatan

    Lihat panduan untuk mencoba kembali logika untuk aplikasi yang menyambungkan ke Azure SQL Database.

    Resolusi: Aplikasi harus ditulis ulang untuk mengambil semua baris hasil hingga selesai. Ini tidak mengesampingkan penggunaan OFFSET dan FETCH dalam klausul ORDER BY dari kueri untuk melakukan paginasi di sisi server.

  4. Pemblokiran yang disebabkan oleh sesi dalam keadaan pemutaran kembali

    Kueri modifikasi data yang dihentikan, atau dibatalkan di luar transaksi yang didefinisikan oleh pengguna, akan dibatalkan. Ini juga dapat terjadi sebagai dampak dari terputusnya sesi jaringan klien, atau ketika permintaan yang dipilih sebagai korban kebuntuan. Ini sering dapat diidentifikasi dengan mengamati output sys.dm_exec_requests, yang mungkin menunjukkan perintah ROLLBACK, dan kolom percent_complete mungkin menunjukkan kemajuan.

    Berkat pemulihan database Dipercepat diperkenalkan pada tahun 2019, pemutaran kembali yang panjang harus jarang terjadi.

    Resolusi: Tunggu sampai SPID selesai membatalkan perubahan yang dibuat.

    Untuk menghindari situasi ini, jangan lakukan operasi penulisan batch besar atau operasi pembuatan atau pemeliharaan indeks selama jam sibuk pada sistem OLTP. Jika memungkinkan, lakukan operasi tersebut selama periode aktivitas rendah.

  5. Pemblokiran yang disebabkan oleh koneksi yatim

    Jika aplikasi klien menjebak kesalahan atau stasiun kerja klien dimulai ulang, sesi jaringan ke server mungkin tidak segera dibatalkan dalam beberapa kondisi. Dari perspektif Azure SQL Database, klien tampaknya masih ada, dan kunci apa pun yang diperoleh mungkin masih dipertahankan. Untuk informasi selengkapnya, lihat Cara memecahkan masalah koneksi tanpa sumber di SQL Server.

    Resolusi: Jika aplikasi klien terputus tanpa membersihkan sumbernya dengan tepat, Anda dapat mengakhiri SPID dengan menggunakan perintah KILL. Perintah KILL mengambil nilai SPID sebagai input. Misalnya, untuk menghentikan SPID 99, keluarkan perintah berikut:

    KILL 99

Konten terkait