INF: Memahami dan memecahkan masalah pemblokiran SQL Server

Terjemahan Artikel Terjemahan Artikel
ID Artikel: 224453 - Melihat produk di mana artikel ini berlaku.
Perbesar semua | Perkecil semua

Pada Halaman ini

RINGKASAN

Pada artikel ini, istilah "sambungan" merujuk kepada satu logon sesi database. Setiap sambungan muncul sebagai ID sesi (SPID). Masing-masing SPIDs ini sering disebut sebagai proses, meskipun tidak konteks proses terpisah dalam arti biasa. Sebaliknya, SPID masing-masing terdiri dari struktur data dan sumber daya server diperlukan untuk melayani permintaan sambungan tunggal dari klien tertentu. A satu klien aplikasi mungkin memiliki satu atau lebih banyak koneksi. Dari perspektif dari SQL Server, tidak ada perbedaan antara beberapa koneksi dari satu klien aplikasi pada komputer klien tunggal dan multiple sambungan dari beberapa klien aplikasi atau beberapa komputer klien. Salah satu koneksi dapat memblokir koneksi lain, terlepas dari apakah mereka berasal dari aplikasi yang sama atau terpisah aplikasi pada dua klien yang berbeda komputer.

INFORMASI LEBIH LANJUT

Memblokir adalah karakteristik yang tidak dapat dihindari dari setiap relasional sistem manajemen basis data (RDBMS) dengan berbasis kunci concurrency. Pada SQL Server, memblokir terjadi ketika satu SPID memegang kunci pada sumber daya tertentu dan kedua SPID upaya untuk memperoleh sejenis kunci bertentangan pada sumber daya yang sama. Biasanya, kerangka waktu yang SPID pertama mengunci sumber daya yang sangat kecil. Ketika melepaskan kunci, koneksi kedua gratis untuk memperoleh yang memiliki kunci pada sumber daya dan melanjutkan proses. Ini merupakan perilaku normal dan mungkin terjadi berkali-kali sepanjang hari dengan efek tidak terlihat pada kinerja sistem.

Konteks durasi dan transaksi permintaan menentukan berapa lama kunci yang diselenggarakan dan, dengan demikian, dampaknya terhadap lain queries. Jika query tidak dijalankan dalam transaksi (dan tidak ada petunjuk kunci digunakan), kunci untuk Pilih pernyataan hanya akan diadakan pada sumber daya pada waktu itu benar-benar sedang dibaca, tidak selama query. Untuk INSERT, UPDATE, dan DELETE pernyataan, kunci diadakan selama permintaan, baik untuk data konsistensi dan untuk memungkinkan permintaan untuk memutar kembali Jika diperlukan.

Untuk pertanyaan yang dijalankan dalam transaksi, durasi yang kunci diadakan ditentukan oleh jenis permintaan, tingkat isolasi transaksi, dan apakah atau tidak petunjuk kunci yang digunakan dalam permintaan. Untuk keterangan mengunci, mengunci petunjuk dan transaksi isolasi tingkat, lihat topik berikut dalam SQL Server buku Online:
  • Mengunci di mesin Database
  • Menyesuaikan mengunci dan versi baris
  • Kunci mode
  • Kunci kompatibilitas
  • Baris berbasis versi tingkat isolasi di mesin Database
  • Mengendalikan transaksi (Database Engine)
Ketika mengunci dan memblokir peningkatan ke titik di mana ada efek yang merugikan pada kinerja sistem, hal ini biasanya disebabkan oleh salah satu alasan berikut:
  • SPID memegang kunci pada set sumber daya untuk diperpanjang jangka waktu sebelum melepaskan mereka. Jenis ini menghalangi menyelesaikan sendiri dari waktu ke waktu, tetapi dapat menyebabkan penurunan kinerja.
  • SPID memegang kunci pada set sumber daya dan tidak pernah melepaskan mereka. Jenis ini menghalangi tidak memecahkan itu sendiri dan mencegah akses ke terpengaruh sumber daya tanpa batas.
Dalam skenario pertama di atas, masalah pemblokiran menyelesaikan itu sendiri dari waktu ke waktu sebagai SPID melepaskan kunci. Namun, situasi dapat sangat fluida berbeda SPIDs menyebabkan menghalangi pada sumber daya yang berbeda dari waktu ke waktu, menciptakan target yang bergerak. Untuk alasan ini, situasi ini dapat menjadi sulit untuk memecahkan masalah menggunakan SQL Server Enterprise Manager atau individu SQL queries. The kedua hasil situasi di negara yang konsisten yang dapat lebih mudah untuk mendiagnosis.

Pengumpulan memblokir informasi

Untuk mengatasi kesulitan mengatasi masalah pemblokiran masalah, database administrator dapat menggunakan skrip SQL yang terus-menerus memantau keadaan mengunci dan memblokir pada SQL Server. Script ini dapat memberikan snapshot dari contoh-contoh spesifik dari waktu ke waktu, menuju keseluruhan gambar masalah. Untuk penjelasan bagaimana untuk memantau menghalangi dengan skrip SQL, lihat artikel berikut pada Basis Pengetahuan Microsoft:
271509 Bagaimana memonitor menghalangi SQL Server 2005 dan di SQL Server 2000
Skrip di artikel ini akan melakukan tugas di bawah ini. Di mana mungkin, metode untuk memperoleh informasi ini dari SQL Server Management Studio diberikan.
  1. Mengidentifikasi SPID (Session ID) kepala rantai memblokir dan pernyataan SQL.
    Selain untuk menggunakan script di artikel Basis Pengetahuan yang disebutkan sebelumnya, Anda dapat mengidentifikasi kepala rantai memblokir dengan menggunakan fitur yang disediakan melalui SQL Server Management Studio. Untuk melakukannya, gunakan salah satu dari metode berikut ini:
    • Klik kanan objek server, memperluas Laporan, memperluas Laporan standar, lalu klik Kegiatan-semua transaksi yang Pemblokir. Laporan ini menunjukkan transaksi di kepala menghalangi rantai. Jika Anda memperluas transaksi, laporan akan menunjukkan transaksi yang diblokir oleh kepala transaksi. Laporan ini juga akan menampilkan "Menghalangi SQL pernyataan" dan "Pernyataan SQL Blocked."
    • Menggunakan DBCC INPUTBUFFER (<spid>) untuk menemukan pernyataan terakhir yang telah disampaikan oleh SPID.</spid>
  2. Menemukan tingkat nesting transaksi dan status proses memblokir SPID.
    Transaksi yang bersarang tingkat SPID tersedia dalam @@ TRANCOUNT variabel global. Namun, yang dapat ditentukan dari luar SPID oleh query sysprocesses tabel sebagai berikut:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
    go
    						
    Nilai kembali adalah nilai @@ TRANCOUNT untuk SPID. Ini menunjukkan tingkat nesting transaksi untuk memblokir SPID, yang pada gilirannya dapat menjelaskan Mengapa itu memegang kunci. Misalnya, jika nilai lebih besar dari nol, SPID adalah di tengah-tengah transaksi (dalam hal ini diperkirakan bahwa mempertahankan kunci tertentu itu telah mengambil alih, tergantung pada transaksi isolasi tingkat).

    Anda juga dapat memeriksa untuk melihat apakah ada jangka panjang membuka transaksi ada di database dengan menggunakan DBCC OPENTRANdatabase_name.

Pengumpulan informasi jejak SQL Server Profiler

Selain informasi di atas, itu sering diperlukan untuk merekam jejak Profiler kegiatan pada server untuk benar-benar menyelidiki masalah pemblokiran pada SQL Server. Jika SPID mengeksekusi beberapa pernyataan dalam transaksi, hanya statementthat terakhir telah disampaikan akan menampilkan dalam laporan, masukan buffer, atau aktivitas monitor output. Namun, salah satu perintah sebelumnya mungkin alasan kunci masih ditahan. Jejak Profiler akan memungkinkan Anda untuk melihat semua perintah dijalankan oleh SPID dalam transaksi saat ini. Langkah berikut membantu Anda untuk mengatur SQL Server Profiler untuk menangkap jejak.
  1. Buka SQL Server Profiler.
  2. Pada Berkas menu, titik Baru, lalu klik Jejak.
  3. Pada General tab, menetapkan nama kecil dan nama file untuk menangkap data.

    Penting Jejak file harus ditulis ke disk cepat lokal atau bersama. Menghindari melacak untuk drive disk atau jaringan yang lambat. Juga pastikan Server proses jejak data dipilih.
  4. Pada Peristiwa seleksi tab, klik untuk memilih Tampilkan semua peristiwa dan Tampilkan semua kolom kotak centang.
  5. Pada Peristiwa seleksi tab, menambah jenis peristiwa yang tercantum dalam tabel 1 jejak Anda.

    Selain itu, Anda mungkin termasuk jenis acara tambahan yang didaftar di tabel 2 untuk informasi lebih lanjut. Jika Anda menjalankan dalam lingkungan produksi volume tinggi, Anda dapat memutuskan untuk menggunakan hanya peristiwa-peristiwa dalam tabel 1, karena mereka biasanya cukup untuk memecahkan sebagian besar masalah pemblokiran. Termasuk acara tambahan dalam tabel 2 mungkin membuatnya lebih mudah untuk dengan cepat menentukan sumber masalah (atau peristiwa-peristiwa ini mungkin diperlukan untuk mengidentifikasi pernyataan pelakunya dalam prosedur multi-statement). Namun, termasuk peristiwa dalam tabel 2 juga akan menambah beban pada sistem dan meningkatkan jejak output ukuran.
Tabel 1: Jenis peristiwa
Perkecil tabel iniPerbesar tabel ini
PosPeristiwa
Kesalahan dan peringatanPengecualian
Kesalahan dan peringatanPerhatian
Audit keamananAudit Login
Audit keamananAudit Logout
SesiAda koneksi
Disimpan prosedurRPC: mulai
TSQLSQL:BatchStarting

Tabel 2: Acara tambahan jenis
Perkecil tabel iniPerbesar tabel ini
PosPeristiwa
TransaksiDTCTransaction
TransaksiSQLTransaction
Disimpan prosedurRPC: selesai
TSQLSQL:BatchCompleted
Disimpan prosedurSP:StmtStarting
Disimpan prosedurSP:StmtCompleted

Untuk informasi lebih lanjut tentang menggunakan SQL Server Profiler, silakan lihat SQL Server Buku secara Online.

Mengidentifikasi dan memecahkan Common menghalangi skenario

Dengan memeriksa informasi di atas, Anda dapat menentukan penyebab Kebanyakan masalah pemblokiran. Sisa dari artikel ini adalah sebuah diskusi tentang bagaimana menggunakan informasi ini untuk mengidentifikasi dan menyelesaikan beberapa skenario memblokir umum. Diskusi ini mengasumsikan Anda telah menggunakan skrip memblokir dalam artikel 271509 (direferensikan sebelumnya) untuk menangkap informasi memblokir SPIDs dan telah membuat jejak Profiler dengan peristiwa-peristiwa yang dijelaskan di atas.

Melihat Output Script memblokir

Memeriksa sys.sysprocesses output untuk menentukan kepala rantai memblokir
Jika Anda tidak menentukan mode cepat untuk memblokir skrip, akan ada bagian yang berjudul "SPIDs kepala memblokir rantai" Daftar SPIDs yang menghalangi lain SPIDs dalam naskah output.
SPIDs at the head of blocking chains
Jika Anda menentukan pilihan cepat, Anda masih dapat menentukan memblokir kepala dengan melihat sys.sysprocesses output dan mengikuti hirarki SPID yang dilaporkan di kolom diblokir.
Memeriksa sys.sysprocesses output untuk informasi tentang SPIDs di kepala rantai menghalangi.
Sangat penting untuk mengevaluasi berikut sys.sysprocesses bidang:

Status

Kolom ini menunjukkan status SPID tertentu. Biasanya, tidur status menunjukkan bahwa SPID telah menyelesaikan eksekusi dan menunggu aplikasi untuk mengirimkan permintaan atau batch lain. A Runnable, menjalankan, atau sos_scheduler_yield status menunjukkan bahwa SPID saat memproses permintaan. The Tabel berikut memberikan penjelasan singkat tentang status berbagai nilai-nilai.
Perkecil tabel iniPerbesar tabel ini
StatusMakna
Latar belakangSPID menjalankan latar belakang tugas, seperti deteksi deadlock.
TidurSPID tidak saat ini pelaksana. Ini biasanya menunjukkan bahwa SPID adalah menunggu perintah dari aplikasi.
MenjalankanSPID yang sedang berjalan di Penjadwal.
RunnableSPID adalah dalam antrian runnable Penjadwal dan menunggu untuk mendapatkan waktu scheduler.
Sos_scheduler_yieldSPID berlari, tetapi telah secara sukarela hasil yang waktu slice di scheduler untuk mengizinkan SPID lain untuk memperoleh waktu scheduler.
DitangguhkanSPID adalah menunggu peristiwa, seperti kunci atau kait.
KembalikanSPID adalah dalam rollback transaksi.
DefwakeupMenunjukkan bahwa SPID yang menunggu untuk sumber daya yang sedang dalam proses dibebaskan. Bidang waitresource harus menunjukkan sumber daya tersebut.

Open_tran

Bidang ini memberitahu Anda transaksi tingkat nesting dari SPID. Jika nilai ini lebih besar dari 0, SPID dalam transaksi terbuka dan mungkin memegang kunci diakuisisi oleh pernyataan dalam transaksi.

Lastwaittype, waittype, dan waittime

The lastwaittype lapangan adalah representasi string waittype lapangan, yang dilindungi undang-undang kolom biner internal. Jika waittype adalah 0x0000, SPID tidak sedang menunggu untuk apa pun dan The lastwaittype nilai menunjukkan terakhir waittype bahwa SPID telah. Jika waittype bukanlah nol, lastwaittype nilai menunjukkan saat ini waittype dari SPID.

Untuk deskripsi singkat yang berbeda lastwaittype dan waittype nilai-nilai, lihat artikel berikut di Microsoft Basis Pengetahuan:
822101 Deskripsi dari waittype dan lastwaittype kolom dalam tabel master.dbo.sysprocesses di SQL Server 2000 dan SQL Server 2005
Untuk informasi selengkapnya tentang sys.dm_os_wait_stats, melihat SQL Server buku Online.

The waittime nilai dapat digunakan untuk menentukan jika SPID adalah membuat kemajuan. Ketika sebuah query terhadap sys.sysprocesses Tabel mengembalikan nilai dalam waittime kolom yang kurang dari waittime nilai dari permintaan sebelumnya dari sys.sysprocesses, ini menunjukkan bahwa kunci sebelumnya diperoleh dan dirilis dan Sekarang menunggu pada kunci baru (dengan asumsi bukan nol waittime). Ini dapat diverifikasi dengan membandingkan waitresource antara sys.sysprocesses output.

Waitresource

Bidang ini menunjukkan sumber bahwa SPID adalah menunggu. Tabel berikut mencantumkan umum waitresource format dan makna mereka:
Perkecil tabel iniPerbesar tabel ini
Sumber dayaFormatContoh
TabelDatabaseID:ObjectID:IndexIDTAB: 5:261575970:1
Dalam kasus ini, database ID 5 yang pub database contoh dan obyek ID 261575970 judul tabel dan 1 adalah indeks berkerumun.
HalamanDatabaseID:FileID:PageIDHALAMAN: 5:1:104
Dalam kasus ini, database ID 5 yang pub, file ID 1 adalah file data primer, dan halaman 104 adalah halaman milik judul tabel.

Untuk mengidentifikasi id objek yang halaman milik, gunakan perintah DBCC halaman (dbid, fileid, pageid, output_option), dan melihat m_objId. Misalnya:
DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )
KunciDatabaseID:Hobt_id (nilai Hash Indeks key)KUNCI: 5:72057594044284928 (3300a4f361aa)

Dalam kasus ini, database ID 5 pub, Hobt_ID 72057594044284928 sesuai dengan non berkerumun index_id 2 untuk objek id 261575970)judul Tabel). Gunakan sys.partitions katalog tampilan untuk mengasosiasikan hobt_id untuk indeks tertentu id dan id objek. Tidak ada cara untuk unhash hash kunci indeks nilai kunci indeks tertentu.
BarisDatabaseID:FileID:PageID:Slot(row)MENYINGKIRKAN: 5:1:104:3

Dalam kasus ini, database ID 5 adalah pub, file ID 1 adalah file data primer, halaman 104 adalah halaman milik judul meja dan slot 3 menunjukkan baris posisi pada halaman.
KompilasiDatabaseID:ObjectID [[KOMPILASI]]TAB: 5:834102012 [[KOMPILASI]] ini bukanlah sebuah meja mengunci, tapi agak kunci kompilasi pada prosedur yang disimpan. Database ID 5 pub, objek ID 834102012 usp_myprocedure disimpan prosedur. Lihat artikel Basis Pengetahuan 263889 untuk informasi lebih lanjut mengenai pemblokiran disebabkan oleh kompilasi kunci.
Kolom lain

Sisa sys.sysprocesses kolom dapat memberikan wawasan ke dalam akar masalah juga. Kegunaan mereka bervariasi tergantung pada keadaan masalah. Untuk contoh, Anda dapat menentukan apakah masalah terjadi hanya dari klien tertentu (hostname), di beberapa jaringan perpustakaan (net_library), ketika batch terakhir Dikirimkan oleh SPID adalah (last_batch), dan seterusnya.
Memeriksa output DBCC INPUTBUFFER.
Untuk setiap SPID di kepala rantai memblokir atau dengan bukan nol waittype, menghalangi script akan mengeksekusi DBCC INPUTBUFFER untuk menentukan permintaan saat ini untuk SPID itu.

Dalam banyak kasus, ini adalah permintaan yang menyebabkan kunci yang yang menghalangi pengguna lain akan diadakan. Namun, jika SPID dalam transaksi, kunci mungkin telah diakuisisi oleh permintaan sebelumnya dieksekusi, bukan satu saat ini. Oleh karena itu, Anda juga harus melihat output Profiler untuk SPID, bukan hanya inputbuffer.

Catatan Karena script memblokir terdiri dari beberapa langkah, mungkin yang SPID mungkin muncul di bagian pertama sebagai kepala menghalangi rantai, tetapi pada saat DBCC INPUTBUFFER query dijalankan, tidak lagi memblokir dan INPUTBUFFER tidak ditangkap. Ini menunjukkan bahwa menghalangi adalah menyelesaikan sendiri untuk SPID itu dan itu mungkin atau mungkin tidak masalah. Ini titik, Anda dapat menggunakan versi cepat memblokir script untuk mencoba Pastikan Anda menangkap inputbuffer sebelum membersihkan (meskipun masih tidak menjamin), atau melihat data Profiler dari kerangka waktu itu untuk menentukan apa pertanyaan SPID mengeksekusi.

Melihat Profiler Data

Melihat Profiler data efisien sangat berharga dalam menyelesaikan masalah pemblokiran. Yang paling penting untuk menyadari adalah bahwa Anda lakukan tidak perlu melihat segala sesuatu yang Anda menangkap; selektif. Profiler menyediakan kemampuan untuk membantu Anda efektif melihat data ditangkap. Dalam Properti kotak dialog (pada Berkas menu, klik Properti), Profiler meleluasakan Anda untuk membatasi data ditampilkan dengan menghapus data kolom atau acara pengelompokan (penyortiran) oleh kolom data dan menerapkan filter. Anda dapat mencari jejak seluruh atau hanya kolom khusus untuk khusus nilai (pada Mengedit menu, klik Menemukan). Anda juga dapat menyimpan data tabel SQL Server Profiler (pada The Berkas menu, titik Simpan sebagai kemudian klik Tabel) dan menjalankan SQL query terhadap itu.

Hati-hati bahwa Anda melakukan penyaringan hanya pada jejak yang sebelumnya telah disimpan file. Jika Anda melakukan langkah-langkah berikut pada jejak aktif, Anda risiko kehilangan data yang telah ditangkap karena jejak dimulai. Simpan aktif menelusuri ke file atau tabel pertama (pada Berkas menu, klik Simpan sebagai) dan kemudian buka kembali (pada Berkas menu, klik Terbuka) sebelum melanjutkan. Ketika bekerja pada file disimpan jejak, penyaringan tidak secara permanen menghapus data yang disaring, hanya melakukan menampilkan semua data. Anda dapat menambah dan menghapus peristiwa dan kolom data sebagai diperlukan untuk membantu fokus pencarian Anda.

Apa untuk diperhatikan:
  • Apa perintah telah SPID di kepala rantai memblokir dijalankan dalam transaksi saat ini?
    Menyaring jejak data untuk SPID tertentu yang adalah kepala dari rantai memblokir (pada Berkas menu, klik Properti; kemudian pada Filter tab menentukan nilai SPID). Anda kemudian dapat memeriksa perintah itu telah dijalankan sebelum ke waktu itu menghalangi SPIDs lain. Jika Anda menyertakan Transaksi peristiwa, mereka dapat dengan mudah mengidentifikasi ketika transaksi dimulai. Jika tidak, Anda dapat mencari Teks kolom untuk BEGIN, Simpan, COMMIT, atau transaksi ROLLBACK operasi. Penggunaan open_tran nilai dari sysprocesses tabel untuk memastikan bahwa Anda menangkap semua peristiwa transaksi. Mengetahui perintah dieksekusi dan konteks transaksi akan memungkinkan Anda untuk menentukan mengapa SPID memegang kunci.

    Ingat, Anda dapat menghapus peristiwa dan data kolom. Alih-alih melihat kedua mulai dan selesai peristiwa, pilih salah satu. Jika memblokir SPIDs tidak disimpan prosedur, menghapusSP: mulai atau SP: selesai peristiwa; The SQLBatch dan RPC acara akan menunjukkan prosedur panggilan. Hanya melihat peristiwa SP ketika Anda perlu untuk melihat bahwa tingkat detail.
  • Apakah durasi permintaan untuk SPIDs di kepala dari menghalangi rantai?
    Jika Anda menyertakan acara selesai di atas, Durasi kolom akan menunjukkan waktu eksekusi query. Ini dapat membantu Anda mengidentifikasi berjalan lama query yang menyebabkan menghalangi. Untuk menentukan mengapa permintaan kinerja perlahan-lahan, lihat CPU, Baca, dan Menulis kolom, serta Rencana pelaksanaan acara.

Mengkategorikan skenario memblokir umum

Tabel di bawah peta gejala umum untuk mereka kemungkinan penyebab. Nomor yang ditunjukkan dalam Skenario kolom yang sesuai dengan nomor di "umum menghalangi Skenario dan resolusi"bagian dari artikel ini di bawah ini. The Waittype, Open_Tran, dan Status merujuk pada kolom sysprocesses informasi. The Menyelesaikan? kolom menunjukkan apakah atau tidak menghalangi akan menyelesaikan pada yang sendiri.

Perkecil tabel iniPerbesar tabel ini
SkenarioWaittypeOpen_TranStatusMenyelesaikan?Lainnya Gejala
1Non-nol>= 0RunnableYa, ketika permintaan selesai.Physical_IO, CPU dan/atau Memusage kolom akan meningkatkan dari waktu ke waktu. Durasi untuk permintaan akan tinggi ketika selesai.
20x0000mengatakan 0tidurTidak tapi SPID bisa membunuh.Perhatian sinyal dapat dilihat di Profiler jejak untuk SPID ini, menunjukkan permintaan timeout atau Batal telah terjadi.
30x0000mengatakan = 0RunnableTidak. Tidak akan memecahkan sampai klien mengambil semua baris atau menutup sambungan. SPID dapat dibunuh, tapi mungkin diperlukan waktu hingga 30 detik.Jika open_tran = 0, dan SPID memegang kunci sementara isolasi transaksi tingkat default (baca COMMMITTED), ini adalah penyebab.
4Bervariasimengatakan = 0RunnableTidak. Tidak akan memecahkan sampai klien membatalkan permintaan atau menutup koneksi. SPIDs dapat dibunuh, tapi mungkin butuh waktu hingga 30 detik.The nama host kolom di sysprocesses untuk SPID di kepala rantai memblokir akan sama seperti salah satu SPID yang menghalangi.
50x0000mengatakan 0KembalikanYa.An perhatian sinyal dapat terlihat di jejak Profiler untuk SPID ini, menunjukkan permintaan timeout atau Batal telah terjadi, atau hanya sebuah pernyataan rollback telah dikeluarkan.
60x0000mengatakan 0tidurAkhirnya. Ketika Windows NT menentukan sesi adalah tidak lagi aktif, SQL Server sambungan akan patah.The last_batch nilai dalam sysprocesses jauh lebih awal dari waktu sekarang.

Umum menghalangi skenario dan resolusi

Skenario-skenario yang tercantum di bawah ini akan memiliki karakteristik yang terdaftar dalam tabel di atas. Bagian ini menyediakan rincian tambahan ketika berlaku, serta jalan untuk resolusi.
  1. Memblokir disebabkan oleh biasanya menjalankan Query dengan waktu eksekusi lama

    Resolusi:
    Solusi untuk masalah memblokir jenis ini adalah untuk mencari cara untuk mengoptimalkan query. Sebenarnya, ini kelas masalah pemblokiran mungkin hanya menjadi masalah kinerja, dan memerlukan Anda untuk mengejar seperti. Untuk informasi pada pemecahan masalah tertentu berjalan lambat query, lihat artikel Basis Pengetahuan Microsoft berikut:
    243589 Bagaimana memecahkan masalah berjalan lambat query di SQL Server 7.0 atau versi yang lebih baru
    Untuk kinerja keseluruhan aplikasi pemecahan masalah, lihat artikel Basis Pengetahuan berikut:
    224587 CARA: Memecahkan masalah kinerja aplikasi dengan SQL Server
    Untuk informasi lebih lanjut, lihat Kinerja pemantauan dan Tuning How-to topik SQL Server 2008 buku Online topik di Website MSDN berikut:
    http://MSDN.Microsoft.com/en-us/library/ms187830.aspx
    Jika Anda memiliki pertanyaan berjalan lama yang menghalangi pengguna lain dan tidak dapat dioptimalkan, mempertimbangkan bergerak dari OLTP lingkungan untuk sistem pendukung keputusan.
  2. Memblokir disebabkan oleh SPID tidur yang kehilangan jejak dari tingkat Nesting transaksi

    Jenis ini menghalangi dapat sering diidentifikasi oleh SPID yang tidur atau menunggu perintah, namun transaksi yang bersarang tingkat (@@ TRANCOUNT, open_tran dari sysprocesses) lebih besar dari nol. Hal ini dapat terjadi jika aplikasi pengalaman permintaan timeout, atau masalah Batal tanpa juga mengeluarkan dibutuhkan sejumlah pernyataan ROLLBACK dan/atau KOMIT. Ketika SPID menerima permintaan timeout atau Batal, hal itu akan mengakhiri permintaan saat ini dan batch, tetapi secara otomatis memutar kembali atau melakukan transaksi. Aplikasi bertanggung jawab untuk ini, seperti SQL Server tidak bisa berasumsi bahwa seluruh transaksi harus digulung kembali hanya karena untuk permintaan tunggal yang dibatalkan. Query timeout atau Batal akan muncul sebagai acara sinyal perhatian untuk SPID di Profiler jejak.

    Untuk menunjukkan hal ini, masalah sederhana query berikut dari Query Analyzer:

    BEGIN TRAN 
    SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
    
    -- Issue this after canceling query
    SELECT @@TRANCOUNT
    ROLLBACK TRAN
    						
    Sementara query mengeksekusi, klik merah Batalkan tombol. Setelah permintaan dibatalkan, pilih @@ TRANCOUNT menunjukkan bahwa tingkat bersarang transaksi adalah salah satu. Ini telah menghapus atau UPDATE permintaan, atau HOLDLOCK telah digunakan pada pilih, semua kunci yang diperoleh akan masih akan diadakan. Bahkan dengan pertanyaan di atas, jika permintaan lain telah diperoleh dan memegang kunci sebelumnya dalam transaksi, mereka akan masih diadakan ketika di atas Pilih dibatalkan.

    Resolusi:

    • Aplikasi harus benar mengelola transaksi bersarang tingkat, atau mereka dapat menyebabkan masalah pemblokiran setelah pembatalan permintaan dengan cara ini. Hal ini dapat dicapai dalam beberapa cara:
      1. Dalam penangan kesalahan aplikasi klien, Kirim jika @@ TRANCOUNT mengatakan 0 ROLLBACK TRAN berikut kesalahan, bahkan jika aplikasi klien tidak percaya transaksi terbuka. Ini diperlukan, karena disimpan prosedur yang disebut selama bets bisa telah mulai transaksi tanpa pengetahuan aplikasi klien. Catatan yang tertentu kondisi, seperti membatalkan permintaan, mencegah prosedur mengeksekusi melewati pernyataan saat ini, jadi bahkan jika prosedur yang memiliki logika untuk memeriksa jika @@ KESALAHAN <> 0 dan abort transaksi, kode rollback ini tidak akan dijalankan dalam kasus tersebut.
      2. Menggunakan SET XACT_ABORT ON untuk sambungan, atau di disimpan prosedur yang mulai transaksi dan tidak membersihkan berikut kesalahan. Dalam hal terdapat galat run-time, pengaturan ini akan membatalkan setiap terbuka transaksi dan kembali kontrol untuk klien. Catatan bahwa pernyataan T-SQL Setelah pernyataan yang menyebabkan kesalahan tidak akan dijalankan.
      3. Jika koneksi penggabungan sedang digunakan dalam aplikasi yang membuka koneksi dan menjalankan sejumlah kecil pertanyaan sebelum melepaskan sambungan kembali ke kolam renang, seperti aplikasi berbasis Web, Sementara menonaktifkan koneksi penggabungan dapat membantu meringankan masalah sampai aplikasi klien yang dimodifikasi untuk menangani kesalahan yang tepat. Oleh menonaktifkan koneksi penggabungan, melepaskan sambungan akan menyebabkan fisik logout koneksi SQL Server, mengakibatkan server bergulir kembali setiap membuka transaksi.
      4. Jika koneksi penggabungan diaktifkan dan server tujuan adalah SQL Server 2000, upgrade komputer klien untuk MDAC 2.6 atau kemudian mungkin bermanfaat. Versi ini dari komponen MDAC menambahkan kode untuk ODBC driver dan penyedia OLE DB sehingga koneksi akan "reset" sebelum digunakan. Panggilan ini untuk sp_reset_connection aborts apapun memprakarsai server transaksi (DTC transaksi diprakarsai oleh aplikasi klien yang tidak terpengaruh), me-reset default database, SET pilihan, dan sebagainya. Catatan bahwa sambungan tidak reset sampai kembali dari kolam sambungan, Jadi sangat mungkin bahwa pengguna dapat membuka transaksi dan kemudian melepaskan sambungan ke koneksi kolam renang, tapi itu mungkin tidak dapat digunakan kembali untuk beberapa detik, selama waktu transaksi akan tetap terbuka. Jika sambungan adalah tidak kembali, transaksi akan dibatalkan ketika koneksi kali keluar dan akan dihapus dari kolam sambungan. Dengan demikian, itu optimal untuk klien aplikasi untuk membatalkan transaksi di handler kesalahan mereka atau menggunakan SET XACT_ABORT ON untuk menghindari penundaan ini potensi.
    • Sebenarnya, ini kelas masalah pemblokiran juga mungkin masalah kinerja, dan memerlukan Anda untuk mengejar seperti. Jika query waktu eksekusi dapat berkurang, permintaan timeout atau Batal tidak akan terjadi. Sangat penting bahwa aplikasi akan mampu menangani batas waktu atau membatalkan skenario harus mereka muncul, tetapi Anda juga dapat memperoleh manfaat dari memeriksa kinerja dari query.
  3. Memblokir disebabkan oleh SPID aplikasi klien yang bersangkutan tidak mengambil semua hasil baris untuk penyelesaian

    Setelah mengirim permintaan ke server, semua aplikasi harus segera mengambil semua hasil baris untuk penyelesaian. Jika sebuah aplikasi tidak tidak mengambil semua hasil baris, kunci dapat meninggalkan meja, menghalangi lain pengguna. Jika Anda menggunakan aplikasi yang transparan mengajukan SQL pernyataan ke server, aplikasi harus mengambil semua hasil baris. Jika itu tidak (dan jika tidak dikonfigurasi untuk melakukannya), Anda mungkin tidak dapat mengatasi masalah pemblokiran. Untuk menghindari masalah, Anda dapat membatasi berperilaku buruk aplikasi untuk pelaporan atau dukungan keputusan database.

    Resolusi:

    Aplikasi harus ditulis ulang untuk mengambil semua baris hasil penyelesaian.
  4. Memblokir disebabkan oleh kebuntuan klien/Server yang terdistribusi

    Tidak seperti kebuntuan konvensional, kebuntuan didistribusikan ini tidak dapat dideteksi menggunakan RDBMS lock manager. Hal ini disebabkan oleh kenyataan bahwa hanya satu dari sumber-sumber yang terlibat dalam kebuntuan adalah kunci SQL Server. The sisi lain dari kebuntuan adalah di level aplikasi klien, atas SQL yang Server tidak memiliki kontrol. Berikut adalah dua contoh bagaimana hal ini dapat terjadi, dan kemungkinan cara aplikasi dapat menghindarinya.

    1. Klien/Server didistribusikan kebuntuan dengan satu klien Benang
      Jika klien memiliki beberapa sambungan terbuka, dan satu benang eksekusi, kebuntuan didistribusikan berikut dapat terjadi. Untuk singkatnya, istilah "dbproc" digunakan di sini merujuk kepada struktur sambungan klien.

       SPID1------blocked on lock------->SPID2
        /\                         (waiting to write results         
        |                           back to client)
        |                                 |
        |                                 |                      Server side
        | ================================|==================================
        |     <-- single thread -->       |                      Client side
        |                                 \/ 
       dbproc1   <-------------------   dbproc2
       (waiting to fetch             (effectively blocked on dbproc1, awaiting
        next row)                     single thread of execution to run)
      								
      Dalam kasus yang ditunjukkan di atas, memiliki satu klien aplikasi thread dua membuka koneksi. Asynchronously mengajukan operasi SQL pada dbproc1. Ini berarti itu tidak menunggu panggilan untuk kembali sebelum melanjutkan. The aplikasi mengajukan lain SQL operasi di dbproc2, kemudian menunggu hasil untuk memulai pengolahan data kembali. Ketika data mulai kembali (dbproc mana pertama Tanggap--menganggap ini adalah dbproc1), proses untuk penyelesaian semua data kembali pada dbproc itu. Ini mengambil hasil dari dbproc1 sampai SPID1 akan diblokir pada kunci yang dipegang oleh SPID2 (karena dua Pertanyaan berjalan asynchronously pada server). Pada titik ini, dbproc1 akan menunggu selamanya untuk lebih banyak data. SPID2 tidak diblokir pada kunci, tetapi mencoba untuk mengirim data ke klien, dbproc2. Namun, dbproc2 secara efektif diblokir di dbproc1 pada lapisan aplikasi sebagai benang satu eksekusi untuk aplikasi ini digunakan oleh dbproc1. Hal ini mengakibatkan kebuntuan SQL Server tidak dapat mendeteksi atau menyelesaikan karena hanya salah satu sumber daya yang terlibat adalah SQL Sumber daya server.
    2. Klien/Server didistribusikan kebuntuan dengan benang per Sambungan

      Bahkan jika ada thread terpisah untuk setiap sambungan pada klien, variasi kebuntuan ini didistribusikan mungkin masih terjadi seperti yang ditunjukkan oleh berikut.

      SPID1------blocked on lock-------->SPID2
        /\                         (waiting on net write)        Server side
        |                                 |
        |                                 |
        | INSERT                          |SELECT
        | ================================|==================================
        |     <-- thread per dbproc -->   |                      Client side
        |                                 \/ 
       dbproc1   <-----data row-------   dbproc2
       (waiting on                     (blocked on dbproc1, waiting for it
        insert)                         to read the row from its buffer)
      								
      Kasus ini serupa dengan contoh A, kecuali dbproc2 dan SPID2 menjalankan sebuah pilih pernyataan dengan tujuan untuk melakukan baris pada waktu pengolahan dan menyerahkan setiap baris melalui penyangga untuk dbproc1 untuk MENYISIPKAN, PEMBARUAN, atau Hapus pernyataan di atas meja yang sama. Akhirnya, SPID1 (melakukan INSERT, UPDATE, atau menghapus) menjadi diblokir di kunci yang dipegang oleh SPID2 (melakukan pilih). SPID2 menulis hasil baris untuk klien dbproc2. Dbproc2 kemudian mencoba untuk lulus baris dalam buffer untuk dbproc1, tetapi menemukan dbproc1 sibuk (itu diblokir menunggu SPID1 untuk menyelesaikan INSERT saat ini, yang diblokir di SPID2). Pada titik ini, dbproc2 diblokir di lapisan aplikasi oleh dbproc1 yang SPID (SPID1) diblokir di database tingkat oleh SPID2. Sekali lagi, Hal ini menyebabkan kebuntuan yang SQL Server tidak dapat mendeteksi atau menyelesaikan karena hanya salah satu sumber daya yang terlibat adalah sumber SQL Server.
    Kedua contoh a dan b adalah dasar masalah yang pengembang aplikasi harus menyadari. Mereka harus kode aplikasi untuk menangani ini kasus yang tepat.

    Resolusi:

    Dua solusi yang dapat diandalkan adalah untuk menggunakan baik permintaan timeout atau terikat koneksi.

    • Permintaan Timeout
      Ketika permintaan timeout telah disediakan, jika kebuntuan didistribusikan terjadi, maka akan rusak ketika kemudian timeout terjadi. Melihat DB-perpustakaan atau ODBC dokumentasi untuk informasi lebih lanjut menggunakan permintaan timeout.
    • Terikat koneksi
      Fitur ini memungkinkan klien memiliki beberapa sambungan untuk mengikat mereka ke dalam ruang transaksi tunggal, jadi koneksi tidak memblokir satu sama lain. Untuk selengkapnya, lihat menggunakan" Terikat koneksi"topik dalam SQL Server 7.0 buku Online.
  5. Memblokir disebabkan oleh SPID yang ada di "emas", atau Rollback, negara

    Permintaan modifikasi data yang dibunuh, atau dibatalkan di luar dari transaksi yang ditetapkan pengguna, akan diluncurkan kembali. Hal ini juga dapat terjadi sebagai efek samping dari restart komputer klien dan sesi jaringan melepaskan. Demikian pula, permintaan dipilih sebagai korban kebuntuan akan diluncurkan kembali. Permintaan modifikasi data sering tidak dapat digulung balik lebih cepat daripada perubahan pada awalnya diterapkan. Sebagai contoh, jika menghapus, memasukkan, atau memperbarui pernyataan telah berjalan selama satu jam, itu bisa mengambil setidaknya satu jam untuk roll kembali. Ini adalah perilaku yang diharapkan, karena perubahan yang dibuat harus benar-benar diluncurkan kembali, atau transaksional dan fisik integritas dalam database akan dikompromikan. Karena hal ini harus terjadi, SQL Server menandai SPID dalam "emas" atau rollback negara (yang berarti itu tidak dapat membunuh atau dipilih sebagai jalan buntu korban). Ini dapat sering diidentifikasi dengan memperhatikan output sp_who, yang dapat menunjukkan perintah ROLLBACK. The Status kolom sys.sysprocesses akan menunjukkan status ROLLBACK, yang juga akan muncul di sp_who output atau SQL Server Management Studio Activity Monitor.
    Resolusi:

    Anda harus menunggu SPID untuk menyelesaikan bergulir kembali perubahan yang dibuat.

    Jika server dimatikan dalam operasi ini, database akan dalam modus pemulihan setelah restart, dan akan menjadi tidak dapat diakses hingga semua terbuka transaksi diproses. Permulaan Pemulihan diperlukan pada dasarnya sama dengan jumlah waktu per transaksi sebagai run-time pemulihan, dan database adalah tidak dapat diakses selama periode ini. Dengan demikian, memaksa server down untuk memperbaiki SPID dalam keadaan rollback akan sering kontraproduktif.

    Untuk menghindari situasi ini, tidak melakukan besar operasi INSERT, UPDATE, atau menghapus batch selama jam sibuk pada OLTP sistem. Jika mungkin, melakukan operasi tersebut selama periode aktivitas rendah.
  6. Memblokir disebabkan oleh koneksi yatim

    Jika perangkap aplikasi klien atau klien komputer di-restart, sesi jaringan ke server tidak dapat segera membatalkan beberapa kondisi. Dari perspektif server, klien masih muncul untuk hadir, dan kunci apapun memperoleh masih mungkin dipertahankan. Untuk informasi selengkapnya, klik nomor artikel berikut untuk melihat artikel di Pangkalan Pengetahuan Microsoft:
    137983Bagaimana memecahkan masalah koneksi yatim piatu di SQL Server

    Resolusi:

    Jika aplikasi klien telah terputus tanpa tepat membersihkan sumber daya, Anda dapat menghentikan SPID dengan menggunakan perintah KILL. Perintah KILL mengambil nilai SPID sebagai masukan. Sebagai contoh, untuk membunuh SPID 9, hanya mengeluarkan perintah berikut:

    KILL 9
    						

    Catatan Perintah KILL mungkin diperlukan waktu hingga 30 detik untuk menyelesaikan, karena untuk interval antara cek untuk perintah KILL.

Aplikasi keterlibatan dalam masalah pemblokiran

Mungkin ada kecenderungan untuk fokus pada sisi server tuning dan platform masalah ketika menghadapi masalah pemblokiran. Namun, hal ini biasanya tidak mengakibatkan resolusi, dan dapat menyerap waktu dan energi yang lebih baik diarahkan pada memeriksa aplikasi klien dan permintaan itu mengajukan. Tidak peduli apa tingkat visibilitas aplikasi memperlihatkan mengenai database panggilan yang dibuat, masalah pemblokiran tetap sering memerlukan pemeriksaan dari pernyataan SQL tepat yang dikirimkan oleh aplikasi dan aplikasi tepat perilaku mengenai permintaan pembatalan, pengelolaan koneksi, mengambil semua hasil baris, dan seterusnya. Jika alat pengembangan tidak memungkinkan eksplisit kontrol atas pengelolaan koneksi, permintaan pembatalan, permintaan timeout, hasil mengambil, dan sebagainya, menghalangi masalah tidak dapat diatasi. Potensi ini harus erat diperiksa sebelum memilih alat pengembangan aplikasi untuk SQL Server, terutama untuk bisnis penting OLTP lingkungan.

Itu penting bahwa perhatian besar dilakukan selama fase desain dan konstruksi database dan aplikasi. Secara khusus, konsumsi sumber daya, tingkat isolasi, dan transaksi jalan panjang harus dievaluasi untuk masing-masing permintaan. Setiap permintaan dan transaksi harus ringan mungkin. Bagus disiplin manajemen koneksi harus dilaksanakan. Jika hal ini tidak dilakukan, mungkin bahwa aplikasi akan muncul untuk memiliki kinerja yang dapat diterima di rendah jumlah pengguna, tetapi kinerja dapat menurunkan secara signifikan sebagai nomor pengguna skala ke atas.

Dengan penerapan dan permintaan desain, Microsoft SQL Server mampu mendukung banyak ribuan simultan pengguna pada sebuah server tunggal, dengan sedikit menghalangi.

Properti

ID Artikel: 224453 - Kajian Terakhir: 19 September 2011 - Revisi: 2.0
Berlaku bagi:
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Workgroup
Kata kunci: 
kbsqlsetup kbhowto kbtshoot kbexpertiseinter kbinfo kbmt KB224453 KbMtid
Penerjemahan Mesin
PENTING: Artikel ini diterjemahkan menggunakan perangkat lunak mesin penerjemah Microsoft dan bukan oleh seorang penerjemah. Microsoft menawarkan artikel yang diterjemahkan oleh seorang penerjemah maupun artikel yang diterjemahkan menggunakan mesin sehingga Anda akan memiliki akses ke seluruh artikel baru yang diterbitkan di Pangkalan Pengetahuan (Knowledge Base) dalam bahasa yang Anda gunakan. Namun, artikel yang diterjemahkan menggunakan mesin tidak selalu sempurna. Artikel tersebut mungkin memiliki kesalahan kosa kata, sintaksis, atau tata bahasa, hampir sama seperti orang asing yang berbicara dalam bahasa Anda. Microsoft tidak bertanggung jawab terhadap akurasi, kesalahan atau kerusakan yang disebabkan karena kesalahan penerjemahan konten atau penggunaannya oleh para pelanggan. Microsoft juga sering memperbarui perangkat lunak mesin penerjemah.
Klik disini untuk melihat versi Inggris dari artikel ini:224453

Berikan Masukan

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com