DESAFIO DO GORDO – Troubleshooting de Performance

Galerinha,

Gostei muito da participação de vocês no último DESAFIO DO GORDO e muitos foram na direção correta para a resolução do problema.

Inicialmente necessitamos descobrir qual a consulta T-SQL que está exigindo tantos recursos do servidor de banco de dados e causando a lentidão descrita pelos usuários.

Existem várias maneiras de monitorarmos o ambiente para encontrar consultas que executam várias operações de leituras e gravações na memória, porém uma das mais rápidas e eficientes é através da utilização das DMV’s.

Utilizando algumas DMVs, conseguimos encontrar a consulta que solicita uma grande quantidade de memória do servidor de banco de dados:

SELECT
ES.session_id,
ES.[host_name],
ES.login_name,
(SELECT DB_NAME(ER.database_id)) as databasename,
ER.logical_reads,
(SELECT [Text] FROM master.sys.dm_exec_sql_text(EC.most_recent_sql_handle )) as sqlscript
FROM
sys.dm_exec_sessions ES
INNER JOIN
sys.dm_exec_connections EC
ON
EC.session_id = ES.session_id
INNER JOIN
sys.dm_exec_requests ER
ON
EC.session_id = ER.session_id
WHERE
EC.session_id <> @@SPID –and
ORDER BY
logical_reads DESC
 

desafio1

Analisando o plano de execução da consulta encontrada, é possível notar que existe um operador extremamente custosos sendo utilizado pelo otimizador de consulta, chamado TABLE SPOOL.

desafio2

Conforme descrito no excelente artigo Working with tempdb, uma consulta utiliza excessivamente o TEMPDB caso o otimizador escolha os seguintes operadores:

  • SORT
  • HASH MATCH
  • SPOOL

Com a análise detalhada do plano de execução, é possível esclarecer que a alta utilização do TEMPDB é devido ao operador TABLE SPOOL que o otimizador de consulta decidiu adotar para a consulta encontrada.

Mas e o contador de performance Page Life Expectancy?

O contador de performance Page Life Expectancy descreve quanto tempo (em segundos) uma página de dados é mantida no BUFFER CACHE do SQL Server.

O valor recomendado pela Microsoft é de 300 segundos, ou seja, uma página de dados permanecerá na memória por apenas 5 minutos.

Esse tempo parece saudável, mas analise com mais cautela o nosso cenario atual.

Conforme descrito anteriormente, o servidor de banco de dados possui 96 GB de RAM, ou seja, caso o contador Page Life Expectancy esteja em 300, a cada 5 minutos ocorre a leitura de 96 GB pelo SQL Server.

Tenho certeza de que todos estão percebendo que o valor recomendado pela Microsoft não é um bom parâmetro certo?

A melhor maneira de saber qual o valor exato para seu ambiente é monitorando o mesmo quando não houver problemas e comparar os valores durante os relatos de lentidão por algum usuário.

Em nosso cenário atual temos uma situação ainda mais crítica, já que o contador está com o valor de 120, o que significa que as páginas de dados são mantidas em memória por apenas 2 minutos.

Este valor abaixo do recomendado pela Microsoft confirma que no momento em que a consulta descrita anteriormente é executada, ocorre o processo de paginação pelo SQL Server.

Como o cenário descrito é esporádico, podemos supor que não há pressão de memória e que o problema é somente durante a execução da consulta encontrada anteriormente através das DMVs.

A minha primeira sugestão seria a atualização das estatísticas nas tabelas utilizadas pela consulta custosa e caso não haja melhora após o termino deste processo, sugiro que a consulta seja analisada para verificar se algum índice eficiente pode ser criado, diminuindo assim a necessidade de utilizar o TEMPDB e também de carregar paginas desnecessárias para o BUFFER CACHE.

Espero que tenham gostado desse desafio e não deixem de se inscrever no blog para receber todos os posts publicados.

Grande abraço.

4 ideias sobre “DESAFIO DO GORDO – Troubleshooting de Performance

  1. Cristiane Silva

    Olá Vitor!
    Muito interessante o post e mais ainda a query.
    Porém não consegui rodas no SQL Server 2008 R2, informa que não existe a view na Master chamada sys.dm_exec_sql_text.
    Essa View não existe mesmo no Sql 2008 R2?
    Obrigada!

    Resposta
    1. vitortff Autor do post

      Oi Cris, tudo bem?

      Você está utilizando o banco de dados com compatibilidade nativa do SQL Server 2008 ou manteve com compatibilidade do SQL Server 2000?

      Grande abraço.

      Resposta
    1. vitortff Autor do post

      Cris,
      No SQL Server 2000 não existiam as DMVs e como está utilizando a compatibilidade com esta versão, podem ocorrer problemas mesmo.
      Pode trocar para o SQL Server 2008?

      Resposta

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.