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.

Anúncios
  1. Cristiane Silva
    23/10/2013 às 14:29

    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!

    • 23/10/2013 às 14:50

      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.

  2. Cristiane Silva
    23/10/2013 às 15:12

    Vitor esta com compatibilidade SQL Server 2000.

    • 23/10/2013 às 16:53

      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?

  1. No trackbacks yet.

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 )

Imagem do Twitter

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

Foto do Facebook

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

Foto do Google+

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

Conectando a %s

%d blogueiros gostam disto: