Nesta quinta parte sobre desempenho do servidor SQL Server, veremos como funcionam os planos de execução, seu funcionamento interno e como se comportam as consultas problemáticas. O artigo anterior pode ser conferido neste link.

Analisando planos de execução

O SQL Server pode expor o plano de execução real para todas as consultas executadas. A árvore de comando real fica serializada em formato XML e há várias maneiras de obtê-la:

• SET SHOWPLAN_XML ON utilizado na sessão onde for executada a consulta.
• Use o SQL Profiler, consulte Exibindo planos de execução usando o SQL Server Profiler.
• Ativar o “Incluir plano de execução real” no SSMS; consulte o link exibindo planos de execução gráfica.
• Use a função sys.dm_exec_query_plan para obter resultados a partir de um XML. Esse plano pode ser obtido a partir de DMVs como sys.dm_exec_requests (consultas atualmente em execução) ou sys.dm_exec_query_stats (consultas já executadas).

Não vou entrar em muitos detalhes sobre como analisar um plano de consulta, porque o assunto é demasiado vasto para este artigo. Planos de execução capturam quem fez a consulta, ou seja, quais operadores executou, quantas vezes foi invocado cada operador, a quantidade de dados resultante de cada processo e a quantidade de dados que produziu e quanto tempo real que levou para cada operação. Ao contrário da análise de tempos de espera, a análise da execução se concentra nos tempos de execução e no tempo real de CPU consumido daqui pra frente. O display gráfico do SSMS simplifica as informações em um plano de execução XML para torná-lo mais legível, e a representação gráfica torna mais fácil solucionar os problemas de desempenho. Setas grossas representam grandes transferências de dados entre os operadores, o que significa que os operadores precisavam olhar para um monte de linhas para que pudessem produzir sua saída. É desnecessário dizer que, quanto mais dados um operador precisa processar, mais tempo será preciso.

Parando o cursor sobre algum dos operadores de exibição, uma informação de contexto com detalhes sobre o operador será exibida. Um grande número real mostra a quantidade de dados que estão sendo processados. O pop-up inclui também o número de linhas estimadas e a quantidade de dados que o SQL Server havia previsto para o operador processar, com base nas estatísticas da tabela. A discrepância entre o estimado e o real muitas vezes é responsável por um mal desempenho e pode ser rastreado com estatísticas desatualizadas sobre a tabela.
Outros valores interessantes do plano de execução são os operadores. Alguns operadores são inerentemente caros para o servidor, por exemplo, operadores de classificação. Um operador que gera muito custo para o servidor, juntamente com uma entrada de dados grande, é geralmente a causa de um tempo de execução enorme. Mesmo que você não saiba quais operadores custam muito para o servidor, o plano de execução irá exemplificar o custo associado a cada operador, e com essa informação é possível ter uma ideia de onde os problemas podem se encontrar.

Identificando consultas problemáticas

O SQL Server armazena as estatísticas relacionadas ao tempo de execução para a maioria das consultas que foram executadas com sucesso em sys.dm_exec_query_stats. Essa DMV mostra o número de vezes que uma consulta foi executada, seu tempo total /max/min /última CPU ativa (tempo de trabalho), tempo máximo total/min/horário/tempo decorrido total/máx/min/últimas leituras e gravações lógicas, total/máx/min/último número de linhas retornadas. Esse tipo de informação é um tesouro para identificar problemas em um aplicativo:

Grande quantidade de execução de consultas

Consultas que são executadas com frequência são as mais sensíveis para bom desempenho do servidor. Mesmo que elas sejam realizadas em um tempo aceitável, se forem executadas muitas vezes seguidas, pequenas melhorias em cada execução individual podem render uma velocidade global significativa. Mas o melhor mesmo é olhar de forma crítica para os motivos pelos quais a consulta é executada com frequência. Mudanças de aplicativos podem reduzir o número das execuções.

Grande leituras lógicas

Uma consulta que faz a varredura de grandes quantidades de dados vai ser uma consulta lenta, não há dúvidas sobre isso. O foco deve ser na redução da quantidade de dados indexados, pois normalmente o problema é um índice ausente. Um plano de consulta “ruim” também pode ser um problema. Um grande número de leituras lógicas é acompanhado por um alto tempo de trabalho das consultas, mas a questão não é o alto uso da CPU e o foco deve ser em grande leituras lógicas.

Grandes leituras físicas

Este é basicamente o mesmo problema das grande leituras lógicas, mas também indica que o servidor não possui memória RAM suficiente. Felizmente este é, de longe, o problema mais fácil de resolver: basta adicionar mais memória RAM ao servidor. Talvez você ainda tenha um grande volume de leitura lógica e deverá lidar com esse problema também, mas, em se tratando de design do aplicativo, normalmente é tão fácil como encomendar 1 TB de RAM.

Elevação do tempo de trabalho com baixa leitura lógica

Este caso não é muito comum e indica uma operação muito intensiva da CPU, mesmo com poucos dados a serem processados. O processamento de XML é o culpado típico, e você deve tentar realizar as operações intensivas da CPU no cliente, e não no servidor.

Tempo decorrido com valor alto

Uma consulta que demora muito tempo para ser executada, mas utiliza alguns ciclos de CPU, é indicativo de um bloqueio. Esta pode ser uma consulta que esteve suspensa a maior parte do tempo, à espera de alguma coisa. A análise deverá indicar a causa do problema e o recurso que está sendo esperado.

Total de linhas com valor alto

Grandes conjuntos de resultados solicitados pelo aplicativo podem ser justificadas em um baixíssimo número de casos. Este problema deve ser abordado no design do aplicativo.

Encontrar o problema em uma consulta usando sys.dm_exec_query_stats torna-se um simples exercício de ordenação dos critérios desejados (por contagem de linhas executadas, por leituras lógicas, pelo tempo decorrido etc.). Para obter o texto puro da consulta em uso, utilize sys.dm_exec_sql_text e para obter o sys.dm_exec_query_plan da consulta em execução no plano, faça:

select st.text,
pl.query_plan,
qs.*
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan(qs.plan_handle) as pl;

Se você não sabe qual tipo de consulta deve procurar, para começar, o meu conselho é concentrar-se na seguinte ordem:

• Contagem de alto tempo de execução: identificar quais consultas são executadas, na maioria das vezes é, na minha opinião, mais importante do que identificar quais consultas são particularmente lentas. Na maioria das vezes, as consultas encontradas em uma fila de execução são uma surpresa e simplesmente limitam o rendimento que a contagem do tempo de execução ajudaria a ganhar em termos de desempenho.
• Grandes leituras lógicas: grandes varreduras de dados são o culpado habitual para a maioria dos problemas de desempenho do servidor SQL. Essas grandes varreduras podem ser causadas por índices perdidos, por um modelo de dados mal projetado, por planos de execução mal planejados, por estatísticas desatualizadas, por parâmetros não utilizados e várias outras causas.
• Tempo decorrido alto com baixa carga de trabalho: consultas de bloqueio não custam muito ao servidor, mas os usuários do aplicativo não se importam se o tempo em que esperam os resultados na frente da tela do computador foi gasto pelo servidor ativo ou bloqueado.

Índices ausentes

Repetidamente tenho dito que os problemas de desempenho em sua maioria são causados por índices ausentes e o SQL Server tem um tratamento de erro apropriado para esses casos:

O recurso de índices ausentes do SQL Server apresenta objetos de gerenciamento dinâmico e planos de execução para fornecer informações sobre quaisquer índices ausentes que poderiam melhorar o desempenho das consultas enviadas ao servidor SQL Server.

Pessoalmente, não costumo focar minha análise na função do recurso de índice ausente. Para começar, esse recurso também tem suas limitações, mas o mais importante é que ele é um dos pontos de partida para encontrar uma solução, sendo um dos primeiros locais onde é possível procurar por uma causa para os problemas de desempenho. Sempre começo com a identificação de consultas problemáticas em primeiro lugar, procurando por aquelas que produzem o maior tempo de resposta. Uma vez que uma consulta problemática é identificada como a causadora de problemas, os DMVs de índices podem ser consultados para identificar um candidato com índices ausentes. O recurso de índice ausente não sabe o que a consulta está fazendo do ponto de vista da lógica de negócios da aplicação. Revendo a consulta, poderemos encontrar uma solução melhor do que aquilo que o recurso automatizado de índice ausente poderia recomendar.

Administradores experientes criam uma automatização para inspecionar periodicamente os índice ausentes e encontrar potenciais pontos de problemas. Essa é uma boa abordagem proativa. Mas, na maioria dos casos, os problemas de desempenho são pontuais e imediatos, e encontrá-los usando a análise de espera ou de execução de consulta dos dados é um processo mais direto. Para uma discussão mais aprofundada da falta índices DMVs, recomendo a leitura do documento sp_BlitzIndex.

Desempenho do TEMPDB

O TEMPDB é o banco de dados especial do SQL Server usado para armazenar todos os dados transitórios, incluindo coisas como tabelas temporárias, cursores estáticos, variáveis de tabela, tipos de execução, tabelas internas de trabalho, armazenamento de versão e muito mais. Mesmo que os problemas que podem ocorrer com o TEMPDB sejam, em última análise, relacionados a CPU, IO ou contenção de dados, o TEMPDB merece um tópico especial, porque alguns desses problemas podem ser entendidos em um nível superior como problemas TEMDB, ao contrário de, por exemplo, problemas de contenção de baixo nível. O TEMPDB também tende a manifestar um conjunto de problemas inteiramente próprios, especialmente devido à elevada taxa de criação e destruição de objetos do TEMPDB. O site do MSDN tem um tópico chamado Otimizando o desempenho do TEMPDB que contém alguns conselhos práticos, e Cindy Bruto compilou uma coleção de Melhores Práticas do SQL Server TEMPDB.

No geral, tentei enfatizar a importância de medir e entender as características de desempenho do SQL Server em vez de seguir cegamente  conselhos sem nenhum motivo forte para tal. Mas aqui vai um que dou sempre, sem qualquer consideração secundária: certifique-se de que o TEMPDB está sendo armazenado em arquivos de tamanhos idênticos, com características de crescimento também idênticas. Passei  muitas horas perseguindo problemas difíceis de diagnosticar para descobrir que eles eram, em última instância, causados por arquivos TEMPDB desalinhados.

Eu recomendaria também como uma leitura obrigatória a resposta de Martin Smith a esta pergunta presente no site dba.stackexchange.com: Qual é a diferença entre uma tabela temporária e uma tabela variável no SQL Server? A resposta traz um grande detalhamento, não só do ponto de vista de uso do TEMPDB, mas também o impacto sobre  bloqueios, compilação, paralelismo e indexabilidade, todos sendo componentes críticos do desempenho do servidor.

Contadores de desempenho do SQL Server

Os contadores de desempenho do SQL Server oferecem uma perspectiva diferente sobre o desempenho do servidor em relação ao que a análise de espera e execução faz. A maior vantagem dos contadores de desempenho é que eles são extremamente fáceis de coletar e armazenar, sendo então o motivo pelo qual acabam sendo a escolha natural para o monitoramento de longo prazo. Olhando para os números do contador de desempenho atuais, podemos verificar potenciais problemas, mas a interpretação dos valores está sujeita à experiência do analista e há um monte de lendas e mitos em determinados valores mágicos que poderão ser apresentados. Para os problemas de desempenho na análise em um servidor ao qual você tem acesso e está se comportando mal, uma abordagem mais direta usando análise dos valores de espera e Estatísticas de Execução é a mais recomendada. Onde os contadores de desempenho brilham é na comparação do comportamento atual do servidor com o comportamento passado em determinada época, para o qual, obviamente, você precisa ter contadores de desempenho coletados e guardados para referência. Essas referências devem ser parte de uma linha de base estabelecida, cuja verificação do comportamento do servidor sob uma carga de trabalho quando o servidor estava se comportando corretamente e não estava passando por problemas. Se você não tem uma linha de base, considere estabelecer uma para que seja possível solucionar problemas de desempenho mais rapidamente no futuro.

O carro-chefe dos contadores de desempenho de uma infraestrutura SQL Server são os contadores de desempenho utilizando o utilitário logman.exe. Para uma introdução rápida, leia Two Minute Drill: logman.exe. Recomendo que você se familiarize com essa ferramenta. Sou a favor de utilizá-la através da interface gráfica (perfmon.exe), pois, como ela é uma ferramenta de interface gráfica para a ferramenta de linha de comando, será mais fácil a criação de scripts sem ter de digitar (e acertar) uma infinidade de comandos. Dizer a alguém para “executar o script x” é sempre mais fácil do que dizer “clique no botão, selecione tal item da lista, em seguida selecione o menu B, clique duas vezes no meio do quadrado vermelho etc.”. A ferramenta perfmon.exe faz da ferramenta uma escolha acertada para auxiliar na exibição de contadores de desempenho.

Os contadores de desempenho, caso recolham dados suficientes, também são uma excelente ferramenta para a resolução de problemas. O display gráfico torna fácil detectar contadores que tendem a dar problema em conjunto ou em um padrão anticorrelacionados. Outros padrões também se tornaram evidentes no gráfico de visualização, como o lag. Um contador de pico é sempre precedido por um outro aumento de recursos do servidor. Em vez de ter um cientista de dados fazendo a análise fatorial de seus contadores, seus próprios olhos são a melhor ferramenta para essa análise.

Na última parte deste artigo, vamos analisar os tipos de contadores de desempenho do SQL Server, os contadores de desempenho de IO, memória e CPU.

***

Artigo traduzido pela Redação iMasters com autorização do autor. Publicado originalmente em http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/