INF: Como monitorizar o bloqueio do SQL Server 7.0

Traduções de Artigos Traduções de Artigos
Artigo: 251004 - Ver produtos para os quais este artigo se aplica.
Expandir tudo | Reduzir tudo

Sumário

Este artigo documenta a utilização e estrutura de um procedimento armazenado que pode utilizar para diagnosticar problemas de bloqueio e desempenho. Para obter uma descrição de como compreender e resolver bloquear, consulte o seguinte artigo na base de dados de conhecimento da Microsoft:
224453INF: Compreender e resolver problemas de bloqueio do SQL Server 7.0
Para obter informações sobre estes mesmos problemas no SQL Server 2000, consulte o seguinte artigo na base de dados de conhecimento da Microsoft:
271509INF: Como monitorizar o bloqueio do SQL Server 2000

Mais Informação

O procedimento sp_blocker_pss70 armazenados, descrito nesta secção, captura as seguintes informações:
  • O início tempo (de acordo com o computador que está a executar o SQL Server), para que esta amostragem de bloqueio de tempo alinhado com outras informações de desempenho, tais como um registo do Monitor de desempenho do Microsoft Windows NT ou um registo de SQL Profiler.

  • Informações sobre ligações ao SQL Server, consultando a tabela de sistema sysprocesses .

  • Informações sobre recursos de bloqueio, consultando a tabela de sistema syslockinfo .

  • Informações sobre recursos aguarda, executando o comando DBCC SQLPERF(WAITSTATS).

  • O actual com secção de SQL para ligações que são bloqueadas por outros utilizadores ou bloquear outros utilizadores, executando o comando DBCC INPUTBUFFER.

  • A hora final, acordo com para o computador que está a executar o SQL Server.
O procedimento armazenado foi criado com as optimizações seguintes para reduzir o efeito no desempenho e bloqueio ao executar este procedimento armazenado:
  • Não existe nenhuma saída gerada a menos que, pelo menos, uma ligação está à espera de um recurso.

  • As tabelas de sistema sysprocesses e syslockinfo da base de dados principal são consultadas directamente, para aumentar o desempenho e para impedir que este procedimento armazenado ficar bloqueado. Por conseguinte, este procedimento armazenado é específico do Microsoft SQL Server 7.0.

  • É criado um pequeno worktable o cursor para obter INPUTBUFFER DBCC saída; Isto irá ter efeito considerável sobre a utilização na base de dados tempdb .

  • Porque o bloqueio pode alterar enquanto na recolha das informações, existe um modo rápido, que aumenta o desempenho, diminuindo os resultados relevantes linhas das tabelas de sistema sysprocesses e syslockinfo .

  • Se tentar registar o recurso de bloqueio não aguarda, existe um modo enganchado que faz com que o bloqueio de saída para ser omitido.
Este procedimento é útil executando-o partir de qualquer ferramenta de consulta. No entanto, a Microsoft sugere que siga estes passos para efectuar análises de bloqueio:
  1. Criar o procedimento armazenado sp_blocker_pss70 , incluída no fim neste artigo, a partir de qualquer ferramenta de consulta enquanto estiver ligado com um início de sessão tiver privilégios sysadmin no servidor de SQL Server ou a instância do SQL Server que pretende monitorizar.
  2. Crie um ficheiro script com a seguinte consulta para executar o procedimento armazenado num loop. Note que o atraso é entre 5 e 60 segundos:
    WHILE 1=1
    BEGIN
       EXEC master.dbo.sp_blocker_pss70
       -- Or for fast mode 
       -- EXEC master.dbo.sp_blocker_pss70 @fast=1
       -- Or for latch mode 
       -- EXEC master.dbo.sp_blocker_pss70 @latch=1
       WAITFOR DELAY '00:00:15'
    END
    GO
    					
  3. Esta saída é muito útil quando combinado com um registo do Monitor de desempenho do Microsoft Windows NT e um registo de SQL Profiler, assim, criar ambas ao mesmo tempo é sugerido. Para obter informações sobre os eventos de monitor de desempenho e Profiler para capturar e para obter informações sobre como interpretar os resultados, consulte o seguinte artigo na base de dados de conhecimento da Microsoft:
    224453INF: Compreender e resolver problemas de bloqueio do SQL Server 7.0
  4. Execute o ficheiro script criado no passo 2 da qualquer ferramenta de consulta Isql.exe ou OSQL.exe a partir de uma linha de comandos do Windows no computador que está a executar o SQL Server que pretende monitorizar para impedir problemas de rede de desligar a ferramenta de consulta. Segue-se uma linha de comandos de exemplo para iniciar OSQL.exe assume que o cliente é executado no computador com o SQL Server e que o ficheiro de script tem o nome Checkblk.sql. Certifique-se de que corrigir parâmetro- S , substitua "servidor" pelo nome do SQL Server correcto, servidor parâmetro- i , substituindo "checkblk.sql" com o caminho e nome para o ficheiro de script criado no passo 2.
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
    						
    nota que tem de utilizar a linha de comandos passa pelas seguintes razões:

    • Para impedir moldagem de linha nos ficheiros de saída, que torna mais fácil de ler.
    • Para enviar a saída para um ficheiro, especificado com- o parâmetro, em vez de no ecrã para que se a ferramenta de consulta tiver problemas, que ainda exportar até deixar da ferramenta de consulta.
Segue-se o script que pode utilizar para criar o procedimento armazenado sp_blocker_pss70 :

use master
GO

if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss70') and sysstat & 0xf = 4)
   drop procedure dbo.sp_blocker_pss70
GO

create proc sp_blocker_pss70 (@latch int = 0, @fast int = 1)
as 
--version 13
set nocount on
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @time3 datetime
declare @dbname nvarchar(128)
declare @IsDetached tinyint
declare @IsEmergencyMode tinyint
declare @IsInLoad tinyint
declare @IsInRecovery tinyint
declare @IsNotRecovered tinyint
declare @IsOffline tinyint
declare @IsShutDown tinyint
declare @IsSingleUser tinyint
declare @IsSuspect tinyint

set @time = getdate()

select spid, ecid, blocked, waittype, dbid, 'ignore_app' = case 
  when convert(varchar(128),hostname) = 'PSSDIAG' then 1 else 0 end into #probclients
  from master..sysprocesses where blocked!=0 or waittype != 0x0000
create unique clustered index pc on #probclients (blocked, spid, ecid)
set @time3 = getdate()

if exists (select spid from #probclients where ignore_app != 1 or waittype != 0x020B)
begin
   set @time2 = getdate()
   print ''
   print '7 Start time: ' + convert(varchar(26), @time, 121) + ' ' 
     + convert(varchar(12), datediff(ms,@time,@time2)) + ' ' + convert(varchar(12), datediff(ms,@time,@time3))

   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..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'
            set @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' 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 'IS'
                                        when 5 then 'SIU'
                                        when 6 then 'IS-S'
                                        when 7 then 'IX'
                                        when 8 then 'SIX'
                                        when 9 then 'S'
                                        when 10 then 'U'
                                        when 11 then 'IIn-Nul'
                                        when 12 then 'IS-X'
                                        when 13 then 'IU'
                                        when 14 then 'IS-U'
                                        when 15 then 'X'
                                        when 16 then 'BU' end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end
            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 -- blocking via locks
      else
         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
   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..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'
            set @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' 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 'IS'
                                        when 5 then 'SIU'
                                        when 6 then 'IS-S'
                                        when 7 then 'IX'
                                        when 8 then 'SIX'
                                        when 9 then 'S'
                                        when 10 then 'U'
                                        when 11 then 'IIn-Nul'
                                        when 12 then 'IS-X'
                                        when 13 then 'IU'
                                        when 14 then 'IS-U'
                                        when 15 then 'X'
                                        when 16 then 'BU' end,
               Status = case req_status when 1 then 'GRANT'
                                        when 2 then 'CNVT'
                                        when 3 then 'WAIT' end
            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)

   end -- Fast not set

   print ''
   print 'DBCC SQLPERF(WAITSTATS)'
   dbcc sqlperf(waitstats)
   print ''

   print ''
   Print '*********************************************************************'
   Print 'Print out DBCC INPUTBUFFER for all blocked or blocking spids.'
   Print '*********************************************************************'

   declare ibuffer cursor fast_forward for
   select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6)) as blocked
   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, @blocked
   while (@@fetch_status != -1)
   begin
      print ''
      exec ('print ''DBCC INPUTBUFFER FOR SPID ' + @spid + '''')
      exec ('dbcc inputbuffer (' + @spid + ')')

      fetch next from ibuffer into @spid, @blocked
   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)
      select @IsDetached = DATABASEPROPERTY(@dbname,'IsDetached')
      select @IsEmergencyMode = DATABASEPROPERTY(@dbname,'IsEmergencyMode')
      select @IsInLoad = DATABASEPROPERTY(@dbname,'IsInLoad')
      select @IsInRecovery = DATABASEPROPERTY(@dbname,'IsInRecovery')
      select @IsNotRecovered = DATABASEPROPERTY(@dbname,'IsNotRecovered')
      select @IsOffline = DATABASEPROPERTY(@dbname,'IsOffline')
      select @IsShutDown = DATABASEPROPERTY(@dbname,'IsShutDown')
      select @IsSingleUser = DATABASEPROPERTY(@dbname,'IsSingleUser')
      select @IsSuspect = DATABASEPROPERTY(@dbname,'IsSuspect')

      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
      if @IsDetached + @IsEmergencyMode + @IsInLoad + @IsInRecovery + @IsNotRecovered + @IsOffline
         + @IsShutDown + @IsSingleUser + @IsSuspect = 0
         dbcc opentran(@dbname)
      else
         print 'Skipped: Status=ID=' + str(@IsDetached,1) + ' IEM=' + str(@IsEmergencyMode,1) + ' IIL=' 
           + str(@IsInLoad,1) + ' IIR=' + str(@IsInRecovery,1) + ' INR' + str(@IsNotRecovered,1) + ' IO=' 
           + str(@IsOffline,1) + ' ISD=' + str(@IsShutDown,1) + ' ISU=' + str(@IsSingleUser,1) + ' IS='
           + str(@IsSuspect,1) 
      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 ''
   print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
  print '7 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '
    + convert(varchar(12), datediff(ms,@time,@time3)) + ' ' + ISNULL (@@servername,'(null)')
GO 
	

Propriedades

Artigo: 251004 - Última revisão: 22 de março de 2004 - Revisão: 3.2
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 7.0 Standard Edition
Palavras-chave: 
kbmt kbhowto kbinfo kbsqlserv700 KB251004 KbMtpt
Tradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine translation ou MT), não tendo sido portanto revisto ou traduzido por humanos. A Microsoft tem artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais. O objectivo é simples: oferecer em Português a totalidade dos artigos existentes na base de dados do suporte. Sabemos no entanto que a tradução automática não é sempre perfeita. Esta pode conter erros de vocabulário, sintaxe ou gramática? erros semelhantes aos que um estrangeiro realiza ao falar em Português. A Microsoft não é responsável por incoerências, erros ou estragos realizados na sequência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza actualizações frequentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 251004

Submeter comentários

 

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