Técnicas para verificação e análise de consultas consideradas lentas no Microsoft SQL Server

Olá galera, comunidade, amigos e seguidores.

Meu deus que dificuldade para encontrar um tema ou conteúdo diferente do que venho postando e trabalhando recentemente que fosse útil e ao mesmo tempo didático para vocês.

Sinceramente não sei se escolhi algo interessante, mas penso que poderá ser importante, quando vocês se depararem com o mesmo cenário que eu me deparei há alguns dias, mais precisamente entre os meses de Abril e Maio.

Estou me referindo a uma das maiores discussões que qualquer profissional de banco de dados sempre é questionado ou se depara, a chamada Lentidão para Consulta ou Manipulação de Dados.

Introdução

As chamadas lentidões no processamento de consultas ou manipulações de dados, comumente ocorrem no dia a dia das empresas. Identificar o que pode estar acontecendo é uma tarefa das mais árduas, chatas, demoradas e estressantes para qualquer Profissional de Suporte, Infraestrutura e principalmente Banco de Dados.

Este é o tipo da situação que o usuário se considera ao mesmo tempo o mais prejudicado e conhecer do assunto, pois ele sempre tem aquela frase: “O Sistema esta lento, o Sistema esta travando, alguém precisa fazer algo.”

É justamente este algo que aparentemente é simples, que se torna o maior dos monstros, pois as variáveis que podem existir são inúmeras, o que possibilitam ocorrer das maneiras mais incerteza e diversas possíveis.

Sempre digo para os meus alunos: “Consultas com execução lenta podem ser causadas por problemas de desempenho relacionados à sua rede ou ao computador onde o SQL Server está sendo executado. Como também, podem ser causadas por problemas com seu projeto de banco de dados físico ou lógico”.

Quando uma consulta ou atualização leva muito mais tempo do que o esperado, faça as seguintes perguntas a si mesmo, que remetem aos motivos para a execução lenta das consultas relacionadas na seção anterior:

Então, como podemos tentar realmente identificar se nosso Ambiente, Sistema, Servidor ou Banco de Dados esta lento? Para responder esta pergunta, eu pesquisei durante muito tempo e nunca encontrei nada tão específico, padronizado ou até mesmo correto, pois cada cenário é um cenário, cada projeto é um projeto, cada situação é uma situação totalmente diferente da outra.

Com base, em grandes especialistas e estudiosos, cheguei à seguinte conclusão, procurar identificar a causa origem da lentidão, pode ser dividida em quatro camadas, onde a camada que representa o Sistema Gerenciador de Banco de Dados ou Sistema Banco de Dados pode ser considerada a última camada em uma Arquitetura Computacional.

Neste caso, uma Arquitetura Computacional básica pode ser estrutura da seguinte forma:

1. Camada de Rede;
2. Camada de Hardware;
3. Camada do Sistema Operacional; e
4. Camada de Software / Aplicativos e Sistema Gerenciador de Banco de Dados.

 

Pensando fora da caixa

Muito bem, dentro deste cenário complexo, repleto de considerações, ponderações, estudos, variações e com certeza muitas dúvidas, eu defini ao longo dos anos como uma pequena metodologia de trabalho, que consiste inicialmente utilizar um simples questionário, composto por algumas perguntas que podem ajudar a encontrar o ponto chave da geração dos problemas ou causas da possível lentidão.

Este questionário esta organizado em duas partes, com objetivo responder questões ou identificar dúvidas, para inúmeros motivos comuns em qualquer ambiente, dentre eles destaco:

• Consumo e configuração de Hardware:
• Estrutura e Topologia de Rede;
• Estrutura física do banco de dados;
• Armazenamento centralizado ou particionamento de dados ou banco de dados;
• Recursos como Índices, Estatísticas, Triggers e Visões Indexadas.

• Conjuntos de Disco(s) Rígidos;
• Memória;
• Processador (ES); e
• Placa(s) de Rede.

 

 

Questionário – Parte 1 – Infraestrutura.

1. Qual é o consumo de processador e memória demandados por seu servidor no momento ou período de lentidão?

 

1. A lentidão esta ocorrendo em consultas ou funcionalidades específicas da sua aplicação ou ambientes?
2. Existem algum tipo de conector ou middleware utilizado para realizar a conexão entre a aplicação e o Banco de Dados?
3. Qual são a versão e edição do seu Sistema Operacional?
4. Qual são a versão e edição do seu Microsoft SQL Server?
5. Todas as atualizações de Sistema Operacional e SQL Server forma aplicadas?
6. Qual é a configuração ou arranjo de disco que você esta utilizando?
7. A lentidão que esta sendo apresentada é generalizada ou ocorre somente nos recursos que fazem uso do Banco de Dados?
8. Você realizou ou esta realizando algum tipo de monitoramento através da Ferramenta Monitor de Sistema do Windows?
9. Alguma alteração nas configurações do seu Servidor, Rede, Banco de Dados ou aplicação foi realizada?
10. Suas aplicações encerram suas conexões após o processamento?

 

Questionário – Parte 2 – Microsoft SQL Server e Banco de Dados

1. Você realizou ou esta realizando algum tipo de monitoramento através das Ferramenta Microsoft SQL Server Profiler?
   1. Caso o problema de lentidão esteja relacionado a consultas, qual consulta ou conjunto de consultas que podem estar envolvidas ou relacionadas com esta situação?
   2. Esta lentidão ou suposta lentidão esta ocorrendo a partir de qual data ou situação?
   3. Ao encontrar um possível consulta com execução lenta, como podemos fazer sua análise?
   4. Existe algum procedimento de atualização de Estatísticas de Dados e Tabelas de Sistema?

• Recomenda-se utilizar estas ou alguma outra ferramenta para procurar identificar e obter informações sobre estas possíveis consultas lentas. A partir do Microsoft SQL Server 2005 foram introduzidos alguns relatórios padrões que podem ajudar nesta análise.

• A partir do Microsoft SQL Server 2008, utilize as exibições de gerenciamento dinâmico sys. dm_exec_query_stats e sys.dm_exec_requests para encontrar consultas similares que podem estar consumindo muitos recursos.

• Depois de identificar a consulta lenta, você pode analisar o desempenho da consulta mais profundamente criando o chamado Plano de Execução, que pode ser um texto, XML ou representação gráfica do plano de execução de consulta que o otimizador de consulta gera.

• O otimizador de consultas existe no SQL Server utiliza estatísticas para criar planos de consulta que melhoram o desempenho. Na grande a maioria das consultas, o otimizador já gera as estatísticas necessárias para um plano de consulta de alta qualidade, existem casos, que você poderá ou deverá criar estatísticas adicionais visando obter uma melhora no processamento destas consultas.

1. As opções AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS estão habilitadas?

• Auto_create_statistics: Ao fazer uso desta opção de estatísticas em todo o banco de dados. Podemos nos deparar com situações em que os planos de consulta poderão ter uma qualidade inferior e o seu desempenho pode vir a ser prejudicado, por outro lado em alguns casos, você pode melhorar os planos de consulta criando estatísticas adicionais com a instrução CREATE STATISTICS. Essas estatísticas adicionais podem capturar correlações estatísticas que o otimizador de consulta não conseguiu identificar ao criar estatísticas para seus índices ou colunas.
• Auto_update_statistics: Em alguns casos, ao deixar esta opção desativada, podemos melhorar o plano de consulta e, portanto, o desempenho das consultas atualizando estatísticas mais freqüentemente do que quando a opção AUTO_UPDATE_STATISTICS está ativada. Neste ponto, eu costumo por padrão para uso dos comando Update_statistics ou Sp_updatestats para realizar o processo de atualização das Estatísticas de Tabelas de Sistema existente no banco de dados.

1. Você já verificou se sua consulta ou conjunto de consultas utiliza algum tipo de recurso ou funcionalidade como, por exemplo: variáveis locais, tabelas temporárias, cursores, linked-server, etc.
2. Existe alguma análise de fragmentação de dados? Todos os índices existentes em seu banco de dados estão sendo utilizados? Qual é o valor definido para o fill factor de seus índices?
3. Processos como reorganização ou reconstrução de índices foram realizados?

 

1. Existe algum tipo de particionamento de dados, tanto no nível de banco de dados, como também, em relação a discos rígidos, arquivos ou grupos de arquivos?

 

1. O banco de dados TEMPDB está armazenado na mesma unidade de disco dos demais bancos de dados?

 

1. Existe algum monitoramento ou análise do Buffer Cache que as transações requisitam para o SQL Server?

 

Pois bem, estas e muitas outras questões podem ser utilizadas, tenho em mente que as informações reunidas por questionário poderá ajudar a determinar como uma consulta é executada, identificando todos os elementos físicos e lógicos que podem estar envolvidos, desde otimizador de consulta existe no SQL Server, como em paralelo os elementos de Hardware e Software que podem estar sendo processados.

 

Conclusão

 

Usando essas informações, você poderá determinar se podem ser feitas melhorias de desempenho, após algum tipo de alteração em sua Infraestrutura, Aplicação ou Banco de Dados, o que poderá obrigar o processo de mudanças em suas consultas, índices nas tabelas ou talvez modificando o projeto de banco de dados.

 

Mais uma vez obrigado por sua visita, espero que você tenha gostado deste post, com certeza totalmente diferente do que venho compartilhamento nos últimos meses.

 

Encontramos-nos em breve.

 

Até mais.