INF: Como monitorar o bloqueio do SQL Server 7.0

Traduções deste artigo Traduções deste artigo
ID do artigo: 251004 - Exibir os produtos aos quais esse artigo se aplica.
Expandir tudo | Recolher tudo

Sumário

Este artigo documenta o uso e design de um procedimento armazenado que você pode usar para diagnosticar problemas de bloqueio e desempenho. Para obter uma descrição de como entender e resolver bloqueio, consulte o seguinte artigo na Base de dados de Conhecimento da Microsoft:
224453INF: Noções básicas sobre e resolvendo problemas de bloqueio do SQL Server 7.0
Para obter informações sobre esses mesmos problemas no SQL Server 2000, consulte o seguinte artigo na Base de dados de Conhecimento da Microsoft:
271509INF: Como monitorar o bloqueio do SQL Server 2000

Mais Informações

O procedimento sp_blocker_pss70 armazenados, descrito nesta seção, captura as informações a seguir:
  • A partida hora (de acordo com o computador que está executando o SQL Server) para que esta amostragem de bloqueio possa ser alinhado tempo com outras informações de desempenho, como um log do Monitor de desempenho do Microsoft Windows NT ou um log do SQL Profiler.

  • Informações sobre conexõ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 recurso aguarda, executando DBCC SQLPERF(WAITSTATS).

  • O atual executando o SQL batch para conexões que são bloqueados por outras pessoas ou bloqueando outros, executando INPUTBUFFER DBCC.

  • A hora de término, acordo com para o computador que está executando o SQL Server.
O procedimento armazenado foi criado com as seguintes otimizações para reduzir o efeito no desempenho e bloqueio executando este procedimento armazenado:
  • Não há nenhuma saída gerada, a menos que pelo menos uma conexão está aguardando um recurso.

  • As tabelas do banco de dados mestre no sistema sysprocesses e syslockinfo deverão diretamente, para aumentar o desempenho e impedir que este procedimento armazenado se tornando bloqueado. Portanto, esse procedimento é específico para o Microsoft SQL Server 7.0.

  • Uma pequena tabela de trabalho é criada pelo cursor para obter saída INPUTBUFFER DBCC; isso terá efeito perceptível no uso no banco de dados tempdb .

  • Como o bloqueio pode ser alterado ao obter as informações, existe um modo rápido que aumenta o desempenho diminuindo os resultados para linhas relevantes das tabelas de sistema sysprocesses e syslockinfo .

  • Se tentar controlar o recurso de bloqueio não espera, existe um modo de registrador de divisão, que faz com que o bloqueio de saída para ser omitido.
Esse procedimento armazenado é útil executando-lo a partir de qualquer ferramenta de consulta. No entanto, a Microsoft sugere que você siga estas etapas para executar análise de bloqueio:
  1. Crie o procedimento armazenado sp_blocker_pss70 , que está incluído no final deste artigo, a partir de qualquer ferramenta de consulta enquanto você estiver conectado com um logon que tenha privilégios sysadmin no SQL server ou a instância do SQL Server que você planeja monitorar.
  2. Crie um arquivo de script com a seguinte consulta para executar o procedimento armazenado em um loop. Observe 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. Essa saída é muito útil quando combinado com um log do Monitor de desempenho do Microsoft Windows NT e um log do SQL Profiler, portanto, criar ambos ao mesmo tempo é sugerida. Para obter informações sobre quais eventos Profiler e o desempenho do sistema 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: Noções básicas sobre e resolvendo problemas de bloqueio do SQL Server 7.0
  4. Execute o arquivo de script criado na etapa 2 da qualquer ferramenta de consulta Isql.exe ou osql.exe em um prompt de comando do Windows no computador que está executando o SQL Server que você deseja monitorar para impedir que problemas de rede desconectando-se a ferramenta de consulta. A seguir está uma linha de comando de exemplo iniciar osql.exe, que assume que o cliente é executado no computador que está executando o SQL Server, e que o arquivo de script é denominado Checkblk.sql. Certifique-se corrigir parâmetro- S , substitua "server" pelo nome do SQL Server correto, servidor parâmetro- i e substitua "checkblk.sql" com o caminho e nome para o arquivo de script criado na etapa 2.
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000
    						
    nota que você deve usar linha de comando alterna pelos seguintes motivos:

    • Para evitar a quebra automática de linha nos arquivos de saída, que torna mais fácil de ler.
    • Para enviar a saída para um arquivo especificado com- o parâmetro, em vez de para a tela para que se a ferramenta de consulta tiver problemas, você ainda tem saída até que a ferramenta de consulta falha.
A seguir está o script que você pode usar para criar o procedimento sp_blocker_pss70 armazenados:

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

ID do artigo: 251004 - Última revisão: segunda-feira, 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 traduzido ou revisto por pessoas. A Microsoft possui artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais, com o objetivo de oferecer em português a totalidade dos artigos existentes na base de dados de suporte. No entanto, a tradução automática não é sempre perfeita, podendo conter erros de vocabulário, sintaxe ou gramática. A Microsoft não é responsável por incoerências, erros ou prejuízos ocorridos em decorrência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza atualizações freqüentes 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