INF: Memahami dan memecahkan masalah pemblokiran SQL Server

PENTING: Artikel ini diterjemahkan oleh perangkat lunak penerjemahan mesin Microsoft, dan mungkin telah diedit oleh Masyarakat Microsoft melalui teknologi CTF dan bukan oleh seorang penerjemah profesional. Microsoft menawarkan baik artikel yang diterjemahkan oleh manusia maupun artikel hasil editan terjemahan oleh mesin/komunitas, sehingga Anda dapat mengakses semua artikel di Sentra Pengetahuan yang kami miliki dalam berbagai bahasa. Namun artikel hasil editan mesin atau bahkan komunitas tidak selalu sempurna. Artikel ini dapat mengandung kesalahan dalam hal kosa kata, sintaksis atau tatabahasa, sangat mirip dengan penutur asing yang membuat kekeliruan ketika berbicara dalam bahasa Anda. Microsoft tidak bertanggung jawab atas ketidakakuratan, kesalahan atau kerugian apa pun akibat dari kekeliruan dalam penerjemahan isi atau penggunaannya oleh pelanggan kami. Microsoft juga akan senantiasa memperbarui perangkat lunak penerjemahan mesin dan alat untuk menyempurnakan Editan Hasil Penerjemahan Mesin.

Klik disini untuk melihat versi Inggris dari artikel ini: 224453
Ringkasan
Di dalam artikel ini, istilah "sambungan" merujuk kepada satu log masuk 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 yang diperlukan untuk melayani permintaan sambungan tunggal dari klien tertentu dan sumber daya server. Satu klien aplikasi mungkin memiliki satu atau lebih banyak koneksi. Dari sudut pandang SQL Server, ada 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. Satu koneksi dapat memblokir koneksi lain, terlepas dari apakah mereka berasal dari aplikasi yang sama atau terpisah aplikasi pada dua komputer klien yang berbeda.
Informasi lebih lanjut
Pemblokiran adalah sifat dapat dihindari setiap database relasional manajemen sistem (RDBMS) dengan berbasis bukti kunci concurrency. Pada SQL Server, memblokir terjadi ketika salah satu SPID memegang bukti kunci pada sumber daya spesifik dan SPID kedua mencoba mendapatkan bukti kunci jenis konflik sumber daya yang sama. Biasanya, kerangka waktu yang SPID pertama mengunci sumber daya sangat kecil. Ketika melepaskan bukti kunci, sambungan kedua secara gratis untuk memperoleh yang memiliki bukti 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 bukti kunci yang disimpan dan dengan demikian, mereka berdampak pada permintaan lainnya. Jika query tidak dijalankan dalam transaksi (dan tidak ada petunjuk bukti kunci digunakan), bukti kunci untuk Pilih pernyataan hanya akan diadakan pada sumber daya saat ini adalah benar-benar membaca, bukan untuk durasi permintaan. Untuk menyisipkan, pembaruan, dan Hapus pernyataan, bukti 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 bukti kunci diadakan ditentukan oleh jenis permintaan, tingkat isolasi transaksi, dan apakah bukti kunci petunjuk yang digunakan dalam permintaan. Untuk keterangan mengunci, mengunci petunjuk dan transaksi isolasi tingkat, lihat topik berikut ini di buku daring SQL Server:
  • Mengunci di mesin database
  • Menyesuaikan mengunci dan versi baris
  • Mode bukti kunci
  • bukti kunci kompatibilitas mundur
  • Tingkat isolasi berbasis versi baris 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 dari alasan berikut ini:
  • SPID memegang bukti kunci pada set sumber daya untuk extendedperiod waktu sebelum melepaskan mereka. Jenis ini menghalangi menyelesaikan itselfover waktu, tetapi dapat menyebabkan penurunan kinerja.
  • SPID memegang bukti kunci pada set sumber daya dan tidak pernah releasesthem. Jenis ini menghalangi menyelesaikan sendiri dan mencegah akses ke sumber daya theaffected tanpa batas.
Dalam skenario pertama di atas, masalah pemblokiran menyelesaikan sendiri Dari Waktu ke waktu sebagai SPID melepaskan bukti 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 query. Situasi kedua hasil dalam keadaan 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. Skrip ini dapat memberikan snapshot contoh khusus Dari Waktu ke waktu, menuju keseluruhan gambar masalah. Untuk penjelasan tentang bagaimana memonitor menghalangi dengan skrip SQL, lihat artikel berikut ini di Pangkalan Pengetahuan Microsoft:
271509 Cara mengawasi pemblokiran SQL Server 2005 dan di SQL Server 2000
Skrip di artikel ini akan melakukan tugas di bawah ini. Jika memungkinkan, metode untuk mendapatkan informasi ini dari SQL Server Management Studio diberikan.
  1. Mengidentifikasi SPID (Session ID) kepala rantai memblokir dan pernyataan SQL.
    Selain menggunakan skrip di artikel Pangkalan Pengetahuan yang disebutkan sebelumnya, youcan 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-atas objek server, memperluas laporan, memperluas Laporan standar, dan kemudian klik kegiatan-semua transaksi yang Pemblokir. Laporan ini menunjukkan transaksi di kepala menghalangi jaringan. Jika Anda memperluas transaksi, laporan akan menunjukkan transaksi yang diblokir oleh kepala transaksi. Laporan ini juga akan menampilkan "Menghalangi SQL pernyataan" dan "Pernyataan SQL diblokir."
    • Menggunakan DBCC INPUTBUFFER (<spid>) untuk menemukan pernyataan terakhir yang dikirimkan oleh SPID.</spid>
  2. Menemukan tingkat bersarang transaksi dan status proses memblokir SPID.
    Transaksi yang bersarang tingkat SPID tersedia di the@@TRANCOUNT variabel global. Namun, dapat ditentukan dari luar theSPID oleh query sysprocesses Daftar Tabel sebagai berikut:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>go						
    Nilai kembali adalah nilai @@TRANCOUNT untuk SPID. Transaksi showsthe ini bersarang tingkat untuk memblokir SPID, yang dapat bergantian explainwhy itu memegang bukti kunci. Misalnya, jika nilai lebih besar dari nol, theSPID adalah di transaksi (di mana kasus diharapkan bahwa itretains tertentu bukti kunci itu telah diperoleh, tergantung pada transaksi isolationlevel).

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

Mengumpulkan informasi jejak SQL Server Profiler

Selain informasi di atas, hal ini sering diperlukan untuk merekam jejak Profiler kegiatan pada server untuk benar-benar menyelidiki masalah pemblokiran 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 bukti kunci masih ditahan. Jejak Profiler akan memungkinkan Anda untuk melihat semua perintah dijalankan oleh SPID dalam transaksi saat ini. Langkah-langkah berikut ini membantu Anda mengatur SQL Server Profiler untuk merekam jejak.
  1. Buka SQL Server Profiler.
  2. Pada File menu, arahkan ke baru, dan kemudian klik jejak.
  3. Pada tab umum , Tentukan nama jejak dan nama file untuk menangkap data.

    Penting Jejak file harus ditulis ke disk cepat lokal atau bersama. Menghindari pelacakan ke drive disk atau jaringan yang lambat. Selain itu, 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 Daftar Tabel 1 jejak Anda.

    Selain itu, Anda mungkin termasuk jenis acara tambahan yang tercantum dalam Daftar Tabel 2 untuk informasi lebih lanjut. Jika Anda menjalankan dalam lingkungan produksi volume tinggi, Anda dapat memutuskan untuk menggunakan hanya peristiwa dalam Daftar Tabel 1, seperti biasanya cukup untuk memecahkan masalah pemblokiran sebagian besar. Termasuk acara tambahan dalam Daftar Tabel 2 mungkin membuatnya lebih mudah untuk cepat menentukan sumber masalah (atau peristiwa ini mungkin diperlukan untuk mengidentifikasi penyebab pernyataan dalam prosedur multi pernyataan). Namun, termasuk peristiwa dalam Daftar Tabel 2 juga akan menambah beban pada sistem dan meningkatkan jejak output ukuran.
Daftar Tabel 1: Jenis peristiwa
JudulPeristiwa
Kesalahan dan peringatanPengecualian
Kesalahan dan peringatanPerhatian
Audit keamananAudit Login
Audit keamananAudit keluar
SesiAda sambungan
Prosedur tersimpanRPC: mulai
TSQLSQL:BatchStarting

Daftar Tabel 2: Jenis peristiwa tambahan
JudulPeristiwa
TransaksiDTCTransaction
TransaksiSQLTransaction
Prosedur tersimpanRPC: selesai
TSQLSQL:BatchCompleted
Prosedur tersimpanSP:StmtStarting
Prosedur tersimpanSP:StmtCompleted

Untuk informasi lebih lanjut tentang menggunakan SQL Server Profiler, silakan lihat Pemesanan daring SQL Server.

Mengidentifikasi dan memecahkan skenario memblokir Umum

Dengan memeriksa informasi di atas, Anda dapat menentukan penyebab masalah pemblokiran sebagian besar. Seluruh artikel ini adalah diskusi tentang cara menggunakan informasi ini untuk mengidentifikasi dan menyelesaikan beberapa skenario memblokir umum. Diskusi ini menganggap Anda telah menggunakan skrip memblokir dalam artikel 271509 (direferensikan sebelumnya) untuk menangkap informasi memblokir SPIDs dan telah membuat jejak Profiler dengan 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" thatlists SPIDs yang menghalangi lain SPIDs dalam naskah output.
SPIDs at the head of blocking chains
Apabila Anda menentukan pilihan cepat, Anda masih dapat menentukan theblocking 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 memblokir.
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. Runnable, menjalankan, atau sos_scheduler_yield status menunjukkan bahwa SPID saat memproses permintaan. Daftar Tabel berikut ini memberikan penjelasan singkat tentang status berbagai nilai.
StatusMakna
Latar belakangSPID menjalankan latar belakang, seperti deteksi kertas macet.
TidurSPID tidak sedang menjalankan. Hal 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 irisan waktu di scheduler untuk mengizinkan SPID lain untuk memperoleh waktu scheduler.
DitangguhkanSPID adalah menunggu peristiwa, seperti bukti kunci atau kait.
Putar kembaliSPID adalah dalam rollback transaksi.
DefwakeupMenunjukkan bahwa SPID menunggu untuk sumber daya yang sedang dalam proses dibebaskan. Bidang waitresource harus menunjukkan sumber daya yang dimaksud.

Open_tran

Kolom ini memberitahu Anda transaksi yang bersarang tingkat SPID. Jika nilai ini lebih besar dari 0, SPID dalam transaksi terbuka dan mungkin memegang bukti kunci diakuisisi oleh pernyataan dalam transaksi.

Lastwaittype, waittype, dan waittime

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 lastwaittype nilai menunjukkan terakhir waittype bahwa SPID telah. Jika waittype bukan nol, lastwaittype nilai menunjukkan saat ini waittype dari SPID.

Untuk deskripsi singkat yang berbeda lastwaittype dan waittype nilai-nilai, lihat artikel berikut ini di Pangkalan Pengetahuan Microsoft:
822101 Deskripsi waittype dan lastwaittype kolom dalam Daftar Tabel Master.DBO.sysprocesses di SQL Server 2000 dan SQL Server 2005
Untuk informasi lebih lanjut tentang sys.dm_os_wait_stats, lihat Pemesanan daring SQL Server.

Waittime nilai dapat digunakan untuk menentukan jika SPID adalah membuat kemajuan. Ketika permintaan terhadap sys.sysprocesses Daftar Tabel gulung balik nilai dalam waittime kolom yang kurang dari waittime nilai dari permintaan sebelumnya dari sys.sysprocesses, ini menunjukkan bahwa bukti kunci sebelumnya diperoleh dan dirilis dan sekarang menunggu pada bukti kunci baru (dengan asumsi bukan nol waittime). Ini akan diverifikasi dengan membandingkan waitresource antara sys.sysprocesses output.

Waitresource

Kolom ini menunjukkan sumber bahwa SPID adalah menunggu. Daftar Tabel berikut mencantumkan umum waitresource format dan makna mereka:
Sumber dayaFormatContoh
Daftar TabelDatabaseID:ObjectID:IndexIDTAB: 5:261575970:1
Dalam kasus ini, database ID 5 adalah contoh database pub adalah objek ID 261575970 judul Daftar Tabel dan 1 adalah indeks kluster.
HalamanDatabaseID:FileID:PageIDHALAMAN: 5:1:104
Dalam kasus ini, database ID 5 adalah pub, file ID 1 adalah file data primer, dan halaman 104 adalah halaman milik judul Daftar 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 )
bukti kunciDatabaseID: hobt_id (nilai Hash indeks Key)KUNCI: 5:72057594044284928 (3300a4f361aa)

Dalam kasus ini, database ID 5 pub, Hobt_ID 72057594044284928 sesuai dengan non kluster 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 bukti kunci indeks nilai bukti kunci indeks tertentu.
BarisDatabaseID:FileID:PageID:Slot(row)RID: 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 Daftar Tabel dan slot 3 menunjukkan baris posisi pada halaman.
MenghimpunDatabaseID: ObjectID [[kompilasi]]TAB: 834102012 [[kompilasi]] ini bukanlah bukti kunci Daftar Tabel, tetapi memilih menghimpun bukti kunci pada prosedur yang disimpan. Database ID 5 pub, objek ID 834102012 usp_myprocedure disimpan prosedur. Lihat artikel Pangkalan Pengetahuan 263889 untuk informasi lebih lanjut mengenai pemblokiran disebabkan oleh kompilasi bukti kunci.
Kolom lain

Sisa sys.sysprocesses kolom dapat menyediakan wawasan akar masalah juga. Kegunaan mereka bervariasi tergantung pada keadaan masalah. Misalnya, 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 sebagainya.
Periksa DBCC INPUTBUFFER output.
Untuk setiap SPID di kepala rantai memblokir atau dengan anon nol waittype, menghalangi script akan mengeksekusi DBCC INPUTBUFFER todetermine permintaan saat ini untuk SPID itu.

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

Catatan Karena script memblokir terdiri dari beberapa langkah ini ispossible bahwa SPID mungkin muncul di bagian pertama sebagai kepala blockingchain, namun saat DBCC INPUTBUFFER query dijalankan, tidak ada longerblocking dan INPUTBUFFER tidak ditangkap. Ini menunjukkan bahwa blockingis menyelesaikan sendiri untuk SPID itu dan mungkin atau mungkin tidak masalah. Di thispoint, Anda dapat menggunakan versi cepat memblokir script untuk mencoba toensure Anda menangkap inputbuffer sebelum Bersihkan (meskipun masih ada noguarantee), atau melihat Profiler data dari kerangka waktu itu untuk menentukan whatqueries SPID eksekusi.

Melihat Profiler Data

Melihat Profiler data efisien sangat berharga dalam menyelesaikan masalah pemblokiran. Yang paling penting untuk memahami bahwa Anda tidak perlu melihat semua Anda ditangkap; selektif. Profiler menyediakan kemampuan untuk membantu Anda efektif melihat data ditangkap. Di kotak dialog properti (pada File menu, klik properti), Profiler memungkinkan Anda untuk membatasi data ditampilkan dengan menghapus data kolom atau peristiwa, pengelompokan (penyortiran) oleh bidang data dan menerapkan filter. Anda dapat Telisik jejak seluruh atau hanya kolom khusus untuk khusus nilai (pada Edit menu, klik menemukan). Anda juga dapat menyimpan Profiler data Daftar Tabel SQL Server (pada File menu, arahkan ke Simpan sebagai dan kemudian klik Daftar Tabel) dan menjalankan SQL query terhadap itu.

Berhati-hatilah bahwa Anda melakukan penyaringan hanya pada berkas jejak disimpan sebelumnya. Apabila Anda melakukan langkah-langkah berikut pada jejak aktif, Anda risiko kehilangan data yang telah ditangkap karena jejak dimulai. Simpan aktif telusuri paling detail ke file atau Daftar Tabel pertama (pada File menu, klik Simpan sebagai) dan kemudian buka kembali (pada File menu, klik terbuka) sebelum melanjutkan. Ketika bekerja pada file disimpan jejak, penyaringan tidak secara permanen menghapus data yang disaring, hanya tidak menampilkan semua data. Anda dapat menambah dan menghapus peristiwa dan bidang data yang diperlukan untuk membantu fokus pencarian Anda.

Apa yang harus mencari:
  • Apa perintah telah SPID kepala chainexecuted memblokir dalam transaksi saat ini?
    Menyaring jejak data untuk aparticular SPID yang ada di kepala rantai memblokir (pada File menu, klik properti; kemudian pada filter tab menentukan nilai SPID). Anda kemudian dapat memeriksa perintah ithas dijalankan sebelum ke waktu itu menghalangi SPIDs lain. Jika Anda memasukkan theTransaction kejadian, mereka dapat dengan mudah mengidentifikasi ketika transaksi dimulai.Jika tidak, Anda dapat Telisik teks kolom dimulai, Simpan, COMMIT, atau ROLLBACK TRANSACTIONoperations. Gunakan open_tran nilai dari sysprocesses Daftar Tabel untuk memastikan bahwa Anda menangkap semua peristiwa transaksi.Mengetahui perintah dieksekusi dan konteks transaksi akan memungkinkan Anda todetermine mengapa SPID memegang bukti kunci.

    Ingat, Anda dapat removeevents dan data kolom. Alih-alih melihat dimulai dan completedevents, pilih salah satu. Jika memblokir SPIDs tidak disimpan prosedur, HapusSP: mulai atau SP: selesai peristiwa; SQLBatch dan RPC acara akan menunjukkan prosedur panggilan. Hanya melihat peristiwa SP apabila perlu untuk melihat bahwa tingkat detail.
  • Apa yang dimaksud dengan durasi permintaan untuk SPIDs di headof menghalangi rantai?
    Jika Anda menyertakan acara selesai di atas, durasi kolom akan menunjukkan waktu eksekusi query. Ini dapat membantu youidentify berjalan lama query yang menyebabkan menghalangi. Untuk menentukan mengapa thequery kinerja lambat, lihat CPU, membaca, menulis kolom, serta Rencana pelaksanaan acara.

Kategorisasi skenario memblokir Umum

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

SkenarioWaittypeOpen_tranStatusMenyelesaikan?Gejala lain
1Non-nolmengatakan = 0RunnableYa, ketika permintaan selesai.Physical_IO, CPU dan/atau Memusage kolom akan meningkatkan waktu. Durasi untuk permintaan akan tinggi ketika selesai.
20x0000mengatakan 0tidurTidak, tetapi SPID dapat mati.Perhatian sinyal dapat dilihat di Profiler jejak untuk SPID ini, menunjukkan permintaan timeout atau Batal telah terjadi.
30x0000mengatakan = 0RunnableNomor Tidak akan memecahkan sampai klien mengambil semua baris atau menutup sambungan. SPID dapat mati, tapi mungkin diperlukan waktu hingga 30 detik.Jika open_tran = 0, dan SPID memegang bukti kunci sementara isolasi transaksi tingkat default (Baca COMMMITTED), ini adalah penyebab yang mungkin.
4Berbeda-bedamengatakan = 0RunnableNomor Tidak akan memecahkan sampai klien membatalkan permintaan atau menutup sambungan. SPIDs dapat mati, tapi mungkin diperlukan waktu hingga 30 detik.Nama host kolom di sysprocesses untuk SPID di kepala rantai memblokir akan sama dengan salah satu SPID yang menghalangi.
50x0000mengatakan 0Putar kembaliYa.Perhatian sinyal dapat dilihat di Profiler jejak untuk SPID ini, menunjukkan permintaan timeout atau Batal telah terjadi, atau hanya sebuah pernyataan rollback telah dikeluarkan.
60x0000mengatakan 0tidurAkhirnya. Ketika Windows NT menentukan sesi tidak lagi aktif, SQL Server sambungan akan rusak.Last_batch nilai dalam sysprocesses lebih lawas Dari Waktu saat ini.

Umum menghalangi skenario dan resolusi

Skenario yang tercantum di bawah ini akan memiliki karakter yang tercantum dalam Daftar 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:
    Pemecahan Masalah memblokir jenis ini adalah untuk melihat forways untuk mengoptimalkan query. Sebenarnya, ini kelas masalah pemblokiran mungkin justbe masalah kinerja, dan mengharuskan Anda untuk melanjutkan seperti itu. Untuk informationon pemecahan masalah tertentu berjalan lambat query, lihat artikel Pangkalan Pengetahuan Microsoft berikut ini:
    243589 Cara memecahkan masalah berjalan lambat query di SQL Server 7.0 atau versi yang lebih baru
    Untuk performancetroubleshooting keseluruhan aplikasi, lihat artikel Pangkalan Pengetahuan berikut:
    224587 CARA: Memecahkan masalah kinerja aplikasi dengan SQL Server
    Untuk informasi selengkapnya, lihat kinerja pemantauan dan Tuning How-to topik SQL Server 2008 buku Online topik di website MSDN berikut: Jika Anda memiliki pertanyaan berjalan lama yang blockingother pengguna dan tidak dapat dioptimalkan, pertimbangkan memindahkan dari OLTPenvironment ke sistem dukungan keputusan.
  2. Memblokir disebabkan oleh SPID tidur yang kehilangan jejak tingkat bersarang transaksi

    Jenis ini menghalangi dapat sering diidentifikasi oleh SPIDthat tidur atau menunggu perintah, namun yang transaksi bersarang tingkat (@@TRANCOUNT, open_tran dari sysprocesses) lebih besar dari nol. Hal ini dapat terjadi jika applicationexperiences permintaan timeout, atau masalah Batalkan tanpa juga penerbit therequired jumlah pernyataan ROLLBACK dan/atau COMMIT. Ketika SPID menerima aquery timeout atau Batal, hal itu akan mengakhiri permintaan saat ini dan batch, butdoes tidak secara otomatis kembali atau melakukan transaksi. Isresponsible aplikasi ini, seperti SQL Server tidak dapat menganggap bahwa seluruh transactionmust dapat dibatalkan hanya karena satu permintaan dibatalkan. Querytimeout atau membatalkan akan muncul sebagai peristiwa sinyal perhatian untuk SPID di jejak theProfiler.

    Untuk menunjukkan hal ini, masalah queryfrom sederhana berikut Query Analyzer:

    BEGIN TRAN SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2-- Issue this after canceling querySELECT @@TRANCOUNTROLLBACK TRAN						
    Sementara query mengeksekusi, klik merah Batalkan tombol tekan. Setelah permintaan dibatalkan, pilih @@TRANCOUNT indicatesthat tingkat bersarang transaksi adalah salah satu. Ini telah menghapus atau UPDATEquery, atau HOLDLOCK telah digunakan pada pilih, Semua bukti kunci yang diperoleh wouldstill diadakan. Bahkan dengan pertanyaan di atas, jika permintaan lain telah diperoleh andheld bukti kunci sebelumnya dalam transaksi, mereka akan masih diadakan ketika aboveSELECT 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 salah satu dari beberapa cara:
      1. Dalam penangan kesalahan aplikasi klien, kirim jika @@TRANCOUNT mengatakan 0 ROLLBACK TRAN berikut kesalahan, bahkan jika aplikasi klien tidak yakin transaksi yang terbuka. Hal ini diperlukan, karena disimpan prosedur yang disebut selama batch dapat mulai transaksi tanpa pengetahuan aplikasi klien. Perhatikan bahwa kondisi tertentu, seperti membatalkan permintaan, mencegah prosedur mengeksekusi melewati pernyataan saat ini, jadi bahkan jika prosedur yang memiliki logika untuk memeriksa jika @@ERROR <> 0 dan membatalkan 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 Bersihkan berikut kesalahan. Apabila terjadi galat run-time, pengaturan ini akan membatalkan setiap terbuka transaksi dan gulung balik kontrol ke klien. Perhatikan 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 pool, seperti aplikasi berbasis Web, sementara menonaktifkan koneksi penggabungan dapat membantu mengatasi masalah sampai aplikasi klien yang dimodifikasi untuk menangani kesalahan yang tepat. Dengan menonaktifkan koneksi penggabungan, melepaskan sambungan akan menyebabkan keluar fisik 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 diinisiasi server transaksi (DTC transaksi diawali dengan aplikasi klien yang tidak terpengaruh), me-reset default database, SET pilihan, dan sebagainya. Perhatikan bahwa sambungan tidak reset sampai kembali dari kumpulan sambungan, jadi mungkin bahwa pengguna dapat membuka transaksi dan kemudian melepaskan sambungan ke pool sambungan, tapi mungkin tidak dapat digunakan untuk beberapa detik, selama waktu transaksi akan tetap terbuka. Jika sambungan tidak kembali, transaksi akan dibatalkan apabila sambungan waktu habis dan dihapus dari kumpulan 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 mungkin juga masalah kinerja, dan mengharuskan Anda untuk melanjutkan seperti itu. Jika waktu eksekusi query dapat berkurang, permintaan timeout atau Batal tidak akan terjadi. Penting bahwa aplikasi akan mampu menangani batas waktu atau membatalkan skenario harus mereka muncul, tetapi Anda juga dapat mengambil manfaat dari memeriksa kinerja permintaan.
  3. Memblokir disebabkan oleh SPID aplikasi klien yang bersangkutan tidak mengambil semua hasil baris untuk penyelesaian

    Setelah mengirim permintaan ke server, Semua applicationsmust segera mengambil semua hasil baris untuk penyelesaian. Jika aplikasi sehingga mengambil semua hasil baris, bukti kunci dapat meninggalkan tabel atak, menghalangi otherusers. Jika Anda menggunakan aplikasi yang transparan mengajukan SQLstatements ke server, aplikasi harus mengambil semua hasil baris. Jika itdoes tidak (dan jika tidak dikonfigurasi untuk melakukannya), Anda mungkin dapat toresolve masalah pemblokiran. Untuk menghindari masalah, Anda dapat berperilaku restrictpoorly aplikasi untuk pelaporan atau supportdatabase keputusan.

    Resolusi:

    Aplikasi harus ditulis ulang untuk mengambil semua baris hasil penyelesaian.
  4. Memblokir disebabkan oleh klien/Server didistribusikan kemogokan

    Tidak seperti biasa kemogokan, deadlockis terdistribusi tidak terdeteksi menggunakan RDBMS bukti kunci manager. Hal ini disebabkan oleh fakta thatonly salah satu dari sumber daya yang terlibat dalam kertas macet adalah bukti kunci SQL Server. Sisi Theother kemogokan adalah di tingkat aplikasi klien, di mana SQLServer tidak memiliki kontrol. Berikut ini adalah dua contoh bagaimana hal ini dapat terjadi, dan kemungkinan cara aplikasi dapat menghindari hal itu.

    1. Klien/Server didistribusikan kemogokan dengan satu klien benang
      Jika klien memiliki beberapa sambungan terbuka, dan satu benang eksekusi, kemogokan didistribusikan berikut ini mungkin 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 ditampilkan di atas, satu klien aplikasi thread memiliki dua membuka koneksi. Asinkron mengajukan operasi SQL pada dbproc1. Ini berarti itu tidak menunggu panggilan untuk kembali sebelum melanjutkan. Aplikasi mengajukan lain SQL operasi di dbproc2, kemudian menunggu hasil untuk memulai pemrosesan data kembali. Ketika data mulai kembali (dbproc pertama merespons--menganggap ini adalah dbproc1), proses untuk penyelesaian semua data kembali pada dbproc itu. Mengambil hasil dari dbproc1 sampai SPID1 akan diblokir pada bukti kunci dilaksanakan SPID2 (karena dua pertanyaan berjalan asinkron pada server). Pada titik ini, dbproc1 akan menunggu selamanya untuk lebih banyak data. SPID2 tidak diblokir pada bukti 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 menyebabkan kertas macet yang SQL Server tidak dapat mendeteksi atau menyelesaikan karena hanya salah satu sumber daya yang terlibat adalah sumber SQL Server.
    2. Klien/Server didistribusikan kemogokan dengan benang per sambungan

      Bahkan jika ada thread terpisah untuk setiap sambungan pada klien, variasi kemogokan 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)								
      Hal ini sangat mirip 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 pada Daftar Tabel yang sama. Akhirnya, SPID1 (melakukan INSERT, UPDATE, atau menghapus) menjadi diblokir pada bukti kunci dilaksanakan 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 kertas macet 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 thatapplication pengembang harus berhati-hatilah atas. Mereka harus kode aplikasi untuk kasus handlethese dengan benar.

    Resolusi:

    Dua andal solusi yang menggunakan querytimeout atau terikat koneksi.

    • Permintaan Timeout
      Ketika permintaan timeout telah disediakan, jika terjadi kertas macet terdistribusi, maka akan rusak ketika kemudian timeout terjadi. Lihat DB-perpustakaan atau ODBC dokumentasi untuk informasi lebih lanjut tentang 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 informasi selengkapnya, lihat topik "Menggunakan terikat sambungan" di SQL Server 7.0 buku Online.
  5. Memblokir disebabkan oleh SPID yang ada di "Emas", atau Rollback, negara

    Permintaan modifikasi data yang membunuh atau canceledoutside transaksi yang ditetapkan pengguna, akan diluncurkan kembali. Hal ini juga dapat occuras efek samping dari restart komputer klien dan yang sessiondisconnecting jaringan. Demikian pula, permintaan dipilih sebagai korban kemogokan akan rolledback. Permintaan modifikasi data sering tidak dapat digulung balik lebih cepat daripada thechanges pada awalnya diterapkan. Sebagai contoh, jika menghapus, memasukkan, atau UPDATEstatement telah berjalan selama satu jam, itu bisa mengambil setidaknya satu jam untuk rollback. Ini adalah perilaku yang diharapkan, karena perubahan yang dibuat harus completelyrolled kembali, atau transaksional dan fisik integritas dalam database akan becompromised. Karena ini harus terjadi, SQL Server menandai SPID dalam keadaan "emas" atau rollback (yang berarti tidak mati atau dipilih sebagai deadlockvictim). Ini dapat sering diidentifikasi dengan memperhatikan output sp_who, yang dapat menunjukkan perintah ROLLBACK. 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 thechanges yang telah dibuat.

    Jika server dimatikan di tengah-tengah ofthis operasi, database akan dalam modus pemulihan setelah restart, dan itwill menjadi tidak dapat diakses hingga semua terbuka transaksi diproses. Startuprecovery mengambil pada dasarnya sama dengan jumlah waktu per transaksi sebagai run-timerecovery, dan pangkalan data tidak dapat diakses selama periode ini. Dengan demikian, forcingthe server untuk memperbaiki SPID dalam keadaan rollback akan sering becounterproductive.

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

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

    Resolusi:

    Jika aplikasi klien telah terputus withoutappropriately Bersihkan sumber daya, Anda dapat menghentikan SPID dengan perintah KILL usingthe. Perintah KILL mengambil nilai SPID sebagai masukan. Misalnya, untuk menghentikan SPID 9, hanya mengeluarkan perintah berikut ini:

    KILL 9						

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

Aplikasi keterlibatan dalam masalah pemblokiran

Mungkin ada kecenderungan untuk fokus pada sisi server tuning dan platform masalah ketika hadap atas 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. Tanpa memperhatikan 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 sebagainya. Jika alat pengembangan tidak memungkinkan eksplisit kontrol atas pengelolaan koneksi, permintaan pembatalan, permintaan timeout, hasil mengambil, dan sebagainya, masalah pemblokiran tidak dapat diatasi. Potensi ini harus sama diperiksa sebelum memilih alat pengembangan aplikasi untuk SQL Server, terutama untuk bisnis penting OLTP lingkungan.

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

Dengan penerapan dan desain permintaan, Microsoft SQL Server mampu mendukung banyak ribuan serentak di server tunggal, dengan sedikit menghalangi.

Peringatan: Artikel ini telah diterjemahkan secara otomatis

Properti

ID Artikel: 224453 - Tinjauan Terakhir: 03/15/2015 05:39:00 - Revisi: 3.0

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

  • kbsqlsetup kbhowto kbtshoot kbexpertiseinter kbinfo kbmt KB224453 KbMtid
Tanggapan