Cara mengawasi pemblokiran SQL Server 2005 dan di SQL Server 2000

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: 271509
Ringkasan
Jika Anda menggunakan Microsoft SQL Server 2000, Anda harus menerapkan Service Pack SQL Server 2000 3a (SP3a) sebelum Anda menggunakan skrip yang dijelaskan di artikel ini.

Untuk informasi selengkapnya tentang cara mendapatkan Service Pack SQL Server 2000 3a, klik nomor artikel berikut ini untuk melihat artikel di Pangkalan Pengetahuan Microsoft:
290211 Cara mendapatkan Service Pack SQL Server 2000
Artikel ini adalah pemutakhiran untuk SQL Server 2000 atau versi yang lebih baru dari SQL Server dari artikel Pangkalan Pengetahuan Microsoft berikut, yang berlaku untuk Microsoft SQL Server 7.0:
251004 Cara mengawasi pemblokiran SQL Server 7.0
Artikel ini mendokumentasikan penggunaan dan desain disimpan prosedur yang dapat Anda gunakan untuk mendiagnosis masalah pemblokiran dan kinerja. Untuk penjelasan tentang cara memahami dan menyelesaikan pemblokiran, lihat artikel berikut ini di Pangkalan Pengetahuan Microsoft:
224453 Memahami dan memecahkan masalah pemblokiran 2000 atau SQL Server 7.0
Microsoft SQL Server 2005, Anda juga dapat menggunakan kelas peristiwa Diblokir proses laporan di SQL Server Profiler untuk menangkap informasi tentang tugas yang telah diblokir untuk lebih dari jumlah waktu yang ditentukan.

Untuk informasi lebih lanjut tentang Diblokir proses laporan kejadian kelas, kunjungi situs web Microsoft Developer Network (MSDN) berikut:
Informasi lebih lanjut
Deskripsi berikut sp_blocker_pss08 disimpan prosedur menangkap informasi ini. Informasi ini juga berlaku untuk SQL Server 2005.
  • Waktu mulai (menurut ke komputer yang isrunning SQL Server) sehingga dapat sampling ini memblokir informasi kinerja withother rata waktu, seperti Microsoft Windows NT PerformanceMonitor log atau log SQL Profiler.
  • Informasi tentang koneksi ke SQL Server, dengan queryingthe sysprocesses Daftar Tabel sistem.
  • Informasi tentang sumberdaya bukti kunci, oleh Daftar Tabel pertanyaan sistem syslockinfo .
  • Informasi tentang sumber daya menunggu, dengan menjalankan DBCCSQLPERF(WAITSTATS).
  • Saat ini menjalankan SQL Server Batch untuk sambungan Yangyang diblokir oleh orang lain atau memblokir lainnya, dengan menjalankan DBCC INPUTBUFFERstatement.
  • Waktu berakhir, berdasarkan komputer itu runningSQL Server.
Prosedur tersimpan dibuat dengan optimasi berikut ini untuk mengurangi efek pada kinerja dan memblokir dengan menjalankan prosedur ini disimpan:
  • Output tidak dibuat kecuali ada sedikitnya satu connectionis menunggu pada sumber daya.
  • Daftar Tabel sistem sysprocesses dan syslockinfo dari pangkalan data master dipertanyakan secara langsung, untuk meningkatkan kinerja dan toprevent ini disimpan prosedur menjadi diblokir. Oleh karena itu, storedprocedure ini bersifat spesifik untuk Microsoft SQL Server 2000 atau versi yang lebih baru dari SQL Server.
  • tabel atak kerja kecil dibuat oleh kursor untuk mendapatkan theDBCC INPUTBUFFER output; ini harus efek tidak terlihat pada penggunaan dalam Pangkalan data tempdb .
  • Karena pemblokiran dapat mengubah sementara mengumpulkan theinformation, mode cepat ada, yang akan meningkatkan kinerja dengan mengurangi theresults relevan baris dari Daftar Tabel sistem sysprocesses dan syslockinfo .
  • Jika mencoba untuk melacak sumber daya non-lock menunggu, modeexists kait, yang menyebabkan bukti kunci output untuk dapat dihilangkan.
Prosedur ini disimpan ini berguna dengan menjalankan dari alat permintaan. Namun, Microsoft menyarankan Anda mengikuti langkah-langkah untuk melakukan analisis memblokir:
  1. Membuat prosedur tersimpan sp_blocker_pss08, yang disertakan di bagian akhir artikel ini, dari querytool setiap saat Anda tersambung dengan login yang memiliki hak sysadmin di server SQL Server atau contoh SQL Serveryou rencana untuk memantau.
  2. Buat berkas skrip dengan kueri berikut ini untuk menjalankan prosedur thestored di sebuah loop. Perhatikan bahwa penundaan harus antara 5 dan 60
    detik:

    SAAT 1 = 1
    MULAI
    EXEC tempdb.dbo.sp_blocker_pss08
    --Atau mode cepat
    --EXEC tempdb.dbo.sp_blocker_pss08 @fast = 1
    --Atau kait mode
    --EXEC tempdb.dbo.sp_blocker_pss08 @latch = 1
    PENUNDAAN WAITFOR ' 00: 00:15'
    AKHIR
    GO
  3. Output ini akan sangat bermanfaat ketika dikombinasikan dengan log MicrosoftWindows NT kinerja Monitor dan log SQL Profiler, sehingga membuat keduanya secara bersamaan disarankan. Untuk informasi mengenai peristiwa Monitor andPerformance Profiler mana untuk menangkap, dan informasi tentang bagaimana tointerpret hasil, lihat artikel berikut di Microsoft KnowledgeBase:
    224453Memahami dan memecahkan masalah pemblokiran 2000 atau SQL Server 7.0
  4. Jalankan berkas skrip yang dibuat di langkah 2 dari Isql.exe, alat permintaan Osql.exe, atau utilitas Sqlcmd di wantian perintah Windows di komputer yang menjalankan SQL Server yang Anda bermaksud untuk memantau untuk mencegah masalah jaringan melepaskan alat permintaan. Berikut ini adalah contoh baris perintah yang dapat Anda gunakan untuk memulai Osql.exe, menganggap bahwa klien dijalankan dari komputer yang menjalankan SQL Server, dan nama file skrip yang Checkblk.sql. Pastikan untuk mengoreksi parameter -S, dan mengganti "server" dengan nama SQL Server server (atau "servername\instance" jika Anda sedang memantau contoh yang disebutkan). Juga memperbaiki -i parameter, dan ganti "checkblk.sql" dengan garis jatuh berseri dan nama file skrip dibuat di langkah 2.
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
    Perhatikan bahwa Anda harus menggunakan switch baris perintah untuk thefollowing alasan:

    • Untuk mencegah Pembungkusan baris di file output yang membuatnya lebih mudah dibaca.
    • Mengirim output ke sebuah file yang ditentukan dengan parameter -o, bukan ke layar sehingga apabila alat permintaan memiliki masalah, Anda masih memiliki output hingga alat permintaan gagal.
Berikut adalah skrip untuk membuat prosedur sp_blocker_pss08 disimpan:
/*Note: This script is meant to have 3 creations of the same stored procedure and two of them will failwith either 207 errors or a 2714 error.*/use masterGOif exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss08') and sysstat & 0xf = 4)   drop procedure dbo.sp_blocker_pss08GOcreate procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')as --version 19.2005 - 2005 or Laterif is_member('sysadmin')=0 begin  print 'Must be a member of the sysadmin group in order to run this procedure'  returnendset nocount onSET LANGUAGE 'us_english'declare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @dbname nvarchar(128)declare @status sql_variantdeclare @useraccess sql_variantdeclare @request varchar(12)set @time = getdate()declare @probclients table(spid smallint, request_id int, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,   ignore_app tinyint, primary key (blocked, spid, request_id, ecid))insert @probclients select spid, request_id, ecid, blocked, waittype, dbid,   case when convert(varchar(128),hostname) = @appname then 1 else 0 end   from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000if exists (select spid from @probclients where ignore_app != 1)begin   set @time2 = getdate()   print ''   print '9.0 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.2005 '+ltrim(str(@latch))+' '+ltrim(str(@fast))    insert @probclients select distinct blocked, 0, 0, 0, 0x0000, 0, 0 from @probclients      where blocked not in (select spid from @probclients) and blocked != 0   if (@fast = 1)   begin      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage, last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121),net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid,         sql_handle, stmt_start, stmt_end, request_id      from master.dbo.sysprocesses      where blocked!=0 or waittype != 0x0000         or spid in (select blocked from @probclients where blocked != 0)         or spid in (select spid from @probclients where blocked != 0)      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select distinct spid from @probclients -- change: added distinct            where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0 and exists (select spid from @probclients where waittype between 0x0001 and 0x0017) -- Change: exists         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo s,               @probclients p            where p.spid = s.req_spid               --and ((p.waittype between 0x0001 and 0x0017) or ()) --change: added line            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end  -- fast set   else     begin  -- Fast not set      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage, last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121),net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid,         sql_handle, stmt_start, stmt_end, request_id      from master.dbo.sysprocesses      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from @probclients         where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end -- Fast not set   print 'sys.dm_os_wait_stats'   select * from sys.dm_os_wait_stats where waiting_tasks_count > 0   print 'OWS'   Print ''   Print '*********************************************************************'   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'   Print '*********************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (spid as varchar(6)) as spid, cast (request_id as varchar(12)) as request_id   from @probclients   where (spid <> @@spid) and (spid > 50) and      ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))      or spid in (select blocked from @probclients where blocked != 0))   open ibuffer   fetch next from ibuffer into @spid, @request   while (@@fetch_status != -1)   begin      print ''      print 'DBCC INPUTBUFFER FOR SPID ' + @spid +'('+@request+')'      exec ('dbcc inputbuffer (' + @spid + ',' + @request +')')      fetch next from ibuffer into @spid, @request   end   deallocate ibuffer   Print ''   Print '*******************************************************************************'   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'   Print '*******************************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (dbid as varchar(6)) from @probclients   where dbid != 0   open ibuffer   fetch next from ibuffer into @spid   while (@@fetch_status != -1)   begin      print ''      set @dbname = db_name(@spid)      set @status = DATABASEPROPERTYEX(@dbname,'Status')      set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'      if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'         dbcc opentran(@dbname)      else         print 'Skipped: Status=' + convert(nvarchar(128),@status)            + ' UserAccess=' + convert(nvarchar(128),@useraccess)      print ''      if @spid = '2' select @blocked = 'Y'      fetch next from ibuffer into @spid   end   deallocate ibuffer   if @blocked != 'Y'    begin      print ''      print 'DBCC OPENTRAN FOR DBID  2 [tempdb]'      dbcc opentran ('tempdb')   end   print 'End time: ' + convert(varchar(26), getdate(), 121)end -- Allelse  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '      + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2005'GO    create procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')as --version 19.20003 --2000 SP3 or Laterif is_member('sysadmin')=0 begin  print 'Must be a member of the sysadmin group in order to run this procedure'  returnendset nocount onSET LANGUAGE 'us_english'declare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @dbname nvarchar(128)declare @status sql_variantdeclare @useraccess sql_variantset @time = getdate()declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,   ignore_app tinyint, primary key (blocked, spid, ecid))insert @probclients select spid, ecid, blocked, waittype, dbid,   case when convert(varchar(128),hostname) = @appname then 1 else 0 end   from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)begin   set @time2 = getdate()   print ''   print '8.2 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.20003 ' +ltrim(str(@latch))+' '+ltrim(str(@fast))    insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients      where blocked not in (select spid from @probclients) and blocked != 0   if (@fast = 1)   begin      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage, last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121),net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid,         sql_handle, stmt_start, stmt_end      from master.dbo.sysprocesses      where blocked!=0 or waittype != 0x0000         or spid in (select blocked from @probclients where blocked != 0)         or spid in (select spid from @probclients where blocked != 0)      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from @probclients            where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo s,               @probclients p            where p.spid = s.req_spid            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end  -- fast set   else     begin  -- Fast not set      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage, last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121),net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid,         sql_handle, stmt_start, stmt_end      from master.dbo.sysprocesses      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from @probclients         where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end -- Fast not set   print 'DBCC SQLPERF(WAITSTATS)'   dbcc sqlperf(waitstats)   Print ''   Print '*********************************************************************'   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'   Print '*********************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (spid as varchar(6)) as spid   from @probclients   where (spid <> @@spid) and       ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))      or spid in (select blocked from @probclients where blocked != 0))   open ibuffer   fetch next from ibuffer into @spid   while (@@fetch_status != -1)   begin      print ''      print 'DBCC INPUTBUFFER FOR SPID ' + @spid      exec ('dbcc inputbuffer (' + @spid + ')')      fetch next from ibuffer into @spid   end   deallocate ibuffer   Print ''   Print '*******************************************************************************'   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'   Print '*******************************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (dbid as varchar(6)) from @probclients   where dbid != 0   open ibuffer   fetch next from ibuffer into @spid   while (@@fetch_status != -1)   begin      print ''      set @dbname = db_name(@spid)      set @status = DATABASEPROPERTYEX(@dbname,'Status')      set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'      if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'         dbcc opentran(@dbname)      else         print 'Skipped: Status=' + convert(nvarchar(128),@status)            + ' UserAccess=' + convert(nvarchar(128),@useraccess)      print ''      if @spid = '2' select @blocked = 'Y'      fetch next from ibuffer into @spid   end   deallocate ibuffer   if @blocked != 'Y'    begin      print ''      print 'DBCC OPENTRAN FOR DBID  2 [tempdb]'      dbcc opentran ('tempdb')   end   print 'End time: ' + convert(varchar(26), getdate(), 121)end -- Allelse  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '      + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.20003'GO    create procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')as --version 19.2000 -- 2000 before SP3if is_member('sysadmin')=0begin  print 'Must be a member of the sysadmin group in order to run this procedure'  returnendset nocount ondeclare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @dbname nvarchar(128)declare @status sql_variantdeclare @useraccess sql_variantset @time = getdate()declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,   ignore_app tinyint, primary key (blocked, spid, ecid))insert @probclients select spid, ecid, blocked, waittype, dbid,   case when convert(varchar(128),hostname) = @appname then 1 else 0 end   from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)begin   set @time2 = getdate()   print ''   print '8 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.2000 '+ltrim(str(@latch))+' '+ltrim(str(@fast))    insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients      where blocked not in (select spid from @probclients) and blocked != 0   if (@fast = 1)   begin      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage,last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121), net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid      from master.dbo.sysprocesses      where blocked!=0 or waittype != 0x0000         or spid in (select blocked from @probclients where blocked != 0)         or spid in (select spid from @probclients where waittype != 0x0000)      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from @probclients            where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo s,               @probclients p            where p.spid = s.req_spid            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end  -- fast set   else     begin  -- Fast not set      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage,last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121), net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid      from master.dbo.sysprocesses      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, ecid, waittype from @probclients where waittype != 0x0000      if exists(select blocked from @probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from @probclients         where blocked = 0 and spid in (select blocked from @probclients where spid != 0)         if @latch = 0         begin            print 'SYSLOCKINFO'            select @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID'                                    when 10 then 'APP' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'S'                                        when 5 then 'U'                                        when 6 then 'X'                                        when 7 then 'IS'                                        when 8 then 'IU'                                        when 9 then 'IX'                                        when 10 then 'SIU'                                        when 11 then 'SIX'                                        when 12 then 'UIX'                                        when 13 then 'BU'                                        when 14 then 'RangeS-S'                                        when 15 then 'RangeS-U'                                        when 16 then 'RangeIn-Null'                                        when 17 then 'RangeIn-S'                                        when 18 then 'RangeIn-U'                                        when 19 then 'RangeIn-X'                                        when 20 then 'RangeX-S'                                        when 21 then 'RangeX-U'                                        when 22 then 'RangeX-X'end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end,               req_transactionID As TransID, req_transactionUOW As TransUOW            from master.dbo.syslockinfo            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else        print 'No blocking via locks at ' + convert(varchar(26), @time, 121)      print ''   end -- Fast not set   print 'DBCC SQLPERF(WAITSTATS)'   dbcc sqlperf(waitstats)   Print ''   Print '*********************************************************************'   Print 'Print out DBCC Input buffer for all blocked or blocking spids.'   Print '*********************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (spid as varchar(6)) as spid   from @probclients   where (spid <> @@spid) and       ((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))      or spid in (select blocked from @probclients where blocked != 0))   open ibuffer   fetch next from ibuffer into @spid   while (@@fetch_status != -1)   begin      print ''      print 'DBCC INPUTBUFFER FOR SPID ' + @spid      exec ('dbcc inputbuffer (' + @spid + ')')      fetch next from ibuffer into @spid   end   deallocate ibuffer   Print ''   Print '*******************************************************************************'   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'   Print '*******************************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (dbid as varchar(6)) from @probclients   where dbid != 0   open ibuffer   fetch next from ibuffer into @spid   while (@@fetch_status != -1)   begin      print ''      set @dbname = db_name(@spid)      set @status = DATABASEPROPERTYEX(@dbname,'Status')      set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'      if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'         dbcc opentran(@dbname)      else         print 'Skipped: Status=' + convert(nvarchar(128),@status)            + ' UserAccess=' + convert(nvarchar(128),@useraccess)      print ''      if @spid = '2' select @blocked = 'Y'      fetch next from ibuffer into @spid   end   deallocate ibuffer   if @blocked != 'Y'    begin      print ''      print 'DBCC OPENTRAN FOR DBID  2 [tempdb]'      dbcc opentran ('tempdb')   end   print 'End time: ' + convert(varchar(26), getdate(), 121)end -- Allelse  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '     + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2000'GO 
Untuk informasi selengkapnya, kunjungi situs web berikut ini:

Peringatan: Artikel ini telah diterjemahkan secara otomatis

Properti

ID Artikel: 271509 - Tinjauan Terakhir: 11/01/2015 02:02:00 - Revisi: 4.0

Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2000 Personal Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 Workgroup Edition, Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Enterprise Edition

  • kbsqlsetup kbhowto kbinfo kbmt KB271509 KbMtid
Tanggapan