MEMPERBAIKI: Masalah kinerja yang terjadi saat pangkalan data kunci aktivitas meningkat di SQL Server

Gejala

Misalnya Anda menjalankan contoh Microsoft SQL Server 2014, SQL Server 2012, SQL Server 2008, atau SQL Server 2008 R2 di komputer yang berisi banyak prosesor. Bila jumlah kunci (tipe sumber daya = DATABASE) untuk pangkalan data khusus melebihi batas tertentu, Anda mengalami masalah kinerja berikut ini:

• Nilai-nilai yang ditinggikan terjadi karena jumlah spinlock LOCK_HASH .
  
  Catatan Lihat bagian "Informasi selengkapnya" untuk informasi tentang cara untuk memantau spinlock ini.

• Operasi yang memerlukan pangkalan data kunci atau permintaan memakan waktu lama untuk diselesaikan. Misalnya, Anda mungkin melihat penundaan kinerja berikut ini:

  • Login SQL Server

  • Pertanyaan terkait server

  • sp_reset_connection

  • Transaksi

Catatan Untuk mencari daftar kunci (tipe sumber daya = pangkalan data) pada database tertentu, lihat bagian "Informasi selengkapnya". Nilai ambang berbeda-beda berdasarkan lingkungan.

Pemecahan masalah

Informasi Hotfix
Tersedia hotfix yang didukung dari Microsoft. Namun, hotfix ini ditujukan untuk memperbaiki masalah yang dijelaskan di artikel ini. Menerapkan hotfix ini hanya ke sistem yang mengalami masalah khusus ini.

Apabila hotfix tersedia untuk diunduh, ada bagian "Tersedia Unduhan Hotfix" di bagian atas artikel Pangkalan Pengetahuan ini. Jika bagian ini tidak muncul, kirimkan permintaan ke layanan pelanggan Microsoft dan dukungan untuk mendapatkan hotfix.

Catatan Jika terjadi masalah tambahan atau apabila pemecahan masalah apa pun diperlukan, Anda mungkin harus membuat permintaan layanan secara terpisah. Biaya dukungan biasa akan berlaku untuk dukungan tambahan pertanyaan dan masalah yang tidak memenuhi syarat untuk hotfix ini. Untuk daftar lengkap nomor telepon layanan pelanggan Microsoft dan dukungan atau untuk membuat permintaan layanan terpisah, kunjungi situs web Microsoft berikut:

http://support.microsoft.com/contactus/?ws=supportCatatan Formulir "Tersedia Unduhan Hotfix" menampilkan bahasa hotfix tersedia. Jika Anda tidak melihat bahasa Anda, hal ini karena hotfix tidak tersedia untuk bahasa tersebut.

Status

Microsoft telah memastikan bahwa ini merupakan masalah di dalam produk Microsoft sebagaimana tercantum di bagian "Berlaku untuk".

Informasi lebih lanjut

Ketika aplikasi membuat sambungan ke SQL Server, pertama-tama menetapkan konteks pangkalan data. Secara default, sambungan akan mencoba mendapatkan kunci pangkalan data dalam SH mode. Kunci SH-DATABASE akan meluncurkan ketika sambungan dihentikan atau database konteks diubah selama masa sambungan. Jika Anda memiliki banyak sambungan aktif yang menggunakan konteks pangkalan data yang sama, Anda dapat memiliki banyak kunci DATABASE jenis sumber daya untuk pangkalan data yang spesifik.

Pada komputer yang memiliki 16 atau lebih CPU, hanya tabel objek menggunakan penguncian terpartisi skema. Namun, pangkalan data kunci tidak dibagi. Oleh karena itu, semakin besar jumlah pangkalan data kunci, lama waktu yang dibutuhkan untuk SQL Server untuk mendapatkan kunci di pangkalan data. Sebagian besar aplikasi tidak mengalami masalah yang disebabkan oleh desain ini. Tetapi sebagai nomor melebihi batas tertentu, upaya tambahan dan waktu diperlukan untuk mendapatkan kunci. Meskipun biaya hanya mikro detik untuk setiap kunci tambahan, total waktu cepat dapat meningkatkan karena kunci hash ember dilindungi dengan menggunakan spinlock. Hal ini menyebabkan tambahan siklus CPU dan menunggu untuk pekerja tambahan untuk mendapatkan kunci.

Hotfix ini memperkenalkan pangkalan data kunci partisi saat bendera pelacakan T1236 diaktifkan pada startup. Partisi data kunci terus-menerus kedalaman kunci daftar dikelola di setiap partisi lokal. Hal ini sangat mengoptimalkan jalur akses yang digunakan untuk mendapatkan kunci pangkalan data .

Untuk memantau LOCK_HASH spinlock, Anda dapat menggunakan kueri berikut ini.SET NOCOUNT ONCREATE TABLE #spinlock_stats([CaptureTime] datetime,[name] nvarchar(512),[collisions] bigint,
[spins] bigint,[spins_per_collision] real,[sleep_time] bigint,[backoffs] int)
DECLARE @counter int = 1
WHILE @counter < 100
BEGIN
INSERT INTO #spinlock_stats SELECT GETDATE() as "CaptureTime" , * FROM sys.dm_os_spinlock_stats WHERE [name] = 'LOCK_HASH'
WAITFOR DELAY '00:00:05'
SET @counter +=1
END
SELECT * FROM #spinlock_stats ORDER BY [CaptureTime]
DROP TABLE #spinlock_stats
Untuk informasi selengkapnya tentang mendiagnosis dan menyelesaikan spinlock sanggahan di SQL Server, buka dokumen berikut ini:

Mendiagnosis dan menyelesaikan Spinlock sanggahan di SQL ServerCatatan Meskipun dokumen ini ditulis untuk SQL Server 2008 R2, informasi ini masih berlaku untuk SQL Server 2012.

Referensi

Untuk informasi selengkapnya tentang bendera pelacakan di SQL Server 2012, kunjungi situs web TechNet berikut:

Informasi tentang bendera pelacakan di SQL Server 2012
Untuk informasi selengkapnya tentang cara menemukan nomor pangkalan data kunci pengguna per database, menggunakan kueri berikut ini untuk menghitung nilai ini:select Resource_database_id, resource_type, request_mode, request_status, count (*) 'LockCount' from sys.dm_tran_locks
group by Resource_database_id, resource_type, request_mode, request_status