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. |
|