O Plano de execução Perfeito (execution plan)

p011_universal-plano-perfeito_200x99Quem está às voltas com otimização de consultas sql deve conhecer o básico de plano de execução, que é o roteiro seguido pelo mecanismo de banco de dados (database engine) do SQL Server para processar a consulta. Ao analisar o plano de execução podemos encontrar possíveis gargalos e tomar decisões sobre melhorias.

1. Introdução

Nos fóruns de SQL Server em que participo às vezes é necessário solicitar ao autor do tópico que obtenha o plano de execução da consulta em formato XML e o disponibilize em algum repositório público. Entretanto, nem sempre o autor do tópico sabe como realizar isso ou até mesmo nem sabe o que é o plano de execução. E então tenho que acrescentar no tópico uma explicação sucinta, com passo a passo; algo que nem sempre é claro o suficiente para o autor do tópico. Assim, há tempos que estou para escrever roteiro sobre o que é o plano de execução e como salvá-lo em arquivo no formato XML mas só agora, após a criação do Porto SQL, resolvi levar a cabo tal tarefa.

Do objetivo inicial do artigo, roteiro de como gerar o plano de execução em arquivo no formato XML, peguei ritmo e resolvi acrescentar capítulo com informações básicas sobre como analisar plano de execução, à procura de problemas e/ou gargalos. Somente como introdução ao tema, pois ao final do artigo há relação de material de estudo (documentação BOL, livros e vídeos) que recomendo para aqueles que tenham interesse em aprofundar no estudo dos planos de execução.

1.1. O que é o plano de execução?

Plano de execução é um plano de trabalho gerado pelo otimizador de consultas contendo as etapas necessárias para a obtenção do resultado da consulta. Ele é composto por operadores que são executados em uma ordem específica. No plano de execução consta a ordem na qual as tabelas são acessadas, quais índices utilizar, algoritmos de junção das tabelas, e assim por diante.

Exemplo de plano de execução (visualização gráfica):

p005_image2

1.2. Como é gerado o plano de execução?

Para transformar o código de uma consulta em um plano de execução há algumas etapas, que são executadas pelo relational engine do SQL Server: o código T-SQL é analisado e processado pelo otimizador de consulta (query optimizer), que gera o plano de execução. O plano de execução é então enviado (em um formato binário) para o storage engine, que usa esse plano para recuperar ou modificar os dados.

1.2.1. Query parsing

p011_parserAo receber o código T-SQL para processamento, o primeiro passo é analisar se o código está escrito corretamente, de acordo com sintaxe e semântica T-SQL. Essa etapa é realizada pelo Parser. Estando o código T-SQL correto, a saída dessa etapa é denominada de parser tree, que contém os passos lógicos para a execução da consulta SQL.

1.2.2. Query binding

p011_algebrizerA etapa seguinte é o processo Algebrizer, que recebe a parser tree e verifica a existência dos nomes de objetos referenciados na consulta (tabelas, colunas etc), além de outras tarefas. Estando tudo correto, a saída desta etapa é o binário chamado de query processor tree, que é então repassado para a próxima etapa.

1.2.3. Query optimization

p011_optimizerO otimizador de consultas recebe a query processor tree e, a partir dela, gera e avalia vários planos de execução e, dentre os planos de execução gerados, escolhe aquele que acha que é o mais rápido e que utiliza menos recursos (CPU e I/O). Necessariamente o otimizador não gera o melhor plano de execução, pois ele tem um limite de tempo para gerar e analisar as várias combinações. Atingido esse limite de tempo, o otimizador tem que decidir qual o melhor plano de execução, dentre os gerados.

Uma vez que o otimizador de consultas gere o plano de execução, ele é então armazenado em uma área de memória denominada de plan cache.

p011_parser algebrizer optimizer

Cabe salientar que a saída da etapa anterior contém um hash, que é um valor representando o código T-SQL. O optimizer utiliza esse valor para verificar se o código T-SQL foi anteriormente analisado, já existindo então o plano de execução no plan cache. Se existir, o plano anteriormente gerado é utilizado.

1.3. Como visualizar o plano de execução?

Para visualização há duas possibilidades de plano de execução: estimado e real. O plano de execução estimado é o plano de execução gerado pelo otimizador de consultas mas sem que tenha sido executado. Desta forma ele não contém as informações que são obtidas somente durante a execução do plano de execução. Já o plano de execução real contém todas as métricas que são obtidas na execução do plano de execução.

Plano de execução estimado. Para exibir graficamente o plano de execução estimado, siga os passos seguintes:

a) Na barra de ferramentas clique no botão Nova Consulta; p011_botao Nova consulta

b) Insira o código da consulta para a qual você deseja exibir o plano de execução estimado;

c) Na barra de ferramentas clique no botão Exibir Plano de Execução Estimado
p011_botao plano execução estimado

O plano de execução estimado é exibido na aba Plano de execução do painel de resultados.

d) Para alterar a exibição do plano de execução, clique com o botão direito do mouse no plano de execução e selecione Ampliar, Reduzir, Zoom Personalizado ou Ajustar Nível de Zoom. Ampliar e Reduzir permitem ampliar ou reduzir o plano de execução com valores fixos. Zoom personalizado permite que você defina sua própria ampliação da exibição, como ampliar em 80 por cento. Ajustar Nível de Zoom aumenta o plano de execução para se ajustar ao painel de resultados.

Plano de execução real. Para exibir graficamente o plano de execução real siga os seguintes passos:

a) Na barra de ferramentas do Management Studio clique no botão Nova Consulta;
p011_botao Nova consulta
b)  Insira a consulta para a qual você deseja exibir o plano de execução real;

c) Na barra de ferramentas clique no botão Incluir Plano de Execução Real;
p011_botao plano execução reald) Execute a consulta clicando no botão de barra de ferramentas Executar. O plano gerado pelo otimizador de consultas é exibido na aba Plano de Execução no painel de resultados. Movimente o mouse sobre os operadores lógicos e físicos para exibir a descrição e propriedades dos operadores na dica de tela exibida;

e) Você pode alterar a exibição do plano de execução clicando com o botão direito no plano de execução e selecionando Ampliar, Reduzir, Zoom Personalizado ou Ajustar Nível de Zoom. Ampliar e Reduzir permitem ampliar ou reduzir o plano de execução, enquanto Zoom Personalizado permite definir seu próprio zoom, como ampliar em 80 por cento. Ajustar Nível de Zoom aumenta o plano de execução para se ajustar ao painel de resultados.

1.4. Como salvar o plano de execução?

Você pode salvar a visualização do plano de execução em um arquivo XML para posterior análise, para enviar a terceiros, etc. Independente dos motivos, é um processo bem simples. As instruções para salvar o plano de execução foram extraídas do documento “Salvar um plano de execução em formato XML”.

a) Gere um plano de execução estimado ou um plano de execução atual usando Management Studio;

b) Na aba Plano de execução do painel de resultados, clique com o botão direito do mouse no plano de execução gráfico e escolha Salvar Plano de Execução Como;

c) Na caixa de diálogo Salvar Como verifique se a opção Salvar como tipo está definida como Arquivos de Plano de Execução (*.sqlplan);

d) Na caixa Nome do arquivo, forneça um nome no formato <nome>.sqlplan e clique em Salvar.

2. Análise de plano de execução

A forma como o plano de execução deve ser lido depende dos objetivos ou informações que se necessitam obter. Como mencionado no início deste artigo, o plano de execução é como um diagrama composto de processos (operadores) que estão interligados através de fluxos de dados. Ao posicionar o mouse sobre esses objetos, as respectivas propriedades são exibidas, nos informando detalhes da execução do objeto.

2.1. Operador

Existem cerca de 85 operadores, mas a maioria das consultas utiliza um pequeno subconjunto deles. Os operadores são representados no plano de execução por ícones, onde cada operador implementa um algoritmo específico.

Stream Aggregate
p011_stream-aggregate-32x
Concatenation
p011_concatenation-32x
Index Seek
p011_index-seek-32x
Index Scan
p011_nonclustered-index-scan-32x
RID Lookup
p011_rid-nonclust-locate-32x

Os operadores que compõem o plano de execução, e suas interligações, nos dizem exatamente o caminho que o query optimizer escolheu para resolver a consulta.

p011_trecho de codigo

Cada operador possui um componente lógico e um físico, onde o componente lógico descreve a ação que o operador realiza naquele ponto e o componente físico a forma de implementação. Geralmente o nome do componente físico é seguido do nome do componente lógico, este entre parênteses. Por exemplo Nested Loops (Inner Join), onde Nested Loops é o operador utilizado para a operação lógica Inner Join:

p011_trecho de operador

Abaixo de cada operador é exibido percentual do custo estimado da execução daquele operador em relação ao custo total do plano de execução. É somente uma estimativa e pode estar errada…

As propriedades do operador podem ser exibidas temporariamente enquanto se mantém o mouse sobre o operador; mas podem também ser exibidas de forma estática ao clicar sobre ele.

Os operadores se classificam em tipos streaming e blocking. O operador do tipo streaming provê um fluxo contínuo de dados, isto é, à medida que lê dados do(s) operador(es) à direita também envia dados para o operador imediatamente à esquerda. Já os operadores do tipo blocking leem todos os dados, os processam e somente após enviam para o operador imediatamente à esquerda. Há também os operadores semi-blocking.

p011_index-seek-32x Streaming p011_stream-aggregate-32xBlocking

2.2. Fluxo de dados

As setas indicam o fluxo de dados entre operadores e a espessura da linha representa a quantidade de dados transmitidos: quanto mais grossa, maior o volume de dados. Ao posicionar o mouse sobre a linha de fluxo de dados são exibidos o número de linhas (estimado e/ou real) e tamanho da linha.

p011_fluxo de dados

2.3. O que analisar?

O que analisar, ou como analisar, depende de qual é o objetivo do acesso ao plano de execução da consulta. Uma pergunta frequente é se o plano de execução deve ser lido da direita para a esquerda ou, ao contrário, da esquerda para a direita. Entretanto, uma prática recomendada é começar esmiuçando o primeiro operador à esquerda (Result Showplan). A seguir, o usual é seguir o fluxo de dados, lendo o plano de execução da direita para a esquerda e de cima para baixo e ir comparando o código da consulta SQL com a sequência de operações que o otimizador de consultas escolheu para montar o plano de execução.

Geralmente consultamos o plano de execução quando detectamos que a execução da consulta está lenta, à procura de algum gargalo, mas também é útil consultá-lo à medida que se desenvolve o código da consulta; principalmente para consultas complexas, que envolvam várias tabelas e processos. Aliás, um bom hábito para compreender como funciona o query optimizer é sempre exibir o plano de execução a cada consulta criada, inclusive à medida que se testam variações no código.

2.3.1. Operador Inicial

O operador inicial, primeiro à esquerda, nos fornece muitas informações sobre o plano de execução e sobre o processo pelo qual o otimizador de consultas passou para chegar a esse plano. O nome desse operador é Result Showplan, embora ele venha identificado externamente com o nome da instrução da consulta mais externa: SELECT, UPDATE etc.

Considerando-se a execução do código #2 (vide capítulo Apêndice), temos o seguinte plano de execução:

p011_codigo #2 - plano de execucao

O plano de execução contém somente 2 operadores: Index Scan e Result Showplan (SELECT). Ao posicionar o mouse sobre o operador SELECT e clicar com o botão direito, aparece o menu de contexto; neste, basta pressionar sobre o item de menu Propriedades (vide próxima figura) para que uma extensa relação de propriedades seja apresentada.

Obtemos então informações diversas, como:

  • Cardinality Estimation Model Version: informa os critérios de estimativa de cardinalidade;
  • Compile CPU, Compile Memory, Compile Time: tempos gastos no processo de compilação;
  • Opções ANSI: informa as configurações ANSI da conexão, durante o processo de compilação;
  • Nível de otimização: informa o grau de complexidade do código; valores usuais: TRIVIAL ou FULL;
  • Query Time Stats: estatísticas disponíveis a partir da versão 2016 do SQL Server;
  • Tamanho do plano em cache: espaço físico que o plano de execução ocupa no plan cache.

Além das propriedades acima há várias outras; a sugestão é que consulte a documentação do SQL Server sobre o significado de cada uma delas, de modo a extrair o máximo de informação sobre o plano de execução.

p011_codigo #2 - propriedades

2.3.2. Alertas

Às vezes são exibidos pequenos triângulos (com fundo amarelo ou vermelho) sobre o ícone original, com exclamação dentro. Estes triângulos são alertas indicando possível problema no plano de execução. Para saber qual é o motivo do alerta deve-se consultar as propriedades do operador.

2.3.3. Número de linhas: estimado x real

Um dos critérios utilizado pelo query optimizer para gerar o plano de execução são as estatísticas, incluindo a cardinalidade das colunas envolvidas. Assim, comparar as estatísticas de número estimado de linhas e número real de linhas pode fornecer informações sobre a acurácia das estatísticas e da qualidade do plano de execução. Uma estimativa errada no número de linhas pode resultar em uma consulta com memória alocada insuficiente. Quando isso acontece, o banco de dados temporário (tempDB) é utilizado como extensão de memória, tornando mais lenta a execução da consulta.

Se as contagens de linhas estimadas e reais diferirem significativamente é necessário descobrir a causa, corrigi-la e gerar um novo plano de execução, repetindo o processo até se obter o plano de execução perfeito…

No capítulo de material de estudos há o vídeo “Getting Better Query Plans by Improving SQL’s Estimates”, de Brent Ozar. Na sinopse do vídeo há a afirmação de “The secret is often comparing the query plan’s estimated number of rows to actual number of rows. If they’re different, it’s up to you – not the SQL Server engine – to figure out why the guesses are wrong”.

2.3.4. Operador Sort

É necessário considerar o impacto negativo da ordenação. O operador Sort é empregado principalmente para as seguintes funções: agregação (aggregate), junção de mesclagem (merge join) ou cláusula ORDER BY. Isso pode não ter impacto quando processando poucas linhas, mas à medida que o número de linhas aumenta, o tempo de processamento também aumenta.

p011_codigo #3 - plano de execucao

O plano de execução acima, referente ao código #3 (vide apêndice), mostra que o operador Sort representa 78% do custo estimado de execução do código. Embora eu não considere essa informação (custo do operador) confiável, pode indicar algo a verificar.

2.3.5. Operadores Spool

Outro operador que se deve prestar atenção é o de spool, que utiliza tabela temporária de trabalho para armazenar dados que necessitam ser utilizados várias vezes no plano de execução. Existem dois tipos lógicos de operador spool, Lazy spool e Eager spool. O Lazy Spool é um operador do tipo streaming; já o Eager Spool é do tipo blocking. Quanto aos operadores físicos de spool, há alguns tipos como Index Spool, Rowcount Spool, Table Spool e Window Spool.

p011_spool-32xEager spool
Lazy spool
p011_index-spool-32xIndex spool p011_remote-count-spool-32xRow count spool p011_table-spool-32xTable spool
Window spool

Ao analisar as propriedades do operador de spool deve-se observar com atenção as reassociações.

Na documentação do SQL Server, página “Referência de operadores físicos e lógicos de plano de execução”, há informações sobre a função de cada um dos operadores de spool; vide capítulo “Material de estudos”, ao final.

3. Conclusão

Como citado na introdução, não era objetivo inicial deste artigo tratar de operadores do plano de execução ou de como analisar o plano de execução. Entretanto, considerando-se a importância do assunto para a otimização de consultas em T-SQL, o assunto foi abordado no capítulo 2, embora de forma superficial. O suficiente, espero, para despertar a curiosidade sobre o tema.

E então, você descobriu o que Clive Owen, Denzel Washington e Jodie Foster têm a ver com SQL Server?

4. Material de estudos

Na web há vasto material sobre planos de execução, mas é necessário garimpar. Do material que encontrei, sugiro a leitura do livro de Grant Fritchey para aqueles que desejam iniciar no estudo de planos de execução; é didático e introduz o leitor aos poucos no assunto, começando do básico e aprofundando o assunto ao longo do texto. Já o livro do Fabiano é útil como referência técnica dos operadores.

4.1. Documentação Microsoft

4.2. Livros (e-book gratuito)

4.3. Vídeos

5. Apêndice

5.1. Códigos T-SQL

Criação da tabela

-- código #1
CREATE TABLE tbFunc 
     Nome varchar(40) collate Latin1_General_CI_AI
          not null primary key,
     DataNasc date not null,
     HoraNasc time(0) not null,
     DataAdmissão date not null,
     SitFunc char(1) not null
);

CREATE nonclustered INDEX I1_tbFunc
       on tbFunc (DataNasc) include (HoraNasc);
CREATE nonclustered INDEX I2_tbFunc on tbFunc (SitFunc);

set dateformat dmy;
INSERT into tbFunc (Nome, DataNasc, HoraNasc, DataAdmissão, SitFunc)
values
  ('Maria das Dores', '14/12/1980', '8:00', '1/1/2018', 'A'),
  ('João Maria da Silva', '29/2/1984', '3:00', '1/6/2017', 'B'),
  ('Maria Angelina Silva', '12/4/1982', '23:15', '12/10/2015', 'D'),
  ('Ernesto Varela', '30/1/1980', '19:12', '12/1/2018', 'C'),
  ('Mariangela Nascimento', '1/10/1977', '0:55', '9/11/2015', 'A');

Consulta

-- código #2
SELECT Nome, DataNasc
  from tbFunc
  where year(DataNasc) = 1980;

Consulta com resultado ordenado

-- código #3
SELECT Nome, DataNasc
  from tbFunc
  where year(DataNasc) = 1980
  order by DataNasc, HoraNasc;

PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para acessar.

O Plano Perfeito

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 )

Conectando a %s

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

Crie seu site com o WordPress.com
Comece agora
%d blogueiros gostam disto: