วิธีการตรวจสอบการบล็อก ใน SQL Server 2005 และ SQL Server 2000

การแปลบทความ การแปลบทความ
หมายเลขบทความ (Article ID): 271509 - ผลิตภัณฑ์ที่เกี่ยวข้องในบทความนี้
ขยายทั้งหมด | ยุบทั้งหมด

สรุป

ถ้าคุณกำลังใช้ Microsoft SQL Server 2000 คุณต้องใช้ SQL Server 2000 Services Pack 3a (SP3a) ก่อนที่คุณใช้สคริปต์ที่อธิบายไว้ในบทความนี้

สำหรับข้อมูลเพิ่มเติมเกี่ยวกับวิธีการขอรับ SQL Server 2000 Service Pack 3a คลิกหมายเลขบทความต่อไปนี้เพื่อดูบทความใน Microsoft Knowledge Base:
290211วิธีการขอรับ service pack ล่าสุดของ SQL Server 2000
บทความนี้เป็นการปรับปรุงสำหรับ SQL Server 2000 หรือรุ่นที่ใหม่กว่าของ SQL Server ของ การต่อไปนี้ Microsoft บทความ knowledge Base ซึ่งนำไปใช้กับ Microsoft SQL เซิร์ฟเวอร์ 7.0:
251004 วิธีการตรวจสอบ SQL Server 7.0 บล็อก
บทความนี้เอกสารที่ใช้งานและการออกแบบของแบบ ขั้นตอนที่คุณสามารถใช้ในการวินิจฉัยปัญหาการบล็อกและประสิทธิภาพการทำงาน ที่เก็บไว้ คำอธิบายของวิธีการทำความเข้าใจ และการแก้ปัญหาการบล็อก โปรดดู บทความใน Microsoft Knowledge Base:
224453 ทำความเข้าใจ และการแก้ปัญหาการบล็อกของ 2000 หรือ SQL Server 7.0
ใน Microsoft SQL Server 2005 คุณยังสามารถใช้ รายงานการประมวลผลที่ถูกบล็อค คลาสเหตุการณ์ของในเว็บไซต์ของผู้สร้างโปรไฟล์ SQL Server เพื่อจับภาพรายละเอียดเกี่ยวกับงานที่ถูกบล็อคสำหรับข้อมูลเพิ่มเติมนอกเหนือจากจำนวนเวลาที่ระบุ

สำหรับข้อมูลเพิ่มเติมเกี่ยวกับการ รายงานการประมวลผลที่ถูกบล็อค เหตุการณ์คลาส เยี่ยมชมเว็บไซต์ต่อไปนี้ของ Microsoft สำหรับนักพัฒนาเครือข่าย (MSDN):
http://msdn2.microsoft.com/en-us/library/ms191168.aspx

ข้อมูลเพิ่มเติม

คำอธิบายดังต่อไปนี้ของ sp_blocker_pss08 กระบวนงานที่เก็บไว้จับข้อมูลนี้ ข้อมูลนี้ยังใช้เป็น SQL Server 2005
  • เวลาเริ่มต้น (ตามไปยังคอมพิวเตอร์เครื่องที่อยู่ การใช้งาน SQL Server) เพื่อให้การสุ่มตัวอย่างนี้ของบล็อกสามารถถูกจัดตำแหน่งชิดเวลาด้วย ข้อมูลด้านประสิทธิภาพอื่น ๆ เช่นประสิทธิภาพการตั้งค่า Microsoft Windows NT ตรวจสอบแฟ้มบันทึกหรือแฟ้มบันทึกของผู้สร้างโปรไฟล์ของ SQL
  • ข้อมูลเกี่ยวกับการเชื่อมต่อกับ SQL Server ด้วยการสอบถาม กระบวนการ sysprocesses ตารางของระบบ
  • ข้อมูลเกี่ยวกับการล็อคทรัพยากร ด้วยการสอบถาม syslockinfo ตารางของระบบ
  • ข้อมูลเกี่ยวกับทรัพยากรรอ ด้วยการเรียกใช้การ DBCC SQLPERF(WAITSTATS)
  • ปัจจุบันเรียกใช้ชุดงานของเซิร์ฟเวอร์ SQL สำหรับการเชื่อมต่อที่ ถูกบล็อก โดยผู้ใช้คนอื่น หรือบล็อกผู้อื่น โดยการเรียกใช้ INPUTBUFFER การ DBCC คำชี้แจง
  • เวลาสิ้นสุด ตามไปยังคอมพิวเตอร์ ที่กำลังเรียกใช้ SQL Server
สร้างกระบวนงานที่เก็บไว้กับข้อความต่อไปนี้ optimizations เพื่อลดผลกระทบต่อประสิทธิภาพการทำงานและบล็อกได้ โดยการรันงานนี้ กระบวนงานที่เก็บไว้:
  • ไม่มีผลผลิตไม่มีการสร้างขึ้นเว้นแต่ว่าค่าอย่างน้อยหนึ่งการเชื่อมต่อ รอในทรัพยากร
  • ที่ sysprocesses และ syslockinfo ระบบตารางจากนั้น ต้นแบบ ฐานข้อมูลจะสอบถามโดยตรง การเพิ่มประสิทธิภาพการทำงาน และการ การป้องกันไม่ให้กระบวนงานที่เก็บไว้นี้กลายเป็นบล็อก ดังนั้น ซึ่งถูกเก็บไว้ ขั้นตอนจะใช้เฉพาะของ Microsoft SQL Server 2000 หรือรุ่นที่ใหม่กว่าของ SQL Server
  • Worktable ที่มีขนาดเล็กถูกสร้างขึ้น ด้วยเคอร์เซอร์ที่เพื่อขอรับการ ผลผลิต DBCC INPUTBUFFER ซึ่งควรมีไม่มีผลอย่างเห็นได้ชัดในการใช้งานในการ tempdb ฐานข้อมูล
  • เนื่องจากบล็อกสามารถเปลี่ยนแปลงในขณะที่กำลังรวบรวม มีรายละเอียด โหมดรวดเร็วอยู่ ซึ่งเพิ่มประสิทธิภาพการทำงาน โดยการลดการ ผลลัพธ์ไปยังแถวที่เกี่ยวข้องของ sysprocesses และ syslockinfo ตารางของระบบ
  • ถ้าคุณพยายามที่จะติดตามไม่ใช่การล็อคทรัพยากร waits โหมด latch มีอยู่ ซึ่งทำให้เกิดการล็อกการแสดงผลจะถูกเว้นไว้
กระบวนงานที่เก็บไว้นี้เป็นประโยชน์ โดยทำงานอยู่จากแบบสอบถามใด ๆ เครื่องมือ อย่างไรก็ตาม Microsoft แนะนำให้ คุณทำตามขั้นตอนเหล่านี้เพื่อทำให้ บล็อกการวิเคราะห์:
  1. สร้างกระบวนงานที่เก็บไว้ sp_blocker_pss08ซึ่งถูกรวมไว้ในตอนท้ายของบทความนี้ จากแบบสอบถามใด ๆ เครื่องมือในขณะที่คุณเชื่อมต่อกับการเข้าสู่ระบบได้ sysadmin สิทธิพิเศษบนเซิร์ฟเวอร์ SQL Server หรืออินสแตนซ์ของ SQL Server คุณวางแผนการตรวจสอบ
  2. สร้างแฟ้มสคริปต์กับแบบสอบถามต่อไปนี้เพื่อเรียกใช้ กระบวนงานที่เก็บไว้แบบวนซ้ำ โปรดสังเกตว่า การหน่วงเวลาควรอยู่ระหว่าง 5 ถึง 60 วินาที
    วินาที:

    ในขณะที่ 1 = 1
    เริ่มต้น
    EXEC tempdb.dbo.sp_blocker_pss08
    -- หรือ สำหรับโหมดรวดเร็ว
    --Tempdb.dbo.sp_blocker_pss08 @ เร็ว EXEC = 1
    -- หรือ สำหรับโหมด latch
    --EXEC tempdb.dbo.sp_blocker_pss08 @ latch = 1
    การหน่วงเวลา WAITFOR ' 00: 00:15'
    จุดสิ้นสุด
    GO
  3. การแสดงผลนี้เป็นประโยชน์อย่างมากเมื่อรวมกับ Microsoft แฟ้มบันทึกการตรวจสอบประสิทธิภาพการทำงานของ Windows NT และแฟ้มบันทึกของผู้สร้างโปรไฟล์ของ SQL ดังนั้น การสร้างทั้งสองที่ ระบบจะแนะนำในเวลาเดียวกัน สำหรับข้อมูลที่เกี่ยวข้องกับการใดของผู้สร้างโปรไฟล์ และ เหตุการณ์การตรวจสอบประสิทธิภาพการทำงานในการจับภาพ และข้อมูลเกี่ยวกับวิธีการ การตีความผลลัพธ์ ดูบทความต่อไปนี้ในการความรู้ของ Microsoft ฐาน:
    224453ทำความเข้าใจ และการแก้ปัญหาการบล็อกของ 2000 หรือ SQL Server 7.0
  4. การเรียกใช้แฟ้มสคริปต์ที่สร้างขึ้นในขั้นตอนที่ 2 จาก Isql.exe เครื่องมือแบบสอบถาม Osql.exe หรือ Sqlcmd ยูทิลิตี้ในหน้าจอพร้อมรับคำสั่งของ Windows บนคอมพิวเตอร์ที่กำลังเรียกใช้ SQL Server ที่คุณต้องการตรวจสอบเพื่อป้องกันไม่ให้ปัญหาเครือข่ายหยุดการเชื่อมต่อเครื่องมือแบบสอบถาม ต่อไปนี้เป็นบรรทัดคำสั่งเป็นตัวอย่างที่คุณสามารถใช้เพื่อเริ่มการทำงานของ Osql.exe ซึ่งสันนิษฐานว่า ไคลเอนต์ที่มีการเรียกใช้จากคอมพิวเตอร์ที่กำลังเรียกใช้ SQL Server และว่าชื่อแฟ้มสคริปต์ Checkblk.sql ตรวจสอบให้แน่ใจว่าได้แก้ไขพารามิเตอร์ -S และแทน "server" ด้วยชื่อของเซิร์ฟเวอร์ SQL Server ของคุณ (หรือ "servername\instance") ถ้าคุณกำลังตรวจสอบการอินสแตนซ์ที่ระบุชื่อไว้ นอกจากนี้ แก้ไข -i พารามิเตอร์ และแทน "checkblk.sql" กับเส้นทางและชื่อแฟ้มสคริปต์สร้างขึ้นในขั้นตอนที่ 2
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
    หมายเหตุว่า คุณต้องใช้อื่น ๆ สวิตช์บรรทัดคำสั่งสำหรับการ ด้วยเหตุผลดังต่อไปนี้:

    • เมื่อต้องการตัดบรรทัดที่อยู่ในผลลัพธ์ของการป้องกันไม่ให้แฟ้มซึ่ง ทำให้ง่ายต่อการอ่าน
    • เมื่อต้องการส่งผลลัพธ์ไปยังไฟล์ ระบุ ด้วย -o พารามิเตอร์ แทนที่เป็นของถึงหน้าจอเพื่อให้มีเครื่องมือในการสอบถามว่าถ้าปัญหา คุณ ยังคง มีผลจนกว่าเครื่องมือแบบสอบถามที่ล้มเหลว
ต่อไปนี้เป็นการสร้างสคริปต์ sp_blocker_pss08 กระบวนงานที่เก็บไว้:
/*
Note: This script is meant to have 3 creations of the same stored procedure and two of them will fail
with either 207 errors or a 2714 error.
*/
use master
GO
if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss08') and sysstat & 0xf = 4)
   drop procedure dbo.sp_blocker_pss08
GO
create procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')
as 
--version 19.2005 - 2005 or Later
if is_member('sysadmin')=0 
begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end

set nocount on
SET LANGUAGE 'us_english'
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant
declare @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 != 0x0000

if 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 -- All
else
  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 Later
if is_member('sysadmin')=0 
begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end

set nocount on
SET LANGUAGE 'us_english'
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant

set @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 != 0x0000

if 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 -- All
else
  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 SP3
if is_member('sysadmin')=0
begin
  print 'Must be a member of the sysadmin group in order to run this procedure'
  return
end

set nocount on
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant

set @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 != 0x0000

if 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 -- All
else
  print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '
     + convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2000'
GO 
สำหรับข้อมูลเพิ่มเติม โปรดเยี่ยมชมเว็บไซต์ต่อไปนี้:
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-2005-performance-statistics-script.aspx

คุณสมบัติ

หมายเลขบทความ (Article ID): 271509 - รีวิวครั้งสุดท้าย: 29 กรกฎาคม 2554 - Revision: 5.0
ใช้กับ
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL 2005 Server Workgroup
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL 2005 Server Enterprise
  • 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
Keywords: 
kbsqlsetup kbhowto kbinfo kbmt KB271509 KbMtth
แปลโดยคอมพิวเตอร์
ข้อมูลสำคัญ: บทความนี้แปลโดยซอฟต์แวร์การแปลด้วยคอมพิวเตอร์ของ Microsoft แทนที่จะเป็นนักแปลที่เป็นบุคคล Microsoft มีบทความที่แปลโดยนักแปลและบทความที่แปลด้วยคอมพิวเตอร์ เพื่อให้คุณสามารถเข้าถึงบทความทั้งหมดในฐานความรู้ของเรา ในภาษาของคุณเอง อย่างไรก็ตาม บทความที่แปลด้วยคอมพิวเตอร์นั้นอาจมีข้อบกพร่อง โดยอาจมีข้อผิดพลาดในคำศัพท์ รูปแบบการใช้ภาษาและไวยากรณ์ เช่นเดียวกับกรณีที่ชาวต่างชาติพูดผิดเมื่อพูดภาษาของคุณ Microsoft ไม่มีส่วนรับผิดชอบต่อความคลาดเคลื่อน ความผิดพลาดหรือความเสียหายที่เกิดจากการแปลเนื้อหาผิดพลาด หรือการใช้บทแปลของลูกค้า และ Microsoft มีการปรับปรุงซอฟต์แวร์การแปลด้วยคอมพิวเตอร์อยู่เป็นประจำ
ต่อไปนี้เป็นฉบับภาษาอังกฤษของบทความนี้:271509

ให้ข้อเสนอแนะ

 

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