Mendiagnosis dan mengatasi pertikaian spinlock di SQL Server

  • Artikel

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

Catatan

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, komoditas komputer Windows Server hanya menggunakan satu atau dua chip microprocessor/CPU, dan CPU telah 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. Ketidakcocokan spinlock adalah salah satu jenis masalah konkurensi yang diamati dalam beban kerja pelanggan nyata 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 spinlock dan pemecahan masalah harus dianggap sebagai subjek lanjutan, yang membutuhkan pengetahuan tentang 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 utas yang mencoba memperoleh spinlock tidak dapat memperoleh akses, utas dijalankan secara berkala memeriksa untuk menentukan apakah sumber daya tersedia alih-alih segera menghasilkan. Setelah beberapa periode waktu, utas yang menunggu spinlock akan menghasilkan sebelum dapat memperoleh sumber daya. Menghasilkan memungkinkan utas lain yang berjalan pada CPU yang sama untuk dijalankan. Perilaku ini dikenal sebagai backoff dan dibahas secara 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 kait dan spinlock adalah fakta bahwa spinlock berputar (menjalankan perulangan) untuk jangka waktu memeriksa ketersediaan struktur data sementara utas mencoba memperoleh akses ke struktur yang dilindungi oleh kait segera menghasilkan 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 untuk durasi singkat secara keseluruhan lebih efisien untuk memungkinkan utas dijalankan dalam perulangan secara berkala memeriksa ketersediaan sumber daya.

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

Gejala

Pada sistem konkurensi tinggi yang sibuk, biasanya melihat ketidakcocokan aktif pada struktur yang sering diakses yang dilindungi oleh spinlock. Penggunaan ini hanya dianggap bermasalah ketika ketidakcocokan memperkenalkan overhead CPU yang signifikan. Statistik spinlock diekspos oleh sys.dm_os_spinlock_stats Tampilan Manajemen Dinamis (DMV) dalam SQL Server. Misalnya, kueri ini menghasilkan output berikut:

Catatan

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

SELECT * FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

Screenshot showing `sys.dm_os_spinlock_stats` output.

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 bertahap untuk setiap kali utas menjalankan perulangan sambil menunggu spinlock 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 back-off; 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 minat tertentu adalah jumlah tabrakan, putaran, dan peristiwa backoff 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 bertambah dan utas akan mulai berputar dalam perulangan dan secara berkala memeriksa apakah sumber daya tersedia. Setiap kali utas berputar (perulangan) jumlah putaran bertahap.

Putaran per tabrakan adalah ukuran jumlah putaran yang terjadi saat spinlock ditahan oleh utas, dan memberi tahu Anda berapa banyak putaran yang terjadi saat utas menahan spinlock. Misalnya, spin kecil per tabrakan dan jumlah tabrakan tinggi berarti ada sejumlah kecil putaran yang terjadi di bawah spinlock dan ada banyak utas yang bersaing untuk itu. Sejumlah besar putaran berarti waktu yang dihabiskan berputar dalam kode spinlock yang relatif panjang umurnya (yaitu, kode akan lebih dari sejumlah besar entri dalam wadah hash). Ketika pertikaian meningkat (sehingga meningkatkan jumlah tabrakan), jumlah putaran juga meningkat.

Backoff mungkin dipikirkan dengan cara yang mirip dengan putaran. 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, backoff spinlock, atau berhenti berputar dan "tidur". Spinlocks backoff terlepas dari apakah mereka pernah mendapatkan kepemilikan sumber daya target. Ini dilakukan untuk memungkinkan utas lain dijadwalkan pada CPU dengan harapan bahwa ini.dapat memungkinkan pekerjaan yang lebih produktif terjadi. Perilaku default untuk mesin adalah memutar interval waktu konstan terlebih dahulu sebelum melakukan backoff. Mencoba mendapatkan spinlock mengharuskan status konkurensi cache dipertahankan, yang merupakan operasi intensif CPU relatif terhadap biaya pemintalan CPU. Oleh karena itu, upaya untuk mendapatkan spinlock dilakukan dengan hemat dan tidak dilakukan setiap kali utas berputar. Di SQL Server jenis spinlock tertentu (misalnya: LOCK_HASH) ditingkatkan dengan menggunakan interval yang meningkat secara eksponensial antara upaya untuk memperoleh spinlock (hingga batas tertentu), yang sering mengurangi efek pada performa CPU.

Diagram berikut memberikan tampilan konseptual algoritma spinlock:

Diagram showing a conceptual view of the spinlock algorithm.

Skenario khusus

Pertikaian spinlock dapat terjadi karena sejumlah alasan yang mungkin tidak terkait dengan keputusan desain database. Karena spinlocks akses gerbang ke struktur data internal, ketidakcocokan spinlock tidak dimanifestasikan dengan cara yang sama seperti pertikaian kait buffer, yang dipengaruhi langsung 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 paling umum pada >= 32 sistem 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 dapat menunjukkan ketidakcocokan spinlock:

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

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

  • Sistem mengalami konkurensi tinggi.

  • Penggunaan dan putaran CPU ditingkatkan tidak proporsional terhadap throughput.

    Penting

    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 akan disebabkan oleh alasan selain pertikaian spinlock. Beberapa penyebab yang lebih umum untuk peningkatan konsumsi CPU meliputi:

  • Kueri yang menjadi lebih mahal dari waktu ke waktu karena pertumbuhan data yang mendasarinya mengakibatkan kebutuhan untuk melakukan pembacaan logis tambahan dari data residen memori.

  • Perubahan dalam rencana kueri yang mengakibatkan eksekusi suboptimal.

Jika semua kondisi ini benar, lakukan penyelidikan lebih lanjut tentang kemungkinan masalah pertikaian spinlock.

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.

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

Contoh

Dalam contoh berikut, ada hubungan yang hampir linier antara konsumsi CPU dan throughput sebagaimana diukur oleh transaksi per detik. Adalah normal untuk melihat beberapa divergensi di sini karena overhead dikeluarkan karena beban kerja meningkat. Seperti yang diilustrasikan di sini, divergensi ini menjadi signifikan. Ada juga penurunan throughput yang curah hujan setelah konsumsi CPU mencapai 100%.

Screenshot showing CPU drops in performance monitor.

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

Screenshot showing a chart of spins over 3 minute intervals.

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

Beberapa skenario yang rentan terhadap masalah ini meliputi:

  • Masalah resolusi nama yang disebabkan oleh kegagalan untuk sepenuhnya memenuhi syarat 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.

  • Ketidakcocokan untuk mengunci wadah hash di manajer kunci untuk beban kerja yang sering mengakses kunci yang sama (seperti kunci bersama pada baris yang sering dibaca). Jenis pertikaian ini muncul sebagai spinlock jenis LOCK_HASH. 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 ketika ada tingkat latensi tinggi 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 Menggunakan
Monitor Performa Cari kondisi CPU yang tinggi atau divergensi antara konsumsi throughput dan CPU.
sys.dm_os_spinlock stats DMV** Cari sejumlah besar peristiwa putaran dan backoff selama periode waktu tertentu.
Peristiwa yang diperluas SQL Server Digunakan untuk melacak tumpukan panggilan untuk spinlock yang mengalami jumlah putaran yang tinggi.
Cadangan memori Dalam beberapa kasus, cadangan memori proses SQL Server dan alat Windows Debugging. Secara umum, tingkat analisis ini dilakukan ketika tim dukungan Microsoft SQL Server 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 di direktori yang sama dengan file .exe layanan SQL Server (sqlservr.exe) untuk instans SQL Server.\ Untuk melihat tumpukan panggilan untuk peristiwa back off, 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 peristiwa back off untuk jenis spinlock yang diminati.

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

Panduan diagnostik

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

Gejala

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

Ini adalah kasus ekstrem di mana ketidakcocokan itu spt itu menciptakan kondisi konvoi spinlock. Konvoi terjadi ketika utas tidak dapat lagi membuat kemajuan melayani beban kerja tetapi sebaliknya menghabiskan semua sumber daya pemrosesan yang mencoba mendapatkan akses ke kunci. Log monitor performa menggambarkan divergensi ini antara throughput log transaksi dan konsumsi CPU dan, pada akhirnya, lonjakan besar dalam pemanfaatan CPU.

Screenshot showing a CPU spike in performance monitor.

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 Spins Berputar per tabrakan Backoff
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 pertikaian yang signifikan karena menunjukkan kapan utas menghabiskan batas putaran saat menunggu untuk memperoleh 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 Peristiwa yang Diperluas di SQL Server, lihat Memperkenalkan Peristiwa Yang Diperluas SQL Server.

Skrip

/*
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 bucketize 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 wadah slot tertinggi umum antara dua 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.

Catatan

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

Catatan

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 paling menarik memiliki Jumlah Slot tertinggi (35.668 dan 8.506), yang, pada kenyataannya, memiliki jumlah > slot 1000.

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 tumpukan panggilan, kita dapat melihat bahwa jalur kode tempat masalah terjadi terkait dengan pencarian keamanan dan metadata (Seperti yang terbukti oleh bingkai CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID)tumpukan berikut .

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 hak istimewa sysadmin. 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 hak istimewa yang ditinggikan (sysadmin), lonjakan CPU yang terkait dengan ketidakcocokan 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 konkurensi tinggi, salah satunya menjadi backoff eksponensial untuk titik pertikaian yang paling umum. Ada peningkatan khusus 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 jenis spinlock SOS_CACHESTORE 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 Ketidakcocokan: Ketidakcocokan pada struktur kunci atau tabrakan wadah hash tertentu tidak dapat ditolak 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 petunjuk NOLOCK jika memungkinkan.

Baris pertama defensif 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 dapat 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.

Semoga teknik ini akan memberikan metodologi yang berguna untuk jenis pemecahan masalah dan wawasan ini tentang beberapa teknik pembuatan profil performa yang lebih canggih yang tersedia dengan SQL Server.

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 akan diperlukan untuk melakukan diagnosis lengkap masalah atau akan diminta oleh tim dukungan Microsoft untuk melakukan analisis mendalam. Di SQL Server 2008, ada batas 16 bingkai dalam tumpukan panggilan yang ditangkap oleh bucketizer, yang mungkin tidak cukup dalam untuk menentukan dengan tepat dari mana di mesin tempat tumpukan panggilan dimasukkan. SQL Server 2012 memperkenalkan peningkatan dengan meningkatkan jumlah bingkai dalam tumpukan panggilan yang ditangkap oleh bucketizer menjadi 32.

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, delta akan mengembalikan delta antara nilai saat ini dan nilai sebelumnya yang 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;

Langkah selanjutnya

Alat Penyetelan dan Pemantauan Performa