Menganalisis dan mencegah kebuntuan di Azure SQL Database dan database SQL di Fabric

Berlaku untuk:Azure SQL DatabaseSQL Database di Fabric

Artikel ini mengajarkan cara mengidentifikasi kebuntuan, menggunakan grafik kebuntuan, dan Penyimpanan Kueri untuk mengidentifikasi kueri dalam kebuntuan, serta merencanakan dan menguji perubahan untuk mencegah kebuntuan terjadi kembali. Artikel ini berlaku untuk Azure SQL Database dan database SQL di Fabric, yang berbagi banyak fitur Azure SQL Database.

Artikel ini berfokus untuk mengidentifikasi dan menganalisis kebuntuan karena perebutan kunci. Pelajari selengkapnya tentang jenis kebuntuan lainnya di sumber daya yang dapat mengakibatkan kebuntuan.

Bagaimana kebuntuan terjadi

Setiap database baru di Azure SQL Database memiliki pengaturan database read committed snapshot isolation (RCSI) diaktifkan secara default. Pemblokiran antara sesi membaca data dan sesi menulis data diminimalkan di bawah 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 dapat memblokir satu dengan yang lain.

  • Kueri mungkin dijalankan pada tingkat isolasi yang meningkatkan pemblokiran. Tingkat isolasi dapat ditentukan melalui metode pustaka klien, petunjuk kueri, atau SET TRANSACTION ISOLATION LEVEL di Transact-SQL.

  • RCSI mungkin dinonaktifkan, sehingga database menggunakan kunci bersama (S) untuk melindungi pernyataan SELECT yang dijalankan di bawah tingkat isolasi read committed. Ini bisa meningkatkan pemblokiran dan kebuntuan.

Contoh kebuntuan

Kebuntuan terjadi ketika dua atau lebih tugas secara permanen memblokir satu sama lain karena setiap tugas memiliki kunci pada sumber daya yang coba dikunci oleh tugas lain. Kebuntuan juga disebut ketergantungan siklik: dalam kasus kebuntuan dua tugas, transaksi A bergantung pada transaksi B, dan transaksi B menyelesaikan lingkaran dengan bergantung pada transaksi A.

Contohnya:

  1. Sesi A memulai transaksi eksplisit dan menjalankan pernyataan pembaruan yang memperoleh kunci pembaruan (U) pada satu baris pada tabel SalesLT.Product yang dikonversi menjadi kunci eksklusif (X).

  2. Sesi B menjalankan pernyataan pembaruan yang mengubah tabel SalesLT.ProductDescription. Pernyataan pembaruan bergabung ke tabel SalesLT.Product untuk menemukan baris yang benar untuk diperbarui.

    • Sesi B memperoleh kunci pembaruan (U) pada 72 baris pada tabel SalesLT.ProductDescription.

    • Sesi B memerlukan penguncian bersama pada baris di tabel SalesLT.Product, termasuk baris yang dikunci oleh Sesi A. Sesi B diblokir pada SalesLT.Product.

  3. Sesi A melanjutkan transaksinya, dan sekarang menjalankan pembaruan terhadap tabel SalesLT.ProductDescription. Sesi A diblokir oleh Sesi B pada SalesLT.ProductDescription.

Diagram memperlihatkan dua sesi dalam kebuntuan. Setiap sesi memiliki sumber daya yang dibutuhkan proses lain untuk melanjutkan.

Semua transaksi dalam kebuntuan menunggu tanpa batas waktu kecuali salah satu transaksi yang berpartisipasi digulung balik, misalnya, karena sesinya dihentikan.

Pemantau kebuntuan mesin database secara berkala memeriksa tugas-tugas yang berada dalam kebuntuan. Jika monitor kebuntuan mendeteksi ketergantungan siklikal, monitor memilih salah satu tugas sebagai korban dan mengakhiri transaksinya dengan kesalahan 1205: Transaction (Process ID <N>) was deadlocked on lock resources with another process and is chosen as the deadlock victim. Rerun the transaction. Memecahkan kebuntuan dengan cara ini memungkinkan tugas atau tugas lain yang terlibat dalam kebuntuan untuk menyelesaikan transaksi mereka.

Catatan

Pelajari selengkapnya tentang kriteria untuk memilih korban kebuntuan di bagian Daftar proses kebuntuan dari artikel ini.

Diagram kebuntuan antara dua sesi. Satu sesi dipilih sebagai korban kebuntuan.

Aplikasi dengan transaksi yang dipilih sebagai korban kebuntuan harus mencoba kembali transaksi, yang biasanya selesai setelah transaksi atau transaksi lain yang terlibat dalam kebuntuan selesai.

Ini adalah praktik terbaik untuk memperkenalkan penundaan singkat dan acak sebelum mencoba kembali untuk menghindari menemui kebuntuan yang sama lagi. Pelajari selengkapnya tentang cara merancang logika percobaan kembali untuk kesalahan sementara.

Tingkat isolasi default di Azure SQL Database

Database yang baru di Azure SQL Database memungkinkan snapshot komit membaca (RCSI) secara default. RCSI mengubah perilaku tingkat isolasi read committed untuk menggunakan versi baris guna memberikan konsistensi pada tingkat pernyataan tanpa menggunakan kunci bersama (S) untuk pernyataan SELECT.

Dengan RCSI yang diaktifkan:

  • Pernyataan membaca data tidak memblokir pernyataan yang memodifikasi data.
  • Pernyataan yang memodifikasi data tidak memblokir pernyataan membaca data.

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, kolom is_read_committed_snapshot_on mengembalikan nilai 1. Jika isolasi rekam jepret diaktifkan, kolom snapshot_isolation_state_desc mengembalikan nilai ON.

Jika RCSI dinonaktifkan untuk database di Azure SQL Database, selidiki mengapa RCSI dinonaktifkan sebelum mengaktifkannya kembali. Kode aplikasi mungkin mengharapkan bahwa kueri yang membaca data akan diblokir oleh kueri yang menulis data, yang mengakibatkan hasil yang salah dari kondisi balapan ketika RCSI diaktifkan.

Menginterpretasikan peristiwa kebuntuan

Peristiwa kebuntuan muncul setelah manajer kebuntuan di Azure SQL Database mendeteksi kebuntuan dan memilih transaksi sebagai korban. Dengan kata lain, jika Anda menyiapkan pemberitahuan untuk kebuntuan, notifikasi akan dikirim setelah kebuntuan individual diselesaikan. Tidak ada tindakan pengguna yang perlu diambil untuk kebuntuan itu. Aplikasi harus ditulis untuk menyertakan logika coba lagi sehingga secara otomatis dilanjutkan setelah menerima kesalahan 1205: Transaction (Process ID <N>) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Namun, berguna untuk menyiapkan pemberitahuan karena kebuntuan dapat terulang kembali. Pemberitahuan kebuntuan memungkinkan Anda menyelidiki apakah pola kebuntuan berulang terjadi di database Anda, dalam hal ini Anda dapat memilih untuk mengambil tindakan untuk mencegah kebuntuan terjadi kembali. Pelajari selengkapnya tentang peringatan di bagian Memantau dan memperingatkan tentang kebuntuan dari artikel ini.

Metode teratas untuk mencegah kebuntuan

Pendekatan risiko terendah untuk mencegah terjadinya kembali kebuntuan umumnya adalah menyetel indeks non-kluster untuk mengoptimalkan kueri yang terlibat dalam kebuntuan.

  • Risiko rendah untuk pendekatan ini karena menyetel indeks non-kluster tidak memerlukan perubahan pada kode kueri itu sendiri, mengurangi risiko kesalahan pengguna saat menulis kembali T-SQL yang menyebabkan data yang salah diberikan ke pengguna.

  • Penyetelan indeks non-kluster yang efektif membantu kueri menemukan data untuk dibaca dan diubah dengan lebih efisien. Dengan mengurangi jumlah data yang perlu diakses oleh kueri, kemungkinan pemblokiran berkurang dan kebuntuan sering kali dapat dicegah.

Dalam beberapa kasus, membuat atau menyetel indeks berkluster dapat mengurangi pemblokiran dan kebuntuan. Karena indeks berkluster termasuk dalam semua definisi indeks non-kluster, membuat atau mengubah indeks berkluster dapat menjadi operasi yang banyak menggunakan IO dan memakan waktu lama pada tabel yang lebih besar dengan indeks non-kluster yang ada. Pelajari selengkapnya tentang Panduan desain indeks berkluster.

Ketika penyetelan indeks tidak berhasil mencegah kebuntuan, metode lain tersedia:

  • Jika deadlock hanya terjadi ketika rencana tertentu dipilih untuk salah satu kueri yang terlibat dalam deadlock, dengan menggunakan Query Store untuk memaksakan rencana kueri tertentu mungkin dapat mencegah deadlock terulang kembali.

  • Menulis kembali T-SQL untuk satu atau lebih transaksi yang terlibat dalam kebuntuan juga dapat membantu mencegah kebuntuan. Memecah transaksi eksplisit menjadi transaksi yang lebih kecil membutuhkan pengodean dan pengujian yang cermat untuk memastikan validitas data saat terjadi perubahan bersamaan.

Pelajari selengkapnya tentang masing-masing pendekatan ini di bagian Mencegah kebuntuan agar tidak terulang kembali di artikel ini.

Memantau dan memperingatkan kebuntuan

Dalam artikel ini, kami menggunakan database sampel AdventureWorksLT untuk menyiapkan pemberitahuan kebuntuan, menyebabkan contoh kebuntuan, menganalisis grafik kebuntuan untuk contoh kebuntuan, dan menguji perubahan untuk mencegah kebuntuan muncul kembali.

Kami menggunakan klien SQL Server Management Studio (SSMS) dalam artikel ini, karena berisi fungsionalitas untuk menampilkan grafik kebuntuan dalam mode visual interaktif. Anda dapat menggunakan klien lain seperti ekstensi MSSQL untuk Visual Studio Code, sqlcmd, atau alat kueri Transact-SQL favorit Anda untuk mengikuti contoh, tetapi Anda mungkin hanya dapat melihat grafik kebuntuan sebagai XML.

Membuat database AdventureWorksLT

Untuk mengikuti contoh, buat database baru di Azure SQL Database dan pilih data Sampel sebagai Sumber data.

Untuk instruksi terperinci tentang cara membuat AdventureWorksLT dengan portal Microsoft Azure, Azure CLI, atau PowerShell, pilih pendekatan pilihan Anda di Mulai Cepat: Membuat database tunggal di Azure SQL Database.

Menyiapkan peringatan kebuntuan di portal Microsoft Azure

Untuk menyiapkan pemberitahuan peristiwa kebuntuan, ikuti langkah-langkah dalam artikel Membuat pemberitahuan untuk Azure SQL Database dan Azure Synapse Analytics menggunakan portal Microsoft Azure.

Pilih nama sinyal Kebuntuan untuk peringatan. Konfigurasikan Grup tindakan untuk memberi tahu Anda menggunakan metode pilihan Anda, seperti jenis tindakan Email/SMS/Push/Suara.

Mengumpulkan grafik kebuntuan di Azure SQL Database dengan Extended Events

Grafik kebuntuan adalah sumber informasi yang kaya mengenai proses dan kunci yang terlibat dalam kebuntuan. Untuk mengumpulkan grafik kebuntuan dengan Extended Events (XEvents) di Azure SQL Database dan database SQL di Fabric, ambil peristiwa tersebut sqlserver.database_xml_deadlock_report .

Anda dapat mengumpulkan grafik kebuntuan dengan XEvents menggunakan target ring buffer atau target file peristiwa. Pertimbangan untuk memilih jenis target yang sesuai diringkas dalam tabel berikut:

Pendekatan Keuntungan Pertimbangan Skenario penggunaan
Sasaran buffer cincin - Pengaturan sederhana hanya dengan Transact-SQL. - Data peristiwa dibersihkan ketika sesi XEvents dihentikan karena alasan apa pun, sekiranya database menjadi offline atau terjadi failover.

- Sumber daya database digunakan untuk mempertahankan data di buffer ring dan untuk mengakses data sesi.		 - Kumpulkan data jejak sampel untuk pengujian dan pembelajaran.

- Buat untuk kebutuhan jangka pendek jika Anda tidak dapat menyiapkan sesi dengan menggunakan target berkas acara secara langsung.

- Gunakan sebagai landing pad untuk data jejak, ketika Anda telah menyiapkan proses otomatis untuk menyimpan data jejak ke dalam tabel.
Target file peristiwa - Mempertahankan data peristiwa ke blob di Azure Storage sehingga data tersedia bahkan setelah sesi dihentikan.

- File peristiwa dapat diunduh dari portal Microsoft Azure atau Azure Storage Explorer dan dianalisis secara lokal, yang tidak memerlukan sumber daya database untuk mengkueri data sesi.		 - Penyiapan lebih kompleks dan memerlukan konfigurasi kontainer Azure Storage dan kredensial cakupan database. - Penggunaan umum saat Anda ingin data peristiwa bertahan bahkan setelah sesi peristiwa berhenti.

- Anda ingin menjalankan jejak yang menghasilkan data peristiwa dalam jumlah yang lebih besar daripada yang ingin Anda pertahankan dalam memori.

Pilih jenis target yang ingin Anda gunakan:

Target ring buffer praktis dan mudah disiapkan, tetapi memiliki kapasitas terbatas, yang dapat menyebabkan peristiwa lama hilang. Ring buffer tidak menyimpan event ke penyimpanan dan target ring buffer dibersihkan saat sesi XEvents berakhir. Ini berarti bahwa setiap XEvent yang dikumpulkan tidak tersedia saat mesin database dimulai ulang karena alasan apa pun, seperti failover. Target buffer melingkar paling cocok untuk belajar dan kebutuhan jangka pendek jika Anda tidak memiliki kemampuan untuk langsung menyiapkan target file peristiwa untuk sesi XEvents.

Kode sampel ini membuat sesi XEvents yang menangkap grafik kemacetan di memori menggunakan target ring buffer. Memori maksimum yang diizinkan untuk target buffer cincin adalah 4 MB, dan sesi akan berjalan secara otomatis ketika database menjadi aktif kembali, misalnya setelah failover.

Untuk membuat dan kemudian memulai sesi XEvents untuk peristiwa sqlserver.database_xml_deadlock_report yang menulis ke target ring buffer, hubungkan ke database Anda dan jalankan perintah Transact-SQL berikut:

CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = START;
GO

Menyebabkan kebuntuan

Karena penguncian yang dioptimalkan selalu diaktifkan di Azure SQL Database dan database SQL di Fabric, kemungkinan terjadinya kebuntuan menjadi lebih kecil. Untuk informasi lebih lanjut, dan untuk contoh kebuntuan yang dapat terjadi dengan penguncian teroptimasi, lihat Penguncian teroptimasi dan kebuntuan.

Melihat grafik kebuntuan dari sesi XEvents

Jika Anda menyiapkan sesi XEvents untuk mengumpulkan kebuntuan dan kebuntuan terjadi setelah sesi dimulai, Anda dapat melihat tampilan grafik interaktif dari graf kebuntuan dan XML dari graf kebuntuan.

Metode yang berbeda tersedia untuk mendapatkan informasi kebuntuan untuk target ring buffer dan target file peristiwa. Pilih target yang Anda gunakan untuk sesi XEvents Anda:

Jika Anda menyiapkan penulisan sesi XEvents ke ring buffer, Anda dapat mengkueri informasi kebuntuan dengan T-SQL berikut. Sebelum menjalankan kueri, ganti nilai @tracename dengan nama sesi XEvents Anda.

DECLARE @tracename AS sysname = N'deadlocks';

WITH ring_buffer
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER JOIN sys.dm_xe_database_session_targets AS t
             ON CAST (t.event_session_address AS BINARY (8)) = CAST (s.address AS BINARY (8))
    WHERE s.name = @tracename
          AND t.target_name = N'ring_buffer'),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM ring_buffer
CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Melihat dan menyimpan grafik kebuntuan dalam XML

Melihat grafik kebuntuan dalam format XML memungkinkan Anda menyalin inputbuffer dari pernyataan T-SQL yang terlibat dalam kebuntuan. Anda juga dapat menganalisis kebuntuan dalam format berbasis teks.

Jika Anda telah menggunakan kueri T-SQL guna menampilkan informasi grafik kebuntuan, untuk melihat XML grafik kebuntuan, pilih nilai di kolom deadlock_xml dari baris mana pun untuk membuka XML grafik kebuntuan di jendela baru di SQL Server Management Studio.

XML untuk contoh grafik kebuntuan ini adalah:

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Silver';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Untuk menyimpan grafik kebuntuan sebagai file XML:

  1. Pilih File dan Simpan Sebagai....
  2. Biarkan nilai Simpan sebagai jenis tetap pada default File XML (*.xml)
  3. Atur Nama file ke nama pilihan Anda.
  4. Pilih Simpan.

Simpan grafik kebuntuan sebagai file XDL yang dapat ditampilkan secara interaktif di SQL Server Management Studio

Melihat representasi interaktif dari grafik kebuntuan dapat berguna untuk mendapatkan gambaran umum singkat tentang proses dan sumber daya yang terlibat dalam kebuntuan, dan dengan cepat mengidentifikasi korban kebuntuan.

Untuk menyimpan grafik kebuntuan sebagai file yang dapat ditampilkan secara grafis oleh SQL Server Management Studio:

  1. Pilih nilai di kolom deadlock_xml dari baris mana pun untuk membuka XML grafik kebuntuan di jendela baru di SQL Server Management Studio.

  2. Pilih File dan Simpan Sebagai....

  3. Atur Simpan sebagai tipe ke Semua File.

  4. Atur Nama file ke nama pilihan Anda, dengan ekstensi yang ditetapkan ke .xdl.

  5. Pilih Simpan.

    Cuplikan layar di SSMS mengenai penyimpanan sebuah file XML grafik kebuntuan ke dalam file dengan ekstensi xsd.

  6. Tutup file dengan memilih X pada tab di bagian atas jendela, atau dengan memilih File, lalu Tutup.

  7. Buka kembali file di SQL Server Management Studio dengan memilih File, lalu Buka, lalu File. Pilih file yang Anda simpan dengan ekstensi .xdl.

    Grafik kebuntuan sekarang ditampilkan di SSMS dengan representasi visual proses dan sumber daya yang terlibat dalam kebuntuan.

    Cuplikan layar file XDL yang dibuka di SSMS (SQL Server Management Studio). Grafik kebuntuan ditampilkan secara grafis, dengan proses yang ditunjukkan oleh oval dan sumber daya yang terkunci ditunjukkan sebagai persegi panjang.

Menganalisis kebuntuan untuk Azure SQL Database

Grafik kebuntuan biasanya memiliki tiga node:

  • Daftar korban. Pengidentifikasi proses yang menjadi korban deadlock.

  • Daftar proses. Informasi tentang semua proses yang terlibat dalam kebuntuan. Grafik kebuntuan menggunakan istilah 'proses' untuk mewakili sesi yang menjalankan transaksi.

  • Daftar sumber daya. Informasi tentang sumber daya yang terlibat dalam kebuntuan.

Saat menganalisis kebuntuan, berguna untuk menelusuri simpul ini.

Daftar korban deadlock

Daftar korban kebuntuan menunjukkan proses yang dipilih sebagai korban kebuntuan. Dalam representasi visual dari grafik kebuntuan, proses diwakili oleh oval. Proses korban kebuntuan memiliki tanda "X" yang digambar di atas oval.

Cuplikan layar tampilan visual kebuntuan. Oval yang mewakili proses yang dipilih sebagai korban memiliki tanda X di atasnya.

Dalam tampilan XML grafik kebuntuan, node victim-list memberikan ID untuk proses yang menjadi korban kebuntuan.

Dalam contoh kebuntuan kami, ID proses korban adalah process24756e75088. Kita dapat menggunakan ID ini saat memeriksa node daftar proses dan daftar sumber daya untuk mempelajari selengkapnya tentang proses korban dan sumber daya yang dikunci atau diminta untuk dikunci.

Daftar proses kebuntuan

Daftar proses kebuntuan adalah sumber informasi yang kaya tentang transaksi yang terlibat dalam kebuntuan.

Representasi grafik kebuntuan hanya menunjukkan sebagian informasi yang terkandung dalam XML grafik kebuntuan. Oval dalam grafik kebuntuan mewakili proses, dan menunjukkan informasi yang mencakup:

  • ID Sesi, juga dikenal sebagai SPID.

  • Prioritas kebuntuan sesi. Jika dua sesi memiliki prioritas kebuntuan yang berbeda, sesi dengan prioritas lebih rendah dipilih sebagai korban kebuntuan. Dalam contoh ini, kedua sesi memiliki prioritas kebuntuan yang sama.

  • Jumlah log transaksi yang digunakan oleh sesi dalam satuan byte. Jika kedua sesi memiliki prioritas kebuntuan yang sama, monitor kebuntuan memilih sesi yang lebih tidak mahal untuk dikembalikan sebagai korban kebuntuan. Biaya ditentukan dengan membandingkan jumlah byte log yang ditulis ke titik tersebut dalam setiap transaksi.

    Dalam contoh kebuntuan kami, session_id 89 telah menggunakan jumlah log transaksi yang lebih rendah, dan dipilih sebagai korban kebuntuan.

Selain itu, Anda dapat melihat buffer input untuk pernyataan terakhir yang dijalankan di setiap sesi sebelum kebuntuan dengan mengarahkan mouse ke masing-masing proses. Penyangga masukan muncul di petunjuk alat.

Cuplikan layar grafik kebuntuan yang ditampilkan secara visual di SSMS. Dua oval mewakili proses. Buffer input untuk satu proses ditampilkan.

Informasi tambahan tersedia untuk proses dalam tampilan XML dari grafik kebuntuan, termasuk:

  • Mengidentifikasi informasi untuk sesi, seperti nama klien, nama host, dan ID masuk.

  • Hash rencana pencarian untuk perintah terakhir yang dijalankan oleh setiap sesi sebelum kebuntuan. Hash rencana kueri berguna untuk mengambil lebih banyak informasi tentang kueri dari Query Store.

Dalam contoh kebuntuan kita:

  • Kita dapat melihat bahwa kedua sesi dijalankan menggunakan klien SSMS di bawah login chrisqpublic.

  • Hash rencana kueri dari pernyataan terakhir yang dijalankan sebelum kebuntuan oleh korban, adalah 0x02b0f58d7730f798. Kita dapat melihat teks pernyataan ini di buffer input.

  • Hash rencana permintaan, dari pernyataan terakhir yang dijalankan oleh sesi lain dalam deadlock yang kami alami, juga 0x02b0f58d7730f798. Kita dapat melihat teks pernyataan ini di buffer input. Dalam kasus ini, kedua kueri memiliki hash rencana kueri yang sama karena kueri tersebut identik, kecuali untuk nilai literal yang digunakan sebagai predikat kesetaraan.

Kami akan menggunakan nilai-nilai ini nanti dalam artikel berikut untuk mencari informasi tambahan di Query Store.

Keterbatasan penyangga masukan dalam daftar proses deadlock

Ada beberapa batasan yang harus diperhatikan terkait informasi mengenai buffer input dalam daftar proses yang mengalami kebuntuan.

Teks kueri mungkin terpotong pada penyangga input. Buffer input terbatas pada 4.000 karakter pertama dari pernyataan yang dijalankan.

Selain itu, beberapa pernyataan yang terlibat dalam kebuntuan mungkin tidak disertakan dalam grafik kebuntuan. Dalam contoh kita, Sesi A menjalankan dua pernyataan pembaruan dalam satu transaksi. Hanya pernyataan pembaruan kedua, pembaruan yang menyebabkan kebuntuan, yang termasuk dalam grafik kebuntuan. Pernyataan pembaruan pertama yang dijalankan oleh Sesi A memainkan peran dalam kebuntuan dengan memblokir Sesi B. Buffer input, query_hash, dan informasi terkait untuk pernyataan pertama yang dijalankan oleh Sesi A tidak disertakan dalam grafik kebuntuan.

Untuk mengidentifikasi pelaksanaan Transact-SQL sepenuhnya dalam sebuah transaksi multi-pernyataan yang terlibat dalam kebuntuan, Anda perlu menemukan informasi spesifik dalam prosedur tersimpan atau kode aplikasi yang menjalankan kueri tersebut, atau melakukan pelacakan menggunakan Extended Events untuk menangkap semua pernyataan yang dijalankan oleh sesi-sesi yang terlibat dalam kebuntuan saat hal itu terjadi. Jika pernyataan yang terlibat dalam kebuntuan dipotong dan hanya sebagian Transact-SQL yang muncul di buffer input, Anda dapat menemukan Transact-SQL untuk pernyataan di Query Store dengan Rencana Eksekusi.

Daftar sumber daya kebuntuan

Daftar sumber daya kebuntuan menunjukkan sumber daya kunci mana yang dimiliki dan ditunggu oleh proses dalam kebuntuan.

Sumber daya diwakili oleh persegi dalam representasi visual kebuntuan:

Cuplikan layar grafik kebuntuan, yang ditampilkan secara visual di SQL Server Management Studio. Persegi menunjukkan sumber daya yang terlibat dalam kebuntuan.

Catatan

Nama database direpresentasikan sebagai GUID (uniqueidentifier) dalam grafik kebuntuan untuk database di Azure SQL Database dan database SQL di Fabric. Ini merupakan physical_database_name untuk database yang tercantum dalam tampilan manajemen dinamis sys.databases dan sys.dm_user_db_resource_governance.

Dalam contoh kebuntuan ini:

  • Korban kebuntuan, yang kita sebut sebagai Sesi A:

    • Memiliki kunci eksklusif (X) pada kunci di indeks PK_Product_ProductID pada tabel SalesLT.Product.

    • Meminta kunci pembaruan (U) pada indeks PK_ProductDescription_ProductDescriptionID di tabel SalesLT.ProductDescription.

  • Proses lainnya, yang kita sebut sebagai Sesi B:

    • Memiliki kunci pembaruan (U) pada kunci di indeks PK_ProductDescription_ProductDescriptionID pada tabel SalesLT.ProductDescription.

    • Meminta kunci berbagi (S) pada sebuah kunci di indeks PK_ProductDescription_ProductDescriptionID pada tabel SalesLT.ProductDescription.

Kita dapat melihat informasi yang sama di XML grafik kebuntuan dalam node daftar sumber daya.

Menemukan rencana eksekusi kueri di Query Store

Seringkali berguna untuk memeriksa rencana eksekusi kueri untuk pernyataan yang terlibat dalam kebuntuan. Rencana pelaksanaan ini seringkali bisa ditemukan di Query Store dengan menggunakan hash rencana kueri dari tampilan XML daftar proses grafik kebuntuan.

Kueri T-SQL ini mencari rencana kueri yang cocok dengan hash rencana kueri yang kita temukan untuk contoh kebuntuan kita. Hubungkan ke database pengguna di Azure SQL Database untuk menjalankan kueri.

DECLARE @query_plan_hash AS BINARY (8) = 0x02b0f58d7730f798;

SELECT qrsi.end_time AS interval_end_time,
       qs.query_id,
       qp.plan_id,
       qt.query_sql_text,
       TRY_CAST (qp.query_plan AS XML) AS query_plan,
       qrs.count_executions
FROM sys.query_store_query AS qs
     INNER JOIN sys.query_store_query_text AS qt
         ON qs.query_text_id = qt.query_text_id
     INNER JOIN sys.query_store_plan AS qp
         ON qs.query_id = qp.query_id
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_runtime_stats_interval AS qrsi
         ON qrs.runtime_stats_interval_id = qrsi.runtime_stats_interval_id
WHERE query_plan_hash = @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

Anda mungkin tidak bisa mendapatkan rencana eksekusi kueri dari Penyimpanan Kueri, bergantung pada pengaturan CLEANUP_POLICY Penyimpanan Kueri atau QUERY_CAPTURE_MODE Anda. Dalam kasus ini, Anda sering kali dapat memperoleh informasi yang diperlukan dengan menampilkan perkiraan rencana eksekusi untuk kueri.

Mencari pola yang meningkatkan pemblokiran

Saat memeriksa rencana eksekusi kueri yang terlibat dalam kebuntuan, perhatikan pola yang dapat berkontribusi terhadap pemblokiran dan kebuntuan.

  • Pemindaian tabel atau indeks. Saat kueri yang mengubah data dijalankan dengan RCSI, pemilihan baris untuk diperbarui dilakukan menggunakan pemindaian blokir di mana kunci pembaruan (U) diterapkan pada baris data saat nilai data dibaca. Jika baris data tidak memenuhi kriteria pembaruan, kunci pembaruan dirilis dan baris berikutnya dikunci dan dipindai.

    Menyetel indeks untuk membantu kueri perubahan menemukan baris dengan lebih efisien mengurangi jumlah kunci pembaruan yang dikeluarkan. Hal ini mengurangi kemungkinan pemblokiran dan kebuntuan.

  • Tampilan terindeks yang merujuk lebih dari satu tabel. Saat Anda mengubah tabel yang direferensikan dalam tampilan terindeks, mesin database juga harus mempertahankan tampilan terindeks. Hal ini membutuhkan mengeluarkan lebih banyak kunci dan dapat menyebabkan peningkatan pemblokiran dan kebuntuan. Tampilan terindeks juga dapat menyebabkan operasi pembaruan dijalankan secara internal di bawah tingkat isolasi read committed.

  • Perubahan kolom yang direferensikan dalam batasan kunci asing. Saat Anda mengubah kolom dalam tabel yang dirujuk dalam batasan FOREIGN KEY, mesin database harus mencari baris terkait dalam tabel referensi. Versi baris tidak dapat digunakan untuk pembacaan data ini. Jika pembaruan atau penghapusan berjenjang diaktifkan, tingkat isolasi dapat ditingkatkan ke serializabel sepanjang pernyataan untuk melindungi dari penyisipan phantom.

  • Petunjuk kunci. Cari petunjuk tabel yang menentukan tingkat isolasi yang membutuhkan lebih banyak kunci. Petunjuk ini termasuk HOLDLOCK (yang setara dengan dapat diserialisasi), SERIALIZABLE, READCOMMITTEDLOCK (yang menonaktifkan RCSI), dan REPEATABLEREAD. Selain itu, petunjuk seperti PAGLOCK, TABLOCK, UPDLOCK, dan XLOCK dapat meningkatkan risiko pemblokiran dan kebuntuan.

    Jika petunjuk ini ada, selidiki mengapa petunjuk tersebut diterapkan. Petunjuk ini dapat mencegah kondisi balapan dan memastikan validitas data. Mungkin untuk membiarkan petunjuk ini tetap ada dan mencegah kebuntuan di masa mendatang dengan menggunakan metode alternatif di bagian Mencegah kebuntuan terulang kembali artikel ini jika perlu.

    Catatan

    Pelajari selengkapnya tentang perilaku saat mengubah data menggunakan penerapan versi baris di Panduan penguncian transaksi dan penerapan versi baris.

Saat memeriksa kode lengkap untuk transaksi, baik dalam rencana eksekusi atau dalam kode kueri aplikasi, cari pola bermasalah tambahan:

  • Interaksi pengguna dalam transaksi. Interaksi pengguna di dalam transaksi multi pernyataan eksplisit secara signifikan meningkatkan durasi transaksi. Hal ini mengakibatkan transaksi ini lebih mungkin menjadi tumpang tindih dan muncul pemblokiran serta kebuntuan.

    Demikian pula, menahan transaksi terbuka dan mengkueri database atau sistem yang tidak terkait di tengah transaksi secara signifikan meningkatkan kemungkinan pemblokiran dan kebuntuan.

  • Transaksi mengakses objek dalam urutan yang berbeda. Kebuntuan kecil kemungkinannya terjadi ketika transaksi multi pernyataan eksplisit secara bersamaan mengikuti pola yang sama dan mengakses objek dalam urutan yang sama.

Mencegah agar kebuntuan tidak terulang kembali

Ada beberapa teknik yang tersedia untuk mencegah kebuntuan terulang kembali, seperti penyetelan indeks secara optimal, memaksa rencana dengan menggunakan Query Store, dan memodifikasi kueri Transact-SQL.

  • Tinjau indeks berkluster pada tabel. Sebagian besar tabel mendapat keuntungan dari indeks berkluster, tetapi sering kali, tabel diterapkan sebagai heap secara tidak sengaja.

    Salah satu cara untuk memeriksa indeks berkluster adalah dengan menggunakan prosedur tersimpan sistem sp_helpindex. Misalnya, kita dapat melihat ringkasan indeks pada tabel SalesLT.Product dengan menjalankan pernyataan berikut:

    EXECUTE sp_helpindex 'SalesLT.Product';
GO

    Tinjau kolom index_description. Tabel hanya dapat memiliki satu indeks berkluster. Jika indeks berkluster diimplementasikan untuk tabel, index_description berisi kata clustered.

    Jika tidak ada indeks berkluster, tabel adalah timbunan. Dalam kasus ini, tinjau apakah tabel sengaja dibuat sebagai timbunan untuk memecahkan masalah performa tertentu. Pertimbangkan untuk menerapkan indeks berkluster berdasarkan panduan desain indeks berkluster.

    Dalam beberapa kasus, membuat atau menyetel indeks berkluster dapat mengurangi atau menghilangkan pemblokiran dalam kebuntuan. Dalam kasus lain, Anda dapat menggunakan teknik tambahan seperti yang lain dalam daftar ini.

  • Membuat atau memodifikasi indeks nonclustered. Menyetel indeks non-kluster dapat membantu kueri perubahan Anda menemukan data untuk diperbarui lebih cepat, yang mengurangi jumlah kunci pembaruan yang diperlukan.

    Dalam contoh kebuntuan kita, rencana eksekusi kueri ditemukan di Query Store yang berisi pemindaian indeks berkluster terhadap indeks PK_Product_ProductID. Grafik kebuntuan menunjukkan bahwa kunci berbagi (S) yang menunggu pada indeks ini menjadi salah satu komponen dalam kebuntuan.

    Cuplikan layar rencana eksekusi kueri. Pemindaian indeks berkluster sedang dilakukan terhadap indeks PK_Product_ProductID pada tabel Produk.

    Pemindaian indeks ini dilakukan karena kueri pembaruan kita perlu mengubah tampilan terindeks bernama vProductAndDescription. Seperti disebutkan dalam bagian Cari pola yang meningkatkan pemblokiran artikel ini, indeks tampilan yang mengacu pada beberapa tabel dapat meningkatkan pemblokiran dan kemungkinan terjadinya kebuntuan.

    Jika kita membuat indeks non-kluster berikut di database AdventureWorksLT yang "mencakup" kolom dari SalesLT.Product yang dirujuk oleh tampilan terindeks, hal ini membantu kueri menemukan baris dengan lebih efisien:

    CREATE INDEX IX_Product_ProductID_Name_ProductModelID
    ON SalesLT.Product(ProductID, Name, ProductModelID);
GO

    Setelah membuat indeks ini, kebuntuan tidak lagi terjadi.

    Ketika kebuntuan melibatkan modifikasi pada kolom yang dirujuk dalam batasan kunci asing, pastikan bahwa indeks pada tabel referensi FOREIGN KEY mendukung menemukan baris terkait secara efisien.

    Sementara indeks dapat secara dramatis meningkatkan performa kueri dalam beberapa kasus, indeks juga memiliki biaya operasional dan manajemen. Tinjau panduan desain indeks umum untuk membantu menilai keuntungan indeks sebelum membuat indeks, terutama indeks lebar dan indeks pada tabel besar.

  • Beri nilai tampilan terindeks. Opsi lain untuk mencegah contoh kebuntuan terulang kembali adalah dengan menghapus tampilan terindeks SalesLT.vProductAndDescription. Jika tampilan terindeks tersebut tidak digunakan, ini mengurangi overhead mempertahankan tampilan terindeks dari waktu ke waktu.

  • Gunakan isolasi Snapshot. Dalam beberapa kasus, mengatur level isolasi transaksi ke snapshot untuk satu atau beberapa transaksi yang terlibat dalam kebuntuan dapat mencegah pemblokiran dan kebuntuan berulang.

    Teknik ini kemungkinan besar berhasil ketika digunakan pada pernyataan SELECT ketika rekam jepret terkomit dinonaktifkan dalam database. Saat rekam jepret komit baca dinonaktifkan, kueri SELECT yang menggunakan tingkat isolasi komit baca memerlukan kunci bersama (S). Menggunakan isolasi snapshot pada transaksi ini menghilangkan kebutuhan akan kunci bersama, yang dapat mencegah pemblokiran dan kebuntuan.

    Dalam database di mana isolasi snapshot baca diaktifkan, kueri SELECT tidak memerlukan kunci bersama (S), sehingga kemungkinan terjadinya deadlock lebih tinggi di antara transaksi yang memodifikasi data. Dalam kasus ketika deadlock terjadi antara beberapa transaksi yang memodifikasi data, isolasi snapshot dapat mengakibatkan konflik pembaruan alih-alih deadlock. Hal ini juga mengharuskan salah satu transaksi untuk mengulangi operasinya.

  • Paksakan sebuah rencana dengan Query Store. Anda mungkin menemukan bahwa salah satu kueri dalam kebuntuan memiliki beberapa rencana eksekusi, dan kebuntuan hanya terjadi ketika rencana tertentu digunakan. Anda dapat mencegah agar kebuntuan tidak terulang kembali dengan memaksa rencana di Query Store.

  • Ubah T-SQL. Anda mungkin perlu memodifikasi Transact-SQL untuk mencegah kebuntuan terulang kembali. Mengubah T-SQL harus dilakukan dengan hati-hati dan perubahan harus diuji secara ketat untuk memastikan bahwa data benar ketika perubahan dijalankan secara bersamaan. Saat menulis kembali T-SQL, pertimbangkan:

    • Mengurutkan pernyataan dalam transaksi agar mengakses objek dalam urutan yang sama.

    • Memecah transaksi menjadi transaksi yang lebih kecil jika memungkinkan.

    • Menggunakan petunjuk kueri, jika perlu, untuk mengoptimalkan performa. Anda dapat menerapkan petunjuk tanpa mengubah kode aplikasi menggunakan Query Store.

Temukan lebih banyak cara untuk meminimalkan kebuntuan di panduan Kebuntuan.

Catatan

Dalam beberapa kasus, Anda dapat menyesuaikan prioritas kebuntuan dari satu atau beberapa sesi yang terlibat dalam kebuntuan jika penting bagi salah satu sesi untuk berhasil diselesaikan tanpa harus mencoba kembali, atau ketika salah satu kueri yang terlibat dalam kebuntuan tidak penting dan harus selalu dipilih sebagai korban. Meskipun ini tidak mencegah kebuntuan terulang kembali, ini dapat mengurangi efek kebuntuan di masa depan.

Menghentikan sesi XEvents

Anda dapat meninggalkan sesi XEvents yang mengumpulkan informasi kebuntuan yang berjalan pada database penting untuk jangka waktu yang lama. Jika Anda menggunakan target file peristiwa, hal ini dapat menghasilkan file yang besar jika terjadi beberapa deadlock. Anda dapat menghapus file blob dari Azure Storage untuk pelacakan aktif, kecuali untuk file yang saat ini sedang ditulis.

Saat Anda ingin menghapus sesi XEvents, cara menghapus sesi menggunakan Transact-SQL sama, apapun jenis target yang dipilih.

Untuk menghapus sesi XEvents, jalankan T-SQL berikut. Sebelum menjalankan kode, ganti nama sesi dengan nilai yang sesuai.

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Gunakan Azure Storage Explorer

Penjelajah Penyimpanan Azure adalah aplikasi mandiri yang menyederhanakan bekerja dengan target file kejadian yang disimpan dalam blob di Azure Storage. Anda dapat menggunakan Storage Explorer untuk:

Unduh Azure Storage Explorer.

Konten terkait

  • Last updated on