Um pouco sobre o Execution plan (plano de execução) do SQL Server
Como você lê os planos de execução? Da direita para a esquerda, esquerda para a direita ou verificando
os custos? Ou o que dizer de objetos como verificações de índice, verificações de tabela e pesquisas?
Embora o SQL Server geralmente gere um bom plano, às vezes não é inteligente o suficiente para validar
seus planos e corrigir os fracos.
Existem dois tipos de planos de execução:
Estimated execution plan: Os planos estimados fornecem uma estimativa do trabalho que o SQL Server
deve executar para obter os dados.
Actual execution plan: Os planos de execução reais são gerados após a execução das consultas TransactSQL ou dos lotes. Por esse motivo, um plano de execução real contém informações de tempo de execução, como as
métricas reais de uso de recursos e quaisquer avisos de tempo de execução.
Processamento de dados
O plano estimado e o plano real na maioria das vezes, será o mesmo, mas poderá ser diferente devido a
alterações nas estatísticas, alterações relacionadas ao esquema ou alterações nos dados. Você sempre deve verificar o
plano de execução real ao solucionar problemas.
Leia o plano de execução corretamente para identificar o ponto real. Comece analisando o fluxo de dados
e não o custo. Não pense nas leituras lógicas ou físicas. É importante reduzir o número de operações de entrada / saída
(E / S). Você sabe que acessar o armazenamento é o mais lento dos recursos de hardware; portanto, tente minimizar essa
atividade. Então, como você explora as estatísticas e o plano de execução mostra isso? Sim! Confira as linhas de direção
passando o mouse sobre o indicador da direita para a esquerda. Ele exibe o número de registros e o tamanho dos dados.
Cada linha é mais grossa ou mais fina com base no volume de dados retornado pela operação, conforme mostrado na
ilustração a seguir:
Se você tiver muitos objetos, precisará de uma maneira melhor de obter uma visão geral da quantidade de
dados processados para cada operação.
Caso precise reduzir o stress de E / S, consulte o valor SET STATISTICS IO ON T-SQL para obter uma
ideia geral do uso de E / S para a consulta. Você deve definir isso antes da execução da consulta no SSMS para ver o
resultado alternar para a guia mensagem no painel de resultados. Deve ser semelhante ao seguinte resultado:
O resultado de “logical reads 14”, mostra que o SQL Server lê páginas de 112KB (14 * 8 KB) para
obter os dados da memória. Ao otimizar a consulta, não ignore as leituras lógicas, pois isso pode ser físico e lógico nos
servidores ativos. Você nunca sabe se as páginas às quais você está se referindo estão na memória ou no disco. O
objetivo deve ser reduzir o número de operações de leitura cumulativas.
Estimativa versus planos reais
Após a geração do plano real, passe o mouse sobre qualquer operação ou unidade lógica no plano de
execução. Consulte a imagem a seguir para ver o tipo de operações físicas, como os resultados de busca por índice em
cluster ou varredura. Procure o número de execuções e o número real e estimado de linhas. Evite analisar o custo de
operações individuais, porque essa é uma estimativa em segundos com base no hardware antigo e pode não fornecer
detalhes precisos. Na imagem a seguir, o operador Clustered Index Seek foi executado uma vez para obter 100 registros,
e o SQL estima 356 registros. A diferença pode dever-se a estatísticas desatualizadas ou ao desempenho da consulta.
Execution iterations (Iterações de Execução)
A imagem a seguir mostra o número estimado de execuções 1154121 vezes em um índice de cluster para
obter um registro. Isso é significativo. Embora o custo do operador seja menor, e poderíamos simplesmente ignorá-lo,
observando as outras operações com custos mais altos, isso pode ter se mostrado uma opção dispendiosa. Essas
operações têm um alto impacto no desempenho da consulta, apesar de terem um índice de cluster. As pesquisas de ID de
linha (RID) são uma operação semelhante para o heap.
Banco de dados temporário
A diferença entre o número estimado e real de registros é outra coisa que você deve considerar. Uma
estimativa incorreta pode resultar em uma consulta com memória limitada alocada. Quando isso acontece, ele usa um
banco de dados temporário (tempdb) para realizar as tarefas. A escolha incorreta de um operador ou plano do SQL
Server pode resultar em operações e extrações lentas das consultas, conforme mostrado na imagem a seguir. O número
real de registros é o mesmo, mas as estimativas são diferentes. Isso pode ser devido a estatísticas desatualizadas ou
ausentes. Observe que as variáveis da tabela não possuem estatísticas; portanto, o plano sempre retorna 1 e 1K em
novos releases até que a opção de recompilar seja usada. Portanto, variáveis de tabela não são uma boa opção para um
grande número de registros.
Sort operator(Operador de classificação)
Você precisa considerar o impacto da classificação. O operador de Sort é usado principalmente para as
seguintes funções: agregação, junção de mesclagem ou ordem por cláusula. Isso pode não ter impacto com apenas
alguns registros, mas, com cada registro adicional, o processamento diminui. Tente evitar a classificação ou não use a
ordem por cláusula. Se a classificação for necessária, use a grade do aplicativo para fazer a classificação em vez de
enviar dados classificados para um aplicativo.
Operador de spool
Outro operador importante que você precisa observar é o spool. O spool é um operador lento porque
armazena objetos ocultos ou temporários ou tabelas de trabalho no tempdb. Isso também diminui a velocidade com
operadores específicos que fazem com que ela seja religada ou retrocedida. Existem muitos tipos de spools no SQL
Server, como Eager, Lazy, Table / Index e assim por diante. O SQL Server usa o spool quando é melhor consultar uma
tabela de trabalho temporária do que retornar à tabela de origem para conjuntos de resultados intermediários. A imagem
a seguir mostra um exemplo
Hash and nested loop operators (Operadores de hash e loop aninhado)
Como eles apresentam bom desempenho em pequenos conjuntos de registros, hash e loops aninhados são
os próximos operadores que você deve considerar. No entanto, para grandes conjuntos de registros ou quando há uma
grande diferença entre os planos estimado e real, esses operadores podem criar um grande impacto, pois podem usar
tempdb em vez de memória. O SQL Server publica o seguinte aviso nos detalhes do operador: “operador usou tempdb
para espalhar dados durante as execuções”. Se isso acontecer, preste atenção às estatísticas. Com uma estimativa
incorreta, o loop fica aquém da alocação de memória ou continua repetindo. Veja a memória alocada para a execução.
Para visualizar a alocação de memória, abra a caixa de propriedades selecionando o ponto inicial do plano de execução
(da esquerda para a direita). Se nada estiver anormal, o ajuste da consulta deve ser a escolha ideal.
Conclusão
Seu primeiro objetivo para otimizar uma consulta deve ser reduzir leituras e gravações gerais (ou seja, E /
S no disco). Não se esqueça de leituras lógicas para leituras e gravações na memória. Reduzir a E / S resolve a maior
parte do problema e as consultas são executadas muito mais rapidamente.
Em seguida, observe outras operações caras devido à atividade no tempdb. Lembre-se, o tempdb é usado
por muitas operações e sempre é caro. Procure por retrocessos analisando o número de execuções de qualquer operação,
spools, sort e loops.
Estes são caros quando usados com tempdb.
Não se esqueça de revisar os avisos de cada operador, porque
eles fornecem boas pistas. Embora esta postagem não discuta o operador de índice ausente, isso não significa que você
possa ignorá-lo.
Revise-o, mas não crie o índice às cegas. Verifique os outros índices disponíveis nas mesmas colunas e
considere o impacto nas consultas em execução no seu banco de dados.
"A vida é a experiência mais fascinante que existe e a maior inspiração para o mundo e as pessoas."
"A vida é a experiência mais fascinante que existe e a maior inspiração para o mundo e as pessoas."
Nenhum comentário:
Postar um comentário