Fala Pessoal!

Como expliquei no meu primeiro post “Boas Vindas! Saindo da Zona de Conforto!”, a ideia desse blog é compartilhar alguns casos do meu dia a dia como DBA SQL Server. Então vamos lá…

Nesse primeiro post técnico, vou compartilhar com vocês a procedure “stpLock_Raiz” que utilizamos bastante nas nossas Consultorias. Abaixo explico para que serve e como ela funciona.

---

YouTube – Vídeo:

Segue abaixo um vídeo que gravei no YouTube mostrando na prática a execução dos scripts desse post:

https://www.youtube.com/watch?v=svJJoBLR_dE

---

stpLock_Raiz:

Imagine o seguinte cenário:

“Um banco de dados possui dezenas ou até centenas de transações simultâneas. Além disso, os usuários reclamam bastante de lentidão (imagine que os carros na imagem acima sejam as transações no banco de dados). Como você é um ótimo DBA, identificou rapidamente que existem muitos processos bloqueados. Contudo, devido à grande quantidade de processos, fica difícil identificar a origem do problema, ou seja, a origem / raiz do Lock.”

Sentimos na pele essa dificuldade e então resolvemos criar uma solução chamada “stpLock_Raiz”.

Essa procedure retorna apenas os processos envolvidos nos Locks do banco de dados. Além disso, informa também o Nível do Lock de cada processo. Com isso, conseguimos identificar facilmente a origem do problema, os usuários envolvidos e agir de forma mais rápida para encontrar uma solução. WOW!!!

---

Pré-Requisitos:

Segue abaixo alguns pré-requisitos para o funcionamento dessa procedure:

1) SQL Server: Compatível com a versão 2008 ou superior.

OBS: Não testei na versão 2005.

2) Procedure “sp_whoisactive” – Adam Machanic.

Essa procedure é utilizada para retornar os processos que estão sendo executados no banco de dados. Ela é uma mão na roda para todo DBA e muito útil no meu dia a dia.

Download: http://whoisactive.com/downloads/

3) Criação da base “Traces”.

Em nossas Consultorias, sempre criamos uma base chamada “Traces” para armazenar de forma separada nossas rotinas e logs de monitoramento do banco de dados. Dessa forma, não impactamos nenhuma base do cliente.

OBS: Você também pode utilizar outro nome para essa base. Basta alterar no script de criação disponibilizado nesse post.

---

Como Funciona:

Logo no início da procedure, armazenamos o resultado da “sp_whoisactive” em uma tabela temporária. Depois disso, incluímos uma coluna para identificar o Nível do Lock, verificamos se existe algum processo bloqueado e atualizamos o valor dessa coluna.

Nessa procedure consideramos apenas quatro níveis de Lock, sendo o nível 0 a Raiz do Lock. Até o momento isso nos atendeu muito bem, visto que a ideia principal da procedure é identificar o nível 0 para que possamos atuar diretamente no problema e evitar lentidão no banco de dados.

Executei um script de teste (disponibilizado mais abaixo) para simular uma situação de Lock no banco de dados. Segue abaixo alguns prints com os resultados das procedures para comparação:

• sp_whoisactive:

• stpLock_Raiz:

Repare que o print da “sp_whoisactive” também retorna os processos 52, 56 e 59. Ou seja, retornou todos os processos em execução no banco de dados, estando bloqueados ou não.

Contudo, a procedure “stpLock_Raiz” retornou apenas os processos 55, 58 e 60. Ou seja, apenas os processos envolvidos no Lock. Como podemos observar, a raiz do Lock era o processo 55. Com isso, teríamos as informações necessárias para começar a atuar no problema e sinalizar os usuários envolvidos.

Nesse caso foi relativamente simples encontrar o problema. Agora imagine como ficaria o retorno dessas procedures considerando dezenas ou centenas de processos simultâneos no banco de dados?

“Mas Luiz, eu executei a procedure e não retornou nada, o que isso quer dizer?”

Ótimo!! Seu banco de dados não possui nenhum lock no momento. Fique feliz por isso kkk =)

---

Download “stpLock_Raiz”:

Segue o link para baixar os scripts utilizados nesse Post:

Link:

https://github.com/luizvitorf/SQLServer/blob/master/Scripts/Administra%C3%A7%C3%A3o/stpLock_Raiz.sql

---

Alerta: Monitorando Processos Bloqueados no SQL Server

Segue também um link muito interessante do Fabricio Lima “Alerta: Monitorando Processos Bloqueados no SQL Server”. Nesse caso, será enviado um e-mail automaticamente informando que existe algum processo bloqueado naquele exato momento no banco de dados. Vale a pena conferir!

---

Por hoje é só pessoal.

Deixe nos comentários algum caso interessante envolvendo Lock no SQL Server e como você conseguiu resolver. Fique à vontade também para fazer sugestões ou melhorias no script.

Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server