Home - www.devmedia.com.br


Gerenciando Bloqueios–Parte II

Os métodos convencionais utilizados no rastreamento de bloqueios nem sempre são eficientes:

  • A visualização de processos e/ou bloqueios ativos pelo Enterprise Manager não possue boa performance durante picos de utilização do servidor;
  • Quando existem muitas conexões ativas, identificar a conexão responsável pela avalanche de travamentos pode consumir um tempo precioso: as procedures SP_WHO, SP_WHO2 e SP_LOCK e Enterprise Manager não possuem um mecanismo eficiente para solucionar esse problema. Sabemos que a conexão 52 está travada pela 65, que a 32 está bloqueada pela 433, ....., mas não somos informados “de maneira explícita” que o spid 34 é quem está ocasionando todos esses bloqueios.

Analisando tabela de sistema SYSPROCESSES

Quando os modelos prontos não atendem nossas necessidades, temos que partir para soluções específicas, feitas sob encomenda para nossas necessidades – é aí que entra a tabela de sistema SYSPROCESSES.

A tabela de sistema SYSPROCESSES localizada no database MASTER é responsável por armazenar o metadata de todos os processos ativos no servidor SQL Server 2000 (ver Figura 1)


Figura 1
. Select na tabela de sistema SYSPROCESSES

Bem, e daí? Como a tabela SYSPROCESSES poderia ajudar a resolver a questão do travamento?

A coluna BLOCKED na tabela SYSPROCESSES (ver Figura 1) pode ser utilizada para rastrear o spid responsável pelos bloqueios da seguinte forma:

  • Sempre que existir um spid registrado nessa coluna, ESSE spid estará bloqueando pelo menos uma conexão. Na Figura 1, o spid 54 é o responsável pelo bloqueio da conexão 51;
  • Podemos tirar proveito dessa informação se analisarmos a situação de bloqueio para cada spid listado na coluna BLOCKED. Em outras palavras, verificaremos se os spids listados TAMBÉM estão em estado de espera (bloqueados) ou se estão com bloqueios ativos (bloqueadores):

Como verificar os spids que estão bloqueados:

Listagem 1. Verificando os spids bloqueados através de consulta na tabela SYSPROCESSES

select spid, blocked, hostname=left(hostname,20), program_name=left(program_name,20),
       WaitTime_Seg = convert(int,(waittime/1000))  ,open_tran, status
From master.dbo.sysprocesses
where blocked > 0
order by spid

Como verificar os spids responsáveis pelos bloqueios (=bloqueadores):

Listagem 2. Verificando spids “bloqueadores” através de consulta na tabela SYSPROCESSES

Note as diferenças de filtro nas duas listagens:

  • Para saber quem está em estado de espera (o popular “travado”) a Listagem 1 emprega o filtro ... blocked > 0 ;
  • Para determinar o spid bloqueador, precisaremos saber as conexões que não estão em estado de espera, mas possuem bloqueios ativos responsáveis por travamentos noutras sessões. Para identificar as conexões responsáveis por bloqueios utiliza-se a subquery ... select blocked from master.dbo.sysprocesses where blocked > 0.  Para determinar, dentre essas conexões, quais são aquelas que “bloqueiam sem ser bloqueadas” utiliza-se o filtro blocked=0.

Conclusão

A partir do select presente na Listagem 2, pode-se desenvolver uma procedure que liste as conexões responsáveis por travamentos, levando-se em conta o parâmetro de tempo de espera (coluna WAITTIME). Essa procedure pode ser utilizada na geração de um job, que rode periodicamente analisando bloqueios e informando o DBA se existirem bloqueios persistentes – por exemplo, com duração superior a 3 segundos.

Bem, é isso. Até a próxima!

 

Paulo Ribeiro (psribeiro@hotmail.com) é Microsoft MCDBA e membro da equipe editorial da SQL Magazine. Atua como DBA sênior em SQL Server na Livraria e Papelaria Saraiva S/A.

  Monitorando deadlocks com Trace Flags    
  Monitorando deadlocks com o Profiler    
  Variáveis tipo TABLE    
  Tabelas Temporárias    
  Subqueries Parte II: Queries correlatas    
  Subqueries – Parte I    
  Gerenciando Bloqueios–Parte II    
  Gerenciando Bloqueios–Parte I    
  Porque qualificar o owner na chamada de stored-procedures    
  Explorando os Tipos de Join – Parte II    
  Explorando os Tipos de Join – Parte I    
  Versões existentes do SQL Server 2000    
  Tuning - Plano de Execução no SQL Server - Parte 4    
  Tuning - Plano de Execução no SQL Server - Parte 3    
  Tuning - Plano de Execução no SQL Server - Parte 2    
  Tuning - Plano de Execução no SQL Server - Parte 1    
  Tuning - Estatísticas de I/O    
  Desfragmentando Índices no SQL Server    
  Procedures Não Documentadas
no SQL Server 2000 Parte 2
   
  Procedures Não Documentadas
no SQL Server 2000.
   
  SQL Server 2005 - YUKON    
  SQL Server 2000: o Contra-Ataque.    
  Boas-Vindas    
     

 

Todos os direitos reservados: DevMedia Group
SQL Magazine - 2004