Se você é um DBA e nunca passou pelo famoso problema de bloqueio de conexões, pode ter certeza de que ainda passará. Uma procedure muito utilizada durante a verificação e análise de bloqueio de conexões é uma procedure chamada sp_blocker_pss80.sql, a qual inclusive possui um artigo KB 271509 demonstrando sua utilização.
O problema da sp_blocker_pss80 é que ela gera um arquivo de saída, na maioria das vezes muito extenso e para os DBAs menos experientes, difícil de analisar.
É aí que entra a ferramenta Sherlock. Dado um arquivo de output, arquivo este gerado pela procedure sp_blocker_pss80, a ferramenta lhe apresenta os SPIDs bloqueados, o SPID que está causando o bloqueio e a instrução que ele está executando, os SPIDs que estão sendo bloqueados.
Como Usar a Ferramenta
A utilização da ferramenta é bastante simples, ao fazer o download e extrair os arquivos, notará a presentaça de 4 arquivos:
1. Sherlock.exe ==> o executável do utilitário
2. sherlock.ppt ==> uma apresentação em powerpoint abrangendo os seguintes tópicos:
— What is locking?
— What is blocking?
— Built-in features in SQL Server to identify blocking.
— Blocker script.
— Analyzing blocking with findstr/grep.
— Analyzing blocking with Sherlock.
— Using Sherlock as a learning tool.
— Sherlock limitations and upcoming features
3. sp_blocker_pss70.sql ==> script da procedure sp_blocker_pss para SQL Server 7.0
4. sp_blocker_pss80.sql ==> script da procedure sp_blocker_pss para SQL Server 2000
O primeiro passo é pegar o script da procedure de acordo com seu ambiente e criar a procedure sp_blocker_pssXX no banco de dados master. Para este exemplo estarei utilizando a sp_blocker_pss80.
Depois crie uma arquivo contendo o script abaixo e salve-o como blockscript.sql (Este nome é apenas uma sugestão). Este script executa a procedure sp_blocker_pss80 a cada 15 segundos.
WHILE 1=1
BEGIN
EXEC master.dbo.sp_blocker_pss80
WAITFOR DELAY ’00:00:15′
END
GO
Crie um outro arquivo contendo a linha de comando abaixo e salve-o como monitblock.bat. Esta .bat irá executar o utilitário osql.exe para se conectar ao servidor especificado e executar o script passado pelo arquivo blockscript.sql. Todo o resultado da execução deste script será armazenado no arquivo de log blocking_log.out.
osql -Usa -P<senhadosa> -S<servidor> -iD:/temp/blockscript.sql -oD:/temp/blocking_log.out -n
Obs: Altere <senhadosa> pela senha do seu usuário sa e <servidor> pelo nome do seu servidor SQL Server.
Para iniciar o processo de coleta de dados, abra um prompt do DOS e execute o arquivo .bat monitblock.bat. Quando quiser parar a coleta, basta teclar Ctrl+C.
Analisando os Dados Coletados
Para analisar os dados coletados, execute a ferramenta Sherlock e siga os passos abaixo:
1. Clique sobre o botão Open e informe o caminho até o arquivo de log blocking_log.out (Neste caso, D:/temp).
2. Clique em Yes para o Sherlock identificar o arquivo de log automaticamente.
3. Clique sobre o botão Analyze. Se houver algum blocking a ferramenta irá detectar e maiores detalhes serão apresetados.
Neste exemplo podemos ver que foi 4 ocorrências de blocking e que em uma delas o SPID 56 está bloqueando o SPID 60 que está bloqueando o SPID 59. Em Last Query, temos a instrução que está sendo executada pela conexão 56 e que está causando o bloqueio das demais conexões.
Como podemos observar, o SPID 56 está executando uma transação (Open Transaction=1) e com isso, as demais conexões só irão prosseguir com seu processamento após esta transação terminar ou ser cancelada.
Para fazer o donwload da ferramenta, clique sobre o link Sherlock Download (versão 1.3)
Veja também Analisando a Performance do Servidor – CheckList