Bagikan melalui

Mendiagnosis dan mengatasi pertikaian spinlock di SQL Server

Artikel ini menyediakan informasi mendalam tentang cara mengidentifikasi dan mengatasi masalah yang terkait dengan kontensi spinlock di aplikasi SQL Server pada sistem dengan tingkat persaingan tinggi.

Nota

Rekomendasi dan praktik terbaik yang didokumentasikan di sini didasarkan pada pengalaman dunia nyata selama pengembangan dan penyebaran sistem OLTP dunia nyata. Ini awalnya diterbitkan oleh tim Microsoft SQL Server Customer Advisory Team (SQLCAT).

Latar Belakang

Di masa lalu, komputer Windows Server dengan kelas komoditas biasanya hanya menggunakan satu atau dua chip mikroprosesor/CPU, dan CPU dirancang hanya dengan satu prosesor atau "core". Peningkatan kapasitas pemrosesan komputer telah dicapai dengan menggunakan CPU yang lebih cepat, yang dimungkinkan sebagian besar melalui kemajuan kepadatan transistor. Mengikuti "Hukum Moore", kepadatan transistor atau jumlah transistor yang dapat ditempatkan di sirkuit terintegrasi secara konsisten meningkat dua kali lipat setiap dua tahun sejak pengembangan CPU chip tunggal tujuan umum pertama pada tahun 1971. Dalam beberapa tahun terakhir, pendekatan tradisional untuk meningkatkan kapasitas pemrosesan komputer dengan CPU yang lebih cepat telah bertambah dengan membangun komputer dengan beberapa CPU. Pada penulisan ini, arsitektur CPU Intel Nehalem mengakomodasi hingga delapan core per CPU, yang ketika digunakan dalam delapan sistem soket kemudian dapat digandakan menjadi 128 prosesor logis dengan menggunakan teknologi multithreading (SMT) simultan. Pada CPU Intel, SMT disebut Hyper-Threading. Ketika jumlah prosesor logis pada komputer yang kompatibel x86 meningkat, masalah terkait konkurensi meningkat saat prosesor logis bersaing untuk sumber daya. Panduan ini menjelaskan cara mengidentifikasi dan mengatasi masalah ketidakcocokan sumber daya tertentu yang diamati saat menjalankan aplikasi SQL Server pada sistem konkurensi tinggi dengan beberapa beban kerja.

Di bagian ini, kami menganalisis pelajaran yang dipelajari oleh tim SQLCAT dari mendiagnosis dan menyelesaikan masalah perselisihan spinlock. Kontensi spinlock adalah salah satu jenis masalah keserentakan yang diamati dalam beban kerja pelanggan sebenarnya pada sistem skala tinggi.

Gejala dan penyebab pertikaian spinlock

Bagian ini menjelaskan cara mendiagnosis masalah dengan pertikaian spinlock, yang merugikan performa aplikasi OLTP di SQL Server. Diagnosis dan pemecahan masalah spinlock adalah subjek lanjutan yang memerlukan pengetahuan alat penelusuran kesalahan dan internal Windows.

Spinlock adalah primitif sinkronisasi ringan yang digunakan untuk melindungi akses ke struktur data. Spinlock tidak unik untuk SQL Server. Sistem operasi menggunakannya ketika akses ke struktur data tertentu hanya diperlukan untuk waktu yang singkat. Ketika sebuah utas yang mencoba memperoleh spinlock tidak dapat memperoleh akses, utas tersebut akan menjalankan proses pengecekan dalam loop secara berkala untuk menentukan apakah sumber daya tersedia, alih-alih segera menyerah. Setelah beberapa periode waktu, utas yang menunggu pada spinlock akan mengalah sebelum dapat memperoleh sumber daya. Menyerahkan memungkinkan utas lain yang berjalan pada CPU yang sama untuk dieksekusi. Perilaku ini dikenal sebagai penundaan dan akan dibahas lebih mendalam nanti dalam artikel ini.

SQL Server menggunakan spinlock untuk melindungi akses ke beberapa struktur data internalnya. Spinlock digunakan dalam mesin untuk membuat serialisasi akses ke struktur data tertentu dengan cara yang mirip dengan kait. Perbedaan utama antara latch dan spinlock adalah bahwa spinlock melakukan looping untuk memeriksa ketersediaan struktur data, sementara thread yang mencoba mengakses struktur yang dilindungi oleh latch akan segera berhenti mencoba jika sumber daya tidak tersedia. Menghasilkan memerlukan peralihan konteks utas dari CPU sehingga utas lain dapat dijalankan. Ini adalah operasi yang relatif mahal, dan untuk sumber daya yang ditahan dalam durasi singkat secara keseluruhan akan lebih efisien untuk memungkinkan utas menjalankan perulangan secara berkala sambil memeriksa ketersediaan sumber daya.

Penyesuaian internal pada Mesin Database yang diperkenalkan di SQL Server 2022 (16.x) membuat spinlock lebih efisien.

Gejala

Pada sistem dengan tingkat konkurensi tinggi yang sibuk, adalah hal biasa untuk melihat kontensi aktif pada struktur yang sering diakses dan dilindungi oleh spinlock. Penggunaan ini hanya dianggap bermasalah ketika kontensi menyebabkan overhead CPU yang signifikan. Statistik spinlock diungkapkan oleh Tampilan Manajemen Dinamis (DMV) di SQL Server. Misalnya, kueri ini menghasilkan output berikut:

Nota

Detail selengkapnya tentang menginterpretasikan informasi yang dikembalikan oleh DMV ini dibahas nanti dalam artikel ini.

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

Cuplikan layar memperlihatkan output 'sys.dm_os_spinlock_stats'.

Statistik yang diekspos oleh kueri ini dijelaskan sebagai berikut:

kolom Deskripsi
Tabrakan Nilai ini bertambah setiap kali utas diblokir agar tidak mengakses sumber daya yang dilindungi oleh spinlock.
Spins Nilai ini bertambah setiap kali utas menjalankan perulangan ketika menunggu spinlock menjadi tersedia. Ini adalah ukuran jumlah pekerjaan yang dilakukan utas saat mencoba memperoleh sumber daya.
Spins_per_collision Rasio putaran per tabrakan.
Waktu tidur Terkait dengan peristiwa penundaan; namun tidak relevan dengan teknik yang dijelaskan dalam artikel ini.
Backoff Terjadi ketika utas "berputar" yang mencoba mengakses sumber daya yang dipegang telah menentukan bahwa perlu mengizinkan utas lain pada CPU yang sama untuk dijalankan.

Untuk tujuan diskusi ini, statistik yang menjadi perhatian khusus adalah jumlah tabrakan, perputaran, dan peristiwa penundaan yang terjadi dalam periode tertentu ketika sistem berada di bawah beban berat. Ketika utas mencoba mengakses sumber daya yang dilindungi oleh spinlock, tabrakan terjadi. Ketika tabrakan terjadi, jumlah tabrakan akan bertambah dan utas akan mulai berputar-putar dalam loop dan secara berkala memeriksa apakah sumber daya tersedia. Setiap kali utas berputar (perulangan) perhitungan putaran bertambah.

Putaran per tabrakan adalah pengukuran jumlah putaran yang terjadi ketika spinlock dipegang oleh sebuah utas, dan memberi tahu Anda berapa banyak putaran yang terjadi saat utas-utas tersebut memegang spinlock. Misalnya, putaran kecil per tabrakan dan jumlah tabrakannya tinggi berarti hanya sedikit putaran yang terjadi di bawah spinlock dan ada banyak utas yang bersaing memperebutkan spinlock tersebut. Sejumlah besar putaran berarti waktu yang dihabiskan untuk berputar dalam kode spinlock cukup lama (yaitu, kode sedang memproses sejumlah besar entri dalam sebuah bucket hash). Ketika pertikaian meningkat (sehingga meningkatkan jumlah tabrakan), jumlah putaran juga meningkat.

Backoff dapat dipikirkan dengan cara yang mirip dengan spin. Secara desain, untuk menghindari limbah CPU yang berlebihan, spinlock tidak terus berputar tanpa batas waktu sampai mereka dapat mengakses sumber daya yang disimpan. Untuk memastikan spinlock tidak terlalu banyak menggunakan sumber daya CPU, spinlock mengurangi aktivitas, atau berhenti berputar dan memasuki mode istirahat. Spinlock mundur tanpa mempedulikan apakah mereka sudah mendapatkan kepemilikan sumber daya target. Ini dilakukan untuk memungkinkan utas lain dapat dijadwalkan pada CPU dengan harapan bahwa ini memungkinkan pekerjaan yang lebih produktif terlaksana. Perilaku default untuk mesin adalah memutar interval waktu konstan terlebih dahulu sebelum melakukan backoff. Mencoba mendapatkan spinlock mengharuskan status cache dipertahankan dalam keadaan bersamaan, yang merupakan operasi intensif CPU relatif terhadap beban berputar CPU. Oleh karena itu, upaya untuk mendapatkan spinlock dilakukan dengan hemat dan tidak dilakukan setiap kali utas berputar. Pada SQL Server, jenis-jenis spinlock tertentu (misalnya, LOCK_HASH) ditingkatkan dengan menggunakan interval yang meningkat secara eksponensial antara upaya mendapatkan spinlock (hingga batas tertentu), yang sering mengurangi dampak pada kinerja CPU.

Diagram berikut memberikan tampilan konseptual algoritma spinlock:

Diagram memperlihatkan tampilan konseptual algoritma spinlock.

Skenario umum

Ketidakcocokan spinlock dapat terjadi karena sejumlah alasan yang mungkin tidak terkait dengan keputusan desain database. Karena spinlock mengendalikan akses ke struktur data internal, kontensi spinlock tidak dimanifestasikan dengan cara yang sama seperti kontensi kait buffer, yang langsung dipengaruhi oleh pilihan desain skema dan pola akses data.

Gejala yang terutama terkait dengan pertikaian spinlock adalah konsumsi CPU yang tinggi sebagai akibat dari banyaknya putaran dan banyak utas yang mencoba memperoleh spinlock yang sama. Secara umum, ini telah diamati pada sistem dengan 24 dan lebih inti CPU, dan paling umum pada sistem dengan lebih dari 32 inti CPU. Seperti yang dinyatakan sebelumnya, beberapa tingkat ketidakcocokan pada spinlock normal untuk sistem OLTP konkurensi tinggi dengan beban yang signifikan dan seringkali ada sejumlah besar putaran (miliaran/triliun) yang dilaporkan dari sys.dm_os_spinlock_stats DMV pada sistem yang telah berjalan untuk waktu yang lama. Sekali lagi, mengamati sejumlah besar spin untuk jenis spinlock tertentu tidak cukup informasi untuk menentukan bahwa ada dampak negatif terhadap performa beban kerja.

Kombinasi beberapa gejala berikut mungkin menunjukkan kontensi spinlock. Jika semua kondisi ini benar, lakukan penyelidikan lebih lanjut tentang kemungkinan masalah pertikaian spinlock.

  • Sejumlah besar spin dan backoff diamati untuk jenis spinlock tertentu.

  • Sistem mengalami penggunaan CPU yang tinggi atau lonjakan konsumsi CPU. Dalam skenario CPU yang berat, Anda melihat sinyal menunggu tinggi pada SOS_SCHEDULER_YIELD (dilaporkan oleh DMV sys.dm_os_wait_stats).

  • Sistem mengalami konkurensi tinggi.

  • Penggunaan CPU dan tingkat putaran meningkat secara tidak proporsional terhadap throughput.

Salah satu fenomena umum yang mudah didiagnosis adalah perbedaan yang signifikan dalam penggunaan throughput dan CPU. Banyak beban kerja OLTP memiliki hubungan antara (throughput / jumlah pengguna pada sistem) dan konsumsi CPU. Putaran tinggi yang diamati bersama dengan divergensi konsumsi dan throughput CPU yang signifikan dapat menjadi indikasi pertikaian spinlock yang memperkenalkan overhead CPU. Hal penting yang perlu diperhatikan di sini adalah bahwa juga umum untuk melihat jenis divergensi ini pada sistem ketika kueri tertentu menjadi lebih mahal dari waktu ke waktu. Misalnya, kueri yang dikeluarkan terhadap himpunan data yang melakukan pembacaan yang lebih logis dari waktu ke waktu dapat mengakibatkan gejala serupa.

Penting

Sangat penting untuk menyingkirkan penyebab lain yang lebih umum dari CPU tinggi saat memecahkan masalah seperti ini.

Bahkan jika setiap kondisi sebelumnya benar, masih mungkin bahwa akar penyebab konsumsi CPU tinggi terletak di tempat lain. Bahkan, dalam sebagian besar kasus peningkatan CPU disebabkan oleh alasan selain ketidakcocokan spinlock.

Beberapa penyebab yang lebih umum untuk peningkatan konsumsi CPU meliputi:

  • Kueri yang menjadi lebih mahal seiring waktu karena pertumbuhan data yang mendasarinya mengakibatkan kebutuhan untuk melakukan pembacaan logis tambahan dari data yang terdapat dalam memori.
  • Perubahan dalam rencana kueri yang mengakibatkan eksekusi suboptimal.

Contoh

Dalam contoh berikut, ada hubungan yang hampir linier antara konsumsi CPU dan throughput sebagaimana diukur oleh transaksi per detik. Normal untuk melihat beberapa perbedaan di sini karena overhead terjadi seiring dengan bertambahnya beban kerja. Seperti yang diilustrasikan di sini, divergensi ini menjadi signifikan. Ada juga penurunan throughput yang tajam setelah konsumsi CPU mencapai 100%.

Cuplikan layar memperlihatkan penurunan CPU di monitor performa.

Ketika mengukur jumlah putaran pada interval 3 menit kita dapat melihat peningkatan yang lebih eksponensial daripada peningkatan linier dalam putaran, yang menunjukkan bahwa pertikaian spinlock mungkin bermasalah.

Cuplikan layar memperlihatkan bagan putaran selama 3 menit interval.

Seperti yang dinyatakan sebelumnya, spinlock paling umum digunakan pada sistem dengan konkurensi tinggi yang berada di bawah beban berat.

Beberapa skenario yang rentan terhadap masalah ini meliputi:

  • Masalah resolusi nama yang disebabkan oleh kegagalan untuk mengkualiikasi sepenuhnya nama objek. Untuk informasi selengkapnya, lihat Deskripsi pemblokiran SQL Server yang disebabkan oleh kunci kompilasi. Masalah khusus ini dijelaskan secara lebih rinci dalam artikel ini.

  • Persaingan untuk slot hash di pengelola kunci untuk beban kerja yang sering mengakses kunci yang sama (seperti penguncian bersama pada baris yang sering dibaca). Jenis pertikaian ini muncul sebagai LOCK_HASH jenis spinlock. Dalam satu kasus tertentu, kami menemukan bahwa masalah ini muncul sebagai akibat dari pola akses yang salah dimodelkan di lingkungan pengujian. Dalam lingkungan ini, lebih dari jumlah utas yang diharapkan terus-menerus mengakses baris yang sama persis karena parameter pengujian yang salah dikonfigurasi.

  • Tingkat transaksi DTC yang tinggi terjadi saat terdapat tingkat latensi tinggi di antara koordinator transaksi MSDTC. Masalah spesifik ini didokumentasikan secara rinci dalam entri blog SQLCAT Menyelesaikan Tunggu Terkait DTC dan Penyetelan Skalabilitas DTC.

Mendiagnosis pertikaian spinlock

Bagian ini menyediakan informasi untuk mendiagnosis pertikaian spinlock SQL Server. Alat utama yang digunakan untuk mendiagnosis pertikaian spinlock adalah:

Alat Gunakan
Monitor Kinerja Cari kondisi CPU yang tinggi atau perbedaan antara laju pemrosesan data dan konsumsi CPU.
Statistik spinlock Jalankan kueri pada sys.dm_os_spinlock_stats DMV untuk mencari sejumlah besar peristiwa spin dan backoff selama jangka waktu tertentu.
Statistik Waktu Tunggu Dimulai dengan Pratinjau SQL Server 2025 (17.x), lakukan kueri pada sys.dm_os_wait_stats dan sys.dm_exec_session_wait_stats DMVs menggunakan SPINLOCK_EXT tipe tunggu. Memerlukan bendera pelacakan 8134. Untuk informasi selengkapnya, lihat SPINLOCK_EXT.
Peristiwa Diperluas SQL Server Digunakan untuk melacak tumpukan panggilan untuk spinlock yang mengalami jumlah putaran yang tinggi.
Pembuangan memori Dalam beberapa kasus, pembuangan memori dari proses SQL Server dan alat debug Windows. Secara umum, tingkat analisis ini dilakukan ketika tim Dukungan Microsoft terlibat.

Proses teknis umum untuk mendiagnosis pertikaian Spinlock SQL Server adalah:

  1. Langkah 1: Tentukan bahwa ada ketidakcocokan yang mungkin terkait dengan spinlock.

  2. Langkah 2: Ambil statistik dari sys.dm_os_spinlock_stats untuk menemukan jenis spinlock yang mengalami ketidakcocokan terbanyak.

  3. Langkah 3: Dapatkan simbol debug untuk sqlservr.exe (sqlservr.pdb) dan tempatkan simbol dalam direktori yang sama dengan file .exe layanan SQL Server (sqlservr.exe) untuk instans SQL Server.\ Untuk melihat tumpukan panggilan untuk peristiwa backoff, Anda harus memiliki simbol untuk versi SQL Server tertentu yang Anda jalankan. Simbol untuk SQL Server tersedia di Microsoft Symbol Server. Untuk informasi selengkapnya tentang cara mengunduh simbol dari Microsoft Symbol Server, lihat Penelusuran kesalahan dengan simbol.

  4. Langkah 4: Gunakan SQL Server Extended Events untuk melacak kejadian penundaan terkait jenis spinlock yang diminati. Peristiwa yang akan diambil adalah spinlock_backoff dan spinlock_backoff_warning.

Extended Events menyediakan kemampuan untuk melacak peristiwa backoff dan menangkap tumpukan panggilan untuk operasi yang paling sering mencoba mendapatkan spinlock. Dengan menganalisis tumpukan panggilan, dimungkinkan untuk menentukan jenis operasi yang berkontribusi pada persaingan untuk spinlock tertentu.

Penjelasan langkah-langkah diagnostik

Panduan berikut menunjukkan cara menggunakan alat dan teknik untuk mendiagnosis masalah pertikaian spinlock dalam skenario dunia nyata. Panduan ini didasarkan pada interaksi pelanggan yang menjalankan pengujian tolok ukur untuk mensimulasikan sekitar 6.500 pengguna secara bersamaan pada server dengan 8 soket dan 64 inti fisik serta memori 1 TB.

Gejala

Lonjakan berkala dalam CPU diamati, yang mendorong pemanfaatan CPU ke hampir 100%. Divergensi antara konsumsi throughput dan CPU diamati menjelang masalah. Pada saat lonjakan CPU besar terjadi, terbentuk pola dari terjadinya banyak putaran selama waktu penggunaan CPU berat pada interval tertentu.

Ini adalah kasus ekstrem di mana perselisihan itu sedemikian rupa sehingga menciptakan kondisi konvoi spinlock. Situasi konvoi terjadi ketika utas tidak dapat lagi membuat kemajuan dalam melayani beban kerja, tetapi menghabiskan semua sumber daya pemrosesan untuk mencoba mendapatkan akses ke kunci. Catatan monitor kinerja menggambarkan divergensi ini antara throughput log transaksi dan konsumsi CPU dan pada akhirnya, lonjakan besar dalam penggunaan CPU.

Cuplikan layar memperlihatkan lonjakan CPU di monitor performa.

Setelah mengkueri sys.dm_os_spinlock_stats untuk menentukan adanya ketidakcocokan signifikan pada SOS_CACHESTORE, skrip peristiwa yang diperluas digunakan untuk mengukur jumlah peristiwa backoff untuk jenis spinlock yang menarik.

Nama Tabrakan Putaran Putaran setiap tabrakan Penundaan
SOS_CACHESTORE 14,752,117 942,869,471,526 63,914 67,900,620
SOS_SUSPEND_QUEUE 69,267,367 473,760,338,765 6,840 2,167,281
LOCK_HASH 5,765,761 260,885,816,584 45,247 3,739,208
MUTEX 2,802,773 9,767,503,682 3,485 350,997
SOS_SCHEDULER 1,207,007 3,692,845,572 3,060 109,746

Cara paling mudah untuk mengukur dampak spin adalah dengan melihat jumlah peristiwa backoff yang diekspos oleh sys.dm_os_spinlock_stats selama interval 1 menit yang sama untuk jenis spinlock dengan jumlah putaran tertinggi. Metode ini paling baik untuk mendeteksi persaingan yang signifikan karena menunjukkan kapan utas melewati batas putaran saat menunggu untuk mendapatkan spinlock. Skrip berikut mengilustrasikan teknik lanjutan yang menggunakan peristiwa yang diperluas untuk mengukur peristiwa backoff terkait dan mengidentifikasi jalur kode tertentu tempat pertikaian berada.

Untuk informasi selengkapnya tentang Acara yang Diperluas di SQL Server, lihat Gambaran umum Kejadian yang Diperluas.

Script

/*
This script is provided "AS IS" with no warranties, and confers no rights.

This script will monitor for backoff events over a given period of time and
capture the code paths (callstacks) for those.

--Find the spinlock types
select map_value, map_key, name from sys.dm_xe_map_values
where name = 'spinlock_types'
order by map_value asc

--Example: Get the type value for any given spinlock type
select map_value, map_key, name from sys.dm_xe_map_values
where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')

Examples:
61LOCK_HASH
144 SOS_CACHESTORE
08MUTEX

*/
--create the even session that will capture the callstacks to a bucketizer
--more information is available in this reference: http://msdn.microsoft.com/en-us/library/bb630354.aspx
CREATE EVENT SESSION spin_lock_backoff ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    OR TYPE = 144 --SOS_CACHESTORE
    OR TYPE = 8 --MUTEX
) ADD TARGET package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
);

--Ensure the session was created
SELECT * FROM sys.dm_xe_sessions
WHERE name = 'spin_lock_backoff';

--Run this section to measure the contention
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = START;

--wait to measure the number of backoffs over a 1 minute period
WAITFOR DELAY '00:01:00';

--To view the data
--1. Ensure the sqlservr.pdb is in the same directory as the sqlservr.exe
--2. Enable this trace flag to turn on symbol resolution
DBCC TRACEON (3656, -1);

--Get the callstacks from the bucketizer target
SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spin_lock_backoff';

--clean up the session
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = STOP;
DROP EVENT SESSION spin_lock_backoff ON SERVER;

Dengan menganalisis output, kita dapat melihat tumpukan panggilan untuk jalur kode yang paling umum untuk putaran SOS_CACHESTORE . Skrip dijalankan beberapa waktu yang berbeda selama waktu ketika pemanfaatan CPU tinggi untuk memeriksa konsistensi dalam tumpukan panggilan yang dikembalikan. Tumpukan panggilan dengan jumlah slot bucket tertinggi serupa dengan kedua output (35.668 dan 8.506). Tumpukan panggilan ini memiliki jumlah slot yang dua urutan besarnya lebih besar dari entri tertinggi berikutnya. Kondisi ini menunjukkan jalur kode yang menarik.

Nota

Tidak jarang melihat tumpukan panggilan yang dikembalikan oleh skrip sebelumnya. Ketika skrip berjalan selama 1 menit, kami mengamati bahwa tumpukan panggilan dengan jumlah > slot 1.000 bermasalah tetapi jumlah > slot 10.000 lebih mungkin bermasalah karena jumlah slot yang lebih tinggi.

Nota

Pemformatan output berikut telah dibersihkan untuk tujuan keterbacaan.

Output 1

<BucketizerTarget truncated="0" buckets="256">
<Slot count="35668" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid
      CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey
      CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
  </value>
</Slot>
<Slot count="752" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey             CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
  </value>
  </Slot>

Output 2

<BucketizerTarget truncated="0" buckets="256">
<Slot count="8506" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep+c7 [ @ 0+0x0 SpinlockBase::Backoff Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
</value>
 </Slot>
<Slot count="190" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
       SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
   </value>
 </Slot>

Dalam contoh sebelumnya, tumpukan yang paling menarik memiliki jumlah slot tertinggi (35.668 dan 8.506), yang, pada kenyataannya, memiliki jumlah slot yang lebih besar dari 1.000.

Sekarang pertanyaannya mungkin, "apa yang harus saya lakukan dengan informasi ini"? Secara umum, pengetahuan mendalam tentang mesin SQL Server diperlukan untuk menggunakan informasi callstack dan pada titik ini proses pemecahan masalah berpindah ke area abu-abu. Dalam kasus khusus ini, dengan melihat panggilan tumpukan, kita dapat melihat bahwa jalur kode di mana masalah terjadi terkait dengan keamanan dan pencarian metadata (Seperti yang terbukti oleh bingkai tumpukan berikut CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID).

Dalam isolasi, sulit untuk menggunakan informasi ini untuk menyelesaikan masalah tetapi memberi kami beberapa ide di mana harus memfokuskan pemecahan masalah tambahan untuk mengisolasi masalah lebih lanjut.

Karena masalah ini terlihat terkait dengan jalur kode yang melakukan pemeriksaan terkait keamanan, kami memutuskan untuk menjalankan pengujian di mana pengguna aplikasi yang terhubung ke database diberikan sysadmin hak istimewa. Meskipun teknik ini tidak pernah direkomendasikan di lingkungan produksi, di lingkungan pengujian kami, teknik ini terbukti menjadi langkah pemecahan masalah yang berguna. Ketika sesi dijalankan menggunakan akses hak istimewa yang lebih tinggi (sysadmin), lonjakan CPU yang terkait dengan perebutan menghilang.

Opsi dan solusi

Jelas, pemecahan masalah pertikaian spinlock dapat menjadi tugas yang tidak sepele. Tidak ada "satu pendekatan terbaik umum". Langkah pertama dalam pemecahan masalah dan penyelesaian masalah performa adalah mengidentifikasi akar penyebabnya. Menggunakan teknik dan alat yang dijelaskan dalam artikel ini adalah langkah pertama dalam melakukan analisis yang diperlukan untuk memahami titik pertikaian terkait spinlock.

Saat versi baru SQL Server dikembangkan, mesin terus meningkatkan skalabilitas dengan menerapkan kode yang lebih baik dioptimalkan untuk sistem konkurensi tinggi. SQL Server telah memperkenalkan banyak pengoptimalan untuk sistem dengan konkurensi tinggi, salah satunya adalah backoff eksponensial untuk titik kontensi yang paling umum. Ada peningkatan mulai SQL Server 2012 yang secara khusus meningkatkan area khusus ini dengan memanfaatkan algoritma backoff eksponensial untuk semua spinlock dalam mesin.

Saat merancang aplikasi kelas atas yang membutuhkan performa dan skala ekstrem, pertimbangkan cara menjaga jalur kode yang diperlukan dalam SQL Server sesingkat mungkin. Jalur kode yang lebih pendek berarti lebih sedikit pekerjaan yang dilakukan oleh mesin database dan secara alami akan menghindari titik pertikaian. Banyak praktik terbaik memiliki efek samping mengurangi jumlah pekerjaan yang diperlukan mesin, dan karenanya, menghasilkan pengoptimalan performa beban kerja.

Mengambil beberapa praktik terbaik dari sebelumnya dalam artikel ini sebagai contoh:

  • Nama yang Sepenuhnya Memenuhi Syarat: Nama yang sepenuhnya memenuhi syarat dari semua objek akan mengakibatkan penghapusan kebutuhan SQL Server untuk menjalankan jalur kode yang diperlukan untuk menyelesaikan nama. Kami telah mengamati titik pertikaian juga pada SOS_CACHESTORE jenis spinlock yang ditemui ketika tidak menggunakan nama yang sepenuhnya memenuhi syarat dalam panggilan ke prosedur tersimpan. Kegagalan untuk sepenuhnya memenuhi syarat nama-nama ini mengakibatkan kebutuhan SQL Server untuk mencari skema default untuk pengguna, yang menghasilkan jalur kode yang lebih lama yang diperlukan untuk menjalankan SQL.

  • Kueri Berparameter: Contoh lain adalah menggunakan kueri berparameter dan panggilan prosedur tersimpan untuk mengurangi pekerjaan yang diperlukan untuk menghasilkan rencana eksekusi. Ini lagi-lagi menghasilkan jalur kode yang lebih pendek untuk eksekusi.

  • LOCK_HASH Kontensi: Kontensi pada struktur kunci tertentu atau tabrakan wadah hash tidak dapat dihindari dalam beberapa kasus. Meskipun mesin SQL Server mempartisi sebagian besar struktur kunci, masih ada kalanya saat memperoleh kunci menghasilkan akses ke wadah hash yang sama. Misalnya, aplikasi mengakses baris yang sama oleh banyak utas secara bersamaan (yaitu, data referensi). Jenis masalah ini dapat didekati oleh teknik yang memperluas skala data referensi ini dalam skema database atau menggunakan kontrol konkurensi optimis dan penguncian yang dioptimalkan jika memungkinkan.

Garis pertahanan pertama dalam menyetel beban kerja SQL Server selalu merupakan praktik penyetelan standar (misalnya, pengindeksan, pengoptimalan kueri, pengoptimalan I/O, dll.). Namun, selain penyetelan standar yang akan dilakukan seseorang, praktik berikut yang mengurangi jumlah kode yang diperlukan untuk melakukan operasi adalah pendekatan penting. Bahkan ketika praktik terbaik diikuti, masih ada kemungkinan bahwa pertikaian spinlock mungkin terjadi pada sistem konkurensi tinggi yang sibuk. Penggunaan alat dan teknik dalam artikel ini dapat membantu mengisolasi atau mengesampingkan jenis masalah ini dan menentukan kapan perlu melibatkan sumber daya Microsoft yang tepat untuk membantu.

Lampiran: Mengotomatiskan pengambilan cadangan memori

Skrip peristiwa yang diperluas berikut terbukti berguna untuk mengotomatiskan pengumpulan cadangan memori ketika pertikaian spinlock menjadi signifikan. Dalam beberapa kasus, cadangan memori diperlukan untuk melakukan diagnosis lengkap masalah atau diminta oleh tim Microsoft untuk melakukan analisis mendalam.

Skrip SQL berikut dapat digunakan untuk mengotomatiskan proses pengambilan cadangan memori untuk membantu menganalisis ketidakcocokan spinlock:

/*
This script is provided "AS IS" with no warranties, and confers no rights.

Use:    This procedure will monitor for spinlocks with a high number of backoff events
        over a defined time period which would indicate that there is likely significant
        spin lock contention.

        Modify the variables noted below before running.

Requires:
        xp_cmdshell to be enabled
            sp_configure 'xp_cmd', 1
            go
            reconfigure
            go

*********************************************************************************************************/
USE tempdb;
GO

IF object_id('sp_xevent_dump_on_backoffs') IS NOT NULL
    DROP PROCEDURE sp_xevent_dump_on_backoffs;
GO

CREATE PROCEDURE sp_xevent_dump_on_backoffs (
    @sqldumper_path NVARCHAR(max) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"',
    @dump_threshold INT = 500, --capture mini dump when the slot count for the top bucket exceeds this
    @total_delay_time_seconds INT = 60, --poll for 60 seconds
    @PID INT = 0,
    @output_path NVARCHAR(MAX) = 'c:\',
    @dump_captured_flag INT = 0 OUTPUT
)
AS
/*
    --Find the spinlock types
    select map_value, map_key, name from sys.dm_xe_map_values
    where name = 'spinlock_types'
    order by map_value asc

    --Example: Get the type value for any given spinlock type
    select map_value, map_key, name from sys.dm_xe_map_values
    where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')
*/
IF EXISTS (
        SELECT *
        FROM sys.dm_xe_session_targets xst
        INNER JOIN sys.dm_xe_sessions xs
            ON (xst.event_session_address = xs.address)
        WHERE xs.name = 'spinlock_backoff_with_dump'
        )
    DROP EVENT SESSION spinlock_backoff_with_dump
        ON SERVER

CREATE EVENT SESSION spinlock_backoff_with_dump ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    --or type = 144           --SOS_CACHESTORE
    --or type = 8             --MUTEX
    --or type = 53            --LOGCACHE_ACCESS
    --or type = 41            --LOGFLUSHQ
    --or type = 25            --SQL_MGR
    --or type = 39            --XDESMGR
) ADD target package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
)

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = START;

DECLARE @instance_name NVARCHAR(MAX) = @@SERVICENAME;
DECLARE @loop_count INT = 1;
DECLARE @xml_result XML;
DECLARE @slot_count BIGINT;
DECLARE @xp_cmdshell NVARCHAR(MAX) = NULL;

--start polling for the backoffs
PRINT 'Polling for: ' + convert(VARCHAR(32), @total_delay_time_seconds) + ' seconds';

WHILE (@loop_count < CAST(@total_delay_time_seconds / 1 AS INT))
BEGIN
    WAITFOR DELAY '00:00:01'

    --get the xml from the bucketizer for the session
    SELECT @xml_result = CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets xst
    INNER JOIN sys.dm_xe_sessions xs
        ON (xst.event_session_address = xs.address)
    WHERE xs.name = 'spinlock_backoff_with_dump';

    --get the highest slot count from the bucketizer
    SELECT @slot_count = @xml_result.value(N'(//Slot/@count)[1]', 'int');

    --if the slot count is higher than the threshold in the one minute period
    --dump the process and clean up session
    IF (@slot_count > @dump_threshold)
    BEGIN
        PRINT 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 c:\ '''

        SELECT @xp_cmdshell = 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 ' + @output_path + ' '''

        EXEC sp_executesql @xp_cmdshell

        PRINT 'loop count: ' + convert(VARCHAR(128), @loop_count)
        PRINT 'slot count: ' + convert(VARCHAR(128), @slot_count)

        SET @dump_captured_flag = 1

        BREAK
    END

    --otherwise loop
    SET @loop_count = @loop_count + 1
END;

--see what was collected then clean up
DBCC TRACEON (3656, -1);

SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spinlock_backoff_with_dump';

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = STOP;
DROP EVENT SESSION spinlock_backoff_with_dump ON SERVER;
GO

/* CAPTURE THE DUMPS
******************************************************************/
--Example: This will run continuously until a dump is created.
DECLARE @sqldumper_path NVARCHAR(MAX) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"';
DECLARE @dump_threshold INT = 300; --capture mini dump when the slot count for the top bucket exceeds this
DECLARE @total_delay_time_seconds INT = 60; --poll for 60 seconds
DECLARE @PID INT = 0;
DECLARE @flag TINYINT = 0;
DECLARE @dump_count TINYINT = 0;
DECLARE @max_dumps TINYINT = 3; --stop after collecting this many dumps
DECLARE @output_path NVARCHAR(max) = 'c:\'; --no spaces in the path please :)
--Get the process id for sql server
DECLARE @error_log TABLE (
    LogDate DATETIME,
    ProcessInfo VARCHAR(255),
    TEXT VARCHAR(max)
);

INSERT INTO @error_log
EXEC ('xp_readerrorlog 0, 1, ''Server Process ID''');

SELECT @PID = convert(INT, (REPLACE(REPLACE(TEXT, 'Server Process ID is ', ''), '.', '')))
FROM @error_log
WHERE TEXT LIKE ('Server Process ID is%');

PRINT 'SQL Server PID: ' + convert(VARCHAR(6), @PID);

--Loop to monitor the spinlocks and capture dumps. while (@dump_count < @max_dumps)
BEGIN
    EXEC sp_xevent_dump_on_backoffs @sqldumper_path = @sqldumper_path,
        @dump_threshold = @dump_threshold,
        @total_delay_time_seconds = @total_delay_time_seconds,
        @PID = @PID,
        @output_path = @output_path,
        @dump_captured_flag = @flag OUTPUT

    IF (@flag > 0)
        SET @dump_count = @dump_count + 1

    PRINT 'Dump Count: ' + convert(VARCHAR(2), @dump_count)

    WAITFOR DELAY '00:00:02'
END;

Lampiran: Menangkap statistik spinlock dari waktu ke waktu

Skrip berikut dapat digunakan untuk melihat statistik spinlock selama periode waktu tertentu. Setiap kali dijalankan, akan mengembalikan perbedaan antara nilai saat ini dan nilai sebelumnya yang telah dikumpulkan.

/* Snapshot the current spinlock stats and store so that this can be compared over a time period
   Return the statistics between this point in time and the last collection point in time.

   **This data is maintained in tempdb so the connection must persist between each execution**
   **alternatively this could be modified to use a persisted table in tempdb. if that
   is changed code should be included to clean up the table at some point.**
*/
USE tempdb;
GO

DECLARE @current_snap_time DATETIME;
DECLARE @previous_snap_time DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (
    SELECT name
    FROM tempdb.sys.sysobjects
    WHERE name LIKE '#_spin_waits%'
)
CREATE TABLE #_spin_waits (
    lock_name VARCHAR(128),
    collisions BIGINT,
    spins BIGINT,
    sleep_time BIGINT,
    backoffs BIGINT,
    snap_time DATETIME
);

--capture the current stats
INSERT INTO #_spin_waits (
    lock_name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    snap_time
    )
SELECT name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    @current_snap_time
FROM sys.dm_os_spinlock_stats;

SELECT TOP 1 @previous_snap_time = snap_time
FROM #_spin_waits
WHERE snap_time < (
    SELECT max(snap_time)
    FROM #_spin_waits
)
ORDER BY snap_time DESC;

--get delta in the spin locks stats
SELECT TOP 10 spins_current.lock_name,
    (spins_current.collisions - spins_previous.collisions) AS collisions,
    (spins_current.spins - spins_previous.spins) AS spins,
    (spins_current.sleep_time - spins_previous.sleep_time) AS sleep_time,
    (spins_current.backoffs - spins_previous.backoffs) AS backoffs,
    spins_previous.snap_time AS [start_time],
    spins_current.snap_time AS [end_time],
    DATEDIFF(ss, @previous_snap_time, @current_snap_time) AS [seconds_in_sample]
FROM #_spin_waits spins_current
INNER JOIN (
    SELECT *
    FROM #_spin_waits
    WHERE snap_time = @previous_snap_time
    ) spins_previous
    ON (spins_previous.lock_name = spins_current.lock_name)
WHERE spins_current.snap_time = @current_snap_time
    AND spins_previous.snap_time = @previous_snap_time
    AND spins_current.spins > 0
ORDER BY (spins_current.spins - spins_previous.spins) DESC;

--clean up table
DELETE
FROM #_spin_waits
WHERE snap_time = @previous_snap_time;

Konten terkait