Uma query no SQL Server

Este artigo descreve o que acontece quando uma query é submetida dentro do SQL Server, criando uma base conceitual a respeito do funcionamento do plano de execução e dos fatores que o influenciam.

A aplicação dos conceitos e o entendimento do plano de execução são recomendados para profissionais em empresas de todos os segmentos. A construção de queries visando performance e tendo a compreensão da melhor maneira de construí-las influencia na qualidade de qualquer projeto que envolva extração de informação do SQL Server.

Hoje, com a grande necessidade de extrair informações do banco de dados, podendo ser tanto para carga e manipulação desses dados como para disponibilização de informações analíticas, entre outras finalidades, surgem alguns problemas para disponibilizar os dados com rapidez a qual não foi prevista durante a construção da query.

Então surge a questão, se os dados não estão sendo recuperados com a velocidade desejada, o plano de execução da query foi analisado? Uma vez que este plano foi ao menos gerado, são compreensíveis quais foram os fatores de influência dentro da construção da query para o resultado gerado pelo plano de execução no SQL? O que acontece dentro do SQL quando uma query é submetida?

No desenvolvimento de um projeto de banco de dados, as informações precisam ser disponibilizadas de forma eficiente e eficaz, ou seja, com rapidez e qualidade. No entanto, nem sempre é possível alcançar isso.

Em um mundo em que os projetos são sempre para ontem, as necessidades estão cada vez mais urgentes e muitas vezes o preço de um processo dentro do banco de dados mais lento do que o suportável é o preço a ser pago.

Mas o tempo escasso e a urgência não são os únicos inimigos a serem vencidos, ou administrados. A falta de conhecimento a respeito de como é gerado o plano de execução de um comando T-SQL e quais fatores influenciam na construção de queries coerentes para obter performance são itens a serem considerados.

Na maioria das vezes, as queries são construídas no processo de tentativa e erro e a sorte passa a ser um fator dominante, ao invés do conhecimento.

Alcançar performance na construção de queries no SQL está além de entender os operadores envolvidos em um plano de execução. Também envolve compreender o porquê esses operadores foram gerados, como foram gerados e quais foram os fatores que influenciaram o SQL a tomar as decisões que tomou para gerar o plano.

Por exemplo, quais as informações que foram levadas em consideração para que o índice criado não fosse usado, ou por que uma determinada query está levando mais tempo que o esperado para recuperar as informações, ou ainda por que determinado processo de carga de dados está levando mais tempo que a janela disponibilizada, entre outros problemas.

Considerando isso, o objetivo deste artigo é compreender o funcionamento do processamento de uma query dentro do SQL, e baseado nisso, ajustar o comando T-SQL para que alcance as necessidades do processo o qual faz parte. Afinal, como obter o melhor de algo que não se entende como funciona?

Logo, é preciso no começo cavar um pouco mais fundo para que o alicerce do conhecimento esteja bem fundamentado.

O que é um plano de execução?

Segundo a o livro The Art of High Performance SQL Code: SQL Server Execution Plans, plano de execução é definido como uma maneira simples do query optimizer calcular o caminho mais eficiente para implementar uma requisição representada pelo T-SQL, quando esta for submetida dentro SQL Server.

Portanto, uma vez que a query for submetida, o SQL vai calcular vários caminhos e escolher uma opção dentre esses.

Plano de Execução – Modo gráfico

Para entender visualmente a ideia do que é um plano de execução o primeiro exemplo a ser apresentado será no modo gráfico. O script disponibilizado na Listagem 1 é um passo a passo que será utilizado neste artigo para o estudo de como funciona o plano de execução. Através do SQL Management Query Editor, considerando que o SQL Server já esteja instalado, este acessível pelo SQL Management Studio, conecte no servidor a ser usado para este estudo, clique em New Query e execute o script abaixo através da tecla F5.

Listagem 1. Script para construção da tabela LOG_EXECUCAO.
  --Criação da tabela
  CREATE TABLE [dbo].[LOG_EXECUCAO](
        [ID]                    [int] IDENTITY(1,1) NOT NULL,
        [DATA_ATUALIZACAO]      [DATETIME]          NOT NULL,
        [ID_PROCESSO]           [INT]               NOT NULL,
        [NOME_SISTEMA]          [VARCHAR](100)      NOT NULL
   CONSTRAINT [PK_LOG_EXECUCAO_ID] PRIMARY KEY NONCLUSTERED 
  (
        [ID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
   
  GO
   
  --Inserção de dados
  INSERT INTO dbo.LOG_EXECUCAO
              (DATA_ATUALIZACAO
              ,ID_PROCESSO
              ,NOME_SISTEMA)
  VALUES  (GETDATE()
              ,1
              ,'SISTEMA DE VENDAS')
              
  GO
   
  INSERT INTO dbo.LOG_EXECUCAO
              (DATA_ATUALIZACAO
              ,ID_PROCESSO
              ,NOME_SISTEMA)
  VALUES  (GETDATE()
              ,2
              ,'SISTEMA DE CONTROLE')
              
  GO
  INSERT INTO dbo.LOG_EXECUCAO
              (DATA_ATUALIZACAO
              ,ID_PROCESSO
              ,NOME_SISTEMA)
  VALUES  (GETDATE()
              ,3
              ,'SISTEMA DE ESTOQUE')
  GO    
  INSERT INTO dbo.LOG_EXECUCAO
              (DATA_ATUALIZACAO
              ,ID_PROCESSO
              ,NOME_SISTEMA)
  VALUES  (GETDATE()
              ,4
              ,'SISTEMA DE MONITORAÇÃO')
  GO
  INSERT INTO dbo.LOG_EXECUCAO
              (DATA_ATUALIZACAO
              ,ID_PROCESSO
              ,NOME_SISTEMA)
  VALUES  (GETDATE()
              ,5
              ,'SISTEMA DE LOGÍSTICA')
   
  --Select dos dados
  SELECT * FROM DBO.LOG_EXECUCAO

Uma vez que a query que será analisada esteja montada, ou seja, como mostrado na Listagem 1, a query a ser analisada é o select da tabela LOG_EXECUCAO. É possível obter o plano de execução de diversas maneiras, entre elas o modo gráfico.

Neste caso para gerar o plano no modo gráfico, clique na barra de ferramentas do Management Studio Query Editor, pode-se escolher entre duas opções Display estimated execution plan e Include actual execution plan. Se a opção escolhida for Include Actual Execution Plan, é preciso executar o script antes que o plano de execução seja gerado, pois dessa forma o plano trará também o resultado atual além da estimativa, esta opção pode ser identificada na Figura 1, circulada em vermelho.

Se a opção desejada for Display Estimated Execution o script primeiro passa pelo processo de "parse", e depois a estimativa do plano de execução é gerada. A opção Display Estimated Execution pode ser identificada circulada em preto na Figura 1.

 Modo gráfico

Figura 1. Modo gráfico.

Após a seleção de uma dessas opções (Display Estimated Execution ou Include Actual Execution Plan) o plano gerado será simular ao mostrado na Figura 2. Esta é a visualização do plano no modo gráfico.

Plano de execução gerado

Figura 2. Plano de execução gerado.

Para entender o caminho que foi percorrido na engine do SQL Server para gerar o plano de execução, usando como, por exemplo, na Figura 2 o operador Table Scan e ainda o que é um Table Scan, é preciso entender o que ocorre dentro do banco de dados quando a execução de uma query é submetida no SQL Server.

Processos do plano de execução

Quando uma determinada query é executada dentro SQL Server como, por exemplo, um select em uma tabela para obter o retorno de um conjunto de dados, ocorrem vários processos no servidor. O propósito deles é administrar a maneira como os dados serão retornados. Embora existam vários processos diferentes ocorrendo ao mesmo tempo, a query submetida vai passar basicamente por dois estágios:

1. Processos que ocorrem na Relational Engine;

2. Processos que ocorrem na Storage Engine.

De forma resumida na Relational Engine é onde a query passa pelo processo de "parse" e é processada pelo query optimizer, o que irá gerar o plano de execução. O plano é mandado (em formato binário) para a Storage Engine para execução da query e retorno dos dados solicitados.

Query Parsing

O “parse” da query ocorre, como informado anteriormente, no Relational Engine. Isso significa que quando a query chega, esta deverá passar pelo processo de análise da escrita do T-SQL para que seja verificado se está correta.

A query também será submetida ao algebrizer, o qual vai identificar entre as colunas todos os tipos de dados (varchar, char, nvarchar, etc.) de todos os objetos que estão sendo acessados.Se existirem agregações serão localizadas (como group by) em um processo chamado de aggregation binding. A saída deste processo é a query gerada em forma de árvore no qual são identificados os passos lógicos para a execução da mesma (isso se o plano da query não existir em cache).

Depois disso a query passa ao Query Optimizer, aonde vai ser modelada, usando o resultado do query parsing e as estatísticas dos dados.

Query Optimizer

O query optimizer é aonde os planos baseado em custo são gerados, o que significa que entre várias possibilidades uma é escolhida baseada no menor custo. Os cálculos vão levar em consideração algumas informações como, por exemplo, recursos de CPU, I\O e estatística. As estatísticas por default são coletadas automaticamente, baseadas nas colunas dos índices dentro do banco de dados elas descrevem a distribuição dos dados, a unicidade, ou seletividade destes.

O caminho ótimo para execução da query será definido pelo optimizer tendo como resultado o plano de execução. Neste processo serão definidos quais os tipos de joins serão usados, quais índices serão acessados e ainda outras informações que são baseadas em custo.

Se o plano não existir em cache vários planos serão gerados, o plano com o menor custo que leve menos tempo para ser gerado será escolhido (veja BOX 1), no entanto algumas vezes o plano menos eficiente vai ser escolhido.

Isso se o optimizer achar que vai ser mais rápido escolher o plano menos eficiente, do que gerar mais planos. Por exemplo, no caso de uma query que faça uma consulta no SQL e não faz o uso de índices, cálculos ou agregações. Ao invés de tentar o plano de execução ótimo, o optimizer escolhe o plano de execução trivial.

BOX 1. Custo

O custo da query não é traduzido em segundos, é um número utilizado para representar o valor de custo para cada recurso no plano. Para obter o custo dos operadores multiplica-se o valor base do operador pelo tamanho da linha e a estimativa de número de linhas. A soma disso é o total de custo dos operadores.

Uma vez que o plano de execução foi gerado, também chamado de plano atual pelo optimizer, ele é e armazenado numa área da memória chamada "plan cache", se um plano idêntico já existir no cache o plano vai ser reutilizado.

Plan Cache

Gerar os planos de execução no SQL pode consumir tempo e recurso intensivamente do banco de dados, logo faz sentido reusar um plano de execução sempre que é possível. Ao reutilizar o plano de execução que foi gerado, por exemplo, torna-se desnecessário gerar um plano de execução para uma query muito complexa que já tem seu plano armazenado em cache ou ainda uma query pequena que é chamada muitas vezes. Os planos de execução são armazenados numa área da memória chamada plan cache.

Os planos de execução armazenados em cache são acessíveis através da DMV sys.dm_exec_cached_plans. Pode-se saber, por exemplo, quantas vezes o plano foi reutilizado. Na Listagem 2, o filtro para planos que foram reutilizados mais de uma vez foi aplicado através da coluna “usecounts > 1”:

Listagem 2. Planos reutilizados.
  SELECT usecounts, cacheobjtype, objtype, text 
  FROM sys.dm_exec_cached_plans 
  CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
  WHERE usecounts > 1 
  ORDER BY usecounts DESC;

Algumas informações interessantes podem ser obtidas com uso das DMVS que o SQL disponibiliza. DMVs são Dynamic Management Views, ou seja, views de sistema que podem ser utilizadas para várias finalidades como análise de performance. Considerando os planos armazenados em cache obtidos através da DMV sys.dm_exec_cached_plans e a DMV sys.dm_exec_query_plan, a qual retorna os planos de execução em formato XML, é possível gerar o plano de execução das queries que estão em cache, como mostrados na Listagem 3.

Listagem 3. Planos de execução em cache.
  SELECT *
  FROM sys.dm_exec_cached_plans c
  CROSS APPLY sys.dm_exec_query_plan(c.plan_handle) p

Entre as informações que são resultadas da execução da Listagem 3 visualizadas na Figura 3, observa-se o retorno da coluna query_plan. Nela existe um link XML que acessa as informações gráficas do plano de execução armazenado no cache. Na maioria das vezes existirá no cache um plano armazenado por query, a não ser que o SQL descubra que uma execução paralela pode resultar numa performance melhor. Neste caso, o paralelismo passa a ser uma opção, e um segundo plano é criado para aquela query.

Plano de execução e plan cache

abrir imagem em nova janela

Figura 3. Plano de execução e plan cache.

Os planos de execução não são mantidos na memória para sempre. Eles envelhecem também, e a fórmula que o SQL utiliza para determinar a idade de um plano de execução, consiste em multiplicar o custo do plano pela quantidade de vezes que ele foi executado. Este valor será decrescido pelo lazy writer (veja BOX 2).

BOX 2. Lazy writer

Lazy writer é o processo responsável por limpar o cache, inclusive a área chamada de plan cache. Periodicamente ele verifica os objetos que estão no cache e diminui o valor que foi calculado para eles.

Portanto, quando a idade do plano chegar a zero ele vai deixar de constar no cache. Existem outras situações em que isso pode ocorrer como, por exemplo, quando mais memória for requerida pelo sistema e toda memória corrente estiver em uso.

Existem alguns casos em que o plano de execução será recompilado, esta operação pode ser bem cara para o SQL. Suponha que um plano de uma query esteja compilado e armazenado no plan cache, quando esta query for executada e o plano reutilizado, a validade e a otimização deste plano serão validados. Se por alguma razão esta operação de validação do SQL no plano falhar, ele será compilado novamente e um novo plano será gerado. Seguem alguns exemplos que podem levar a recompilação do plano de execução:

· A estrutura da schema da tabela referenciada pela query foi modificado;

· Mudar o índice usado pela query;

· Dropar o índice usado pela query;

· Atualizar as estatísticas usadas pela query;

· Utilizar a procedure sp_recompile.

Talvez em algum momento de análise de performance da query possa ser interessante forçar a saída do armazenamento desta query no cache. O cache do SQL pode ser limpo completamente ou individualmente.

Para limpá-lo individualmente, por exemplo, pode ser feito através do plan_handle com o uso da DBCC FreeProcCache, a Listagem 4 mostra como obter o plan_handle, em seguida limpar individualmente o plano do cache.

Listagem 4. Planos de handle.
  SELECT plan_handle, st.text
  FROM sys.dm_exec_cached_plans 
  CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
  WHERE text LIKE N'SELECT * FROM dbo.LOG_EXECUCAO%';
  GO
   
  DBCC FREEPROCCACHE (0x0600060022DC2D1E40818F85000000000000000000000000);

Como informado anteriormente, é possível limpar todos os planos armazenados em cache, neste caso não é preciso passar o plan handle. A Listagem 5 mostra como realizar isso através do uso do comando DBCC FreeProcCache.

Listagem 5. Limpar todos os planos do cache.
  DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Query Execution

A próxima fase pela qual a query que foi submetida irá passar é o query execution, como nome já diz é o momento em que ocorre a execução da query.

Depois que o plano é gerado na Storage Engine a query vai ser executada de acordo com o plano e os dados requeridos serão acessados. As páginas com os dados requeridos ou são obtidas por existirem no cache ou são acessadas no disco e depois disso colocadas no cache de dados.

O tempo que se leva para ler os dados do disco e colocá-los na memória é o tempo representado pelo waittype PageIOLatch.

Algumas vezes podem ocorrer diferenças entre as estatísticas coletadas e os dados que existem nas tabelas. Isso geralmente ocorre quando:

· Os dados são inseridos e deletados;

· A chave que define o índice sofre mudanças;

· A distribuição da informação muda.

Isso gera um plano de execução menos eficiente e má performance na execução da query.

Modos de se obter o plano de execução

O plano de execução no SQL pode ser obtido de várias maneiras e em vários formatos diferentes. A forma que irá ser escolhida para análise vai variar de pessoa para a pessoa.

Tende-se a preferir o modo gráfico, pois este é mais fácil de compreender. O modo texto é uma feature deprecated, ou seja, em algum momento a Microsoft vai desabilitar este modo de visualização.

O modo XML é o que deverá substituir o modo de visualização texto e é mais fácil de compreender.

Planos no modo texto

Uma vez que a geração de plano no modo texto é uma feature deprecated. Qual o sentido de saber como gerar o plano neste modo? A resposta é simples, nem sempre ao se desenvolver um projeto em SQL, os servidores estão na última versão do produto.

Em alguns casos, nem mesmo estão usando o SQL 2005, neste caso por uma questão de compatibilidade é importante saber gerar plano de execução em diferentes versões e situações. A seguir estão descritas as opções que permitem gerar o plano de execução no modo texto:

· SHOWPLAN_ALL: Esta opção retorna a estimativa do plano de execução sem executar a query. Uma vez que esta opção esteja habilitada todos os T-SQLs que forem executados retornarão as estimativas do plano de execução até que esta opção seja desabilitada. Provê uma quantidade razoável de dados para a análise;

· SHOWPLAN_TEXT (Deprecated, será desabilitado em futuras versões): Também retorna a estimativa do plano de execução sem executar a query. Uma vez que esta opção esteja habilitada todos os T-SQLs que forem executados retornarão as estimativas do plano de execução até que esta opção seja desabilitada. Provê uma quantidade limitada de dados, recomendado em ferramentas como osql.exe;

· STATISTICS PROFILE: Similar ao SHOWPLAN_ALL exceto que representa os dados do plano de execução atual. Logo a query é executada, trazendo além do resultado da query o resultado do plano de execução.

Planos no modo XML

Os planos de execução no formato XML passaram a existir a partir do SQL 2005, deverão substituir os planos de execução no modo texto. Eles podem ser visualizados em formato gráfico ou formato XML e trazem até mais informações que no modo gráfico.

· SHOWPLAN_XML: Esta opção retorna a estimativa do plano de execução sem executar a query. Uma vez que esta opção esteja habilitada todos os T-SQLs que forem executados retornarão as estimativas do plano de execução até que esta opção seja desabilitada. O retorno de dados desta opção é o mais completo;

· STATISTICS XML - Similar ao SHOWPLAN_XML exceto que representa os dados do plano de execução atual. Logo a query é executada, trazendo além do resultado da query o resultado do plano de execução.

Plano de execução no Modo Gráfico

Para gerar o plano de execução em qualquer modo, seja XML, texto ou gráfico é necessário ter permissão no banco de dados para isso. Se a pessoa que for gerar o plano de execução não for sysadmin, dbcreator ou dbowner, a permissão a necessária é de SHOWPLAN:

GRANT SHOWPLAN TO [USER]

Com as devidas permissões dadas, o próximo passo é obter o plano de execução. Continuando pelo modo gráfico, escolha Display Estimated Execution Plan. Além do caminho apresentado no tópico “Exemplificando: Plano de Execução - Modo gráfico” no início deste artigo, também existem outros caminhos possíveis para obter este modo de visualização do plano de execução, sendo eles:

· Clique com botão direito próximo ou no local no qual a query foi digitada e selecione a opção do menu, conforme mostra a Figura 4;

· Selecione a opção do menu query;

· Digite CTRL+L.

Display Estimated Execution Plan com
o botão direito do mouse.

Figura 4. Display Estimated Execution Plan com o botão direito do mouse.

Uma vez que é gerado o plano apresentado, este pode ser visualizado na aba execution plan (ver Figura 5).

 Display estimated execution, ao lado
de messages.

Figura 5. Display estimated execution, ao lado de messages.

Com o uso dos mesmos meios demonstrados já descritos, o plano de execução com as informações atuais, pode ser obtido selecionando-se Include Actual Execution Plan. No entanto a tecla de atalho para acessá-lo é CTRL+M. Lembrando que o plano será gerado com a execução da query, por isso ele inclui as informações atuais além das estimativas, diferente do Display estimated execution plan.

Importante lembrar que no caso de se tentar ler o plano de execução de uma procedure que esteja encriptada, esta por uma questão de segurança não mostra o plano de execução.

Como ler o plano gerado no modo gráfico

No modo gráfico, a leitura do plano deve ser feita da direita para a esquerda de cima para baixo. São aproximadamente 78 operações que representam várias ações e decisões que podem ser tomadas pelo plano de execução, a operação mostrada na Figura 6, é uma das possíveis.

Plano de execução e interpretação,
modo gráfico.

Figura 6. Plano de execução e interpretação, modo gráfico.

A seta que liga os ícones representa os dados que estão sendo passados entre eles. Quanto mais grossa a seta, maior o número de informações que está sendo passada. Esta inclusive é uma pista que pode ser analisada em caso de problemas de performance.

Abaixo de cada item tem uma porcentagem que representa o custo relativo da query, para cada operador representado pelos ícones. Este custo vai de 0% até 100%, uma query pode ter múltiplos passos, cada passo vai ter um valor de custo relativo dentro do contexto da query.

Ao passar com o mouse pelos ícones percebe-se uma janela aparecer como pode ser visualizada na Figura 7, esta janela é a “tooltip”. Cada operador tem uma “tooltip” associada a ele, com detalhes referente à execução da package. A natureza de execução de cada operador define um grupo de dados que estará disponível nesta “tooltip”.

 Plano de execução e tooltip

Figura 7. Plano de execução e tooltip.

As seguintes informações podem aparecer nas tooltips:

· Cached Plan Size (Operador Select): Tamanho do plano armazenado no Cache;

· Logical Operation: Representa o resultado dos cálculos do Optimizer para o que deve acontecer quando query executar;

· Physical Operation: Representa o que realmente aconteceu quando a query foi executada. Geralmente Physical e Logical são iguais;

· Actual Number Rows (disponível se a opção Include Actual Execution Plan for selecionada): Número atual das linhas retornadas baseada em quantas vezes a função GetNext(), o qual retorna uma linha por dado, foi chamada;

· Estimated I\O Cost: Baseado em cálculos feitos pelo Optimizer, é apenas uma estimativa, mas pode ser um ponto de atenção para queries com alto custo de I\O;

· Estimated CPU Cost: Baseado em cálculos feitos pelo Optimizer, é apenas uma estimativa, mas pode ser um ponto de atenção para queries com alto custo de CPU;

· Estimated Operator Cost: Estimativa de custo para a execução do operador. Ex: Estimativa de custo para um table Scan, ou seja, para a leitura de toda a tabela linha por linha sem uso de índice;

· Estimated SubtreeCost: Custo acumulado da execução dos passos anteriores até o passo atual (leitura deve ser feita da direita para a esquerda);

· Estimated Number of Rows: Cálculo baseado na estatística disponível para Optimizer produzindo a estimativa de número de linhas. Esta varia de ícone a ícone os quais representam os operadores envolvidos no plano de execução. Cada operador esta lidando com um grupo e quantidade diferente de dados;

· Estimated Row Size: Estimativa do tamanho da linha retornada, baseado em estatística;

· Actual Rewbinds (disponível se a opção Include Actual Execution Plan for selecionada): Significa que um ou mais parâmetros correlacionados ao join mudaram e o inner precisa ser reavaliado. Refere-se ao número de vezes que o método INIT() foi chamado;

· Actual Rewinds (disponível se a opção Include Actual Execution Plan for selecionada): Significa que um ou mais parâmetros correlacionados ao join mudaram antes do resultado do inner e este precisa ser usado novamente. Refere-se ao número de vezes que o método INIT() foi chamado;

· Ordered: Valor do tipo boolean, o qual informa se os dados estão ordenados ou não. Em alguns casos as informações retornadas por uma query vão determinar que os dados ou um grupo deles estivessem organizados de maneira ordenada, isto pode gerar um processamento extra no plano;

· Node ID: Simplesmente indica a ordem do nó, ou seja, a ordem dos operadores. Perceba que eles estão ordenados da esquerda para a direita, embora a leitura do plano seja melhor da direita para a esquerda.

Ao clicar com o botão direito no plano de execução no modo gráfico e selecionar propriedades, pode-se obter mais detalhes sobre as operações, conforme pode ser observado na Figura 8. A maioria do que está disponível nas propriedades já é conhecido, exceto por algumas informações, como por exemplo:

· Forced Index: Valor boolean, que vai ficar true se a query estiver usando algum hint para forçar o uso de índice;

· No Expand Hint: Mesmo conceito do Forced Index aplicado a indexed views;

· Parallel: valor lógico do tipo boolean, podendo assumir os valores 0 e 1. 0 indica que não está rodando em paralelo, e 1 que está rodando em paralelo.

Plano de execução e as propriedades

Figura 8. Plano de execução e as propriedades.

Como gerar o plano no modo texto

O modo de visualização de planos de execução no modo texto tem a vantagem de passar a informação de uma vez só, sem a necessidade de utilizar recursos como tooltip ou as propriedades obtidas através do modo gráfico.

Para ativar este modo de exibição, é preciso fazê-lo através de comando no SQL Server Management Studio, antes de executar a query, como por exemplo:

SET SHOWPLAN_ALL ON;

Logo, todos os comandos T-SQL que forem executados após a ativação, como feita no exemplo acima, estarão sujeitos à exibição do plano de execução. Lembrando que nos casos de SHOWPLAN_ALL, SHOWPLAN_TEXT, SHOWPLAN_XML, o plano de execução é estimado sem a execução da query. Para desabilitar esta opção digite o comando:

SET SHOWPLAN_ALL OFF;

Considerando que este comando não executa a query, apenas gera o plano. Se a query estiver na dependência da criação de uma tabela, e esta opção for habilitada antes da criação da tabela, o comando irá retornar erro. O comando não irá executar a query, apenas estimá-la.

E uma vez que o comando não irá executar a criação da tabela esta não vai existir para a geração do plano de execução. Esta situação pode ser observada na Figura 9.

Plano modo texto

Figura 9. Plano modo texto – Criação de tabela sem execução.

O modo de ativação do plano de execução no modo gráfico é diferente da ativação no modo texto ou XML, neste caso utiliza-se a sintaxe: SET [modo do plano de execução] ON e para desativação SET [modo do plano de execução] OFF.

Seguem alguns exemplos na Listagem 6 de ativação e desativação do plano de execução.

Listagem 6. Ativação do plano de execução texto ou XML.
  SET SHOWPLAN_TEXT ON;
  SET SHOWPLAN_TEXT OFF;
   
  SET STATISTICS PROFILE ON; 
  SET STATISTICS PROFILE OFF;
   
  SET SHOWPLAN_XML ON; 
  SET SHOWPLAN_XML OFF; 
   
  SET STATISTICS XML ON;
  SET STATISTICS XML OFF;

Como ler o plano gerado no Modo Texto

O plano de execução no modo texto é mais complexo de ser lido do que no modo gráfico, tanto que no geral o plano de execução no modo gráfico é mais usado. Para tornar isso mais claro, segue na Figura 10 o mesmo plano de execução que foi gerado no modo gráfico, exemplificado dessa vez no modo texto com uso do comando SHOWPLAN ALL.

Plano de execução

Figura 10. Plano de execução – ShowPlan_ALL.

No retorno obtido ao executar o comando SHOWPLAN_ALL, a primeira coluna é StmtText, e a primeira linha desta coluna é o SELECT que foi submetido ao plano de execução, as linhas após isso são as operações. No modo gráfico as operações eram mostradas através de ícones.

Quanto mais complexo o plano de execução, mais complexa é a leitura no modo textual. A leitura neste caso, não pode ser da direita para a esquerda como no modo gráfico, no modo texto fará mais sentido ler a partir do meio e depois disso para fora seguindo o uso da endentação e do pipe (|).

As mesmas informações disponíveis no modo gráfico estão disponíveis no modo texto ou XML, e ainda um pouco mais de informação.

Lembrando que o modo mais recomendado para obter os planos de execução no modo texto são os planos gerados como XMLs.

Como gerar o plano no Modo XML

No modo XML, disponível a partir do SQL 2005, é possível visualizar mais informações que no modo gráfico como, por exemplo, frações de memória (como a memória que foi disponibilizada é distribuída entre os operadores no plano) e é notavelmente mais simples que o modo texto, no qual tem – se que se localizar através de endentações e pipes (|).

O plano de execução no modo XML traz os resultados do plano através de um link XML, conforme pode ser verificado na Figura 11, e pode ser visualizado tanto no modo gráfico como em XML.

Plano de execução – ShowPlan_XML

Figura 11. Plano de execução – ShowPlan_XML.

Ao clicar no link disponibilizado pelo plano de execução, percebe-se que um conjunto mais completo de informações, mesmo para uma query simples, é disponibilizado.

Algo interessante de se observar é o tipo de plano que foi gerado, esta informação é obtida através do valor em StatementOptmLevel = “TRIVIAL”. Logo, para a execução desta query foi escolhido um plano de execução trivial, ou seja, ao invés de escolher o plano de execução ótimo, pela simplicidade da query foi escolhido o modo trivial.

Como ler o plano gerado no Modo XML

O modo XML provê mais informação para análise e é melhor para ler que o modo texto, parte do plano pode ser observado pela Listagem 7 o qual o resultado foi gerado através do script da Listagem 8. A maioria das informações disponibilizadas já são conhecidas como, por exemplo, EstimateRows ou EstimateIO e podem ser visualizadas tanto no modo XML , texto ou gráfico.

Listagem 7. Parte do plano em XML.
  <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.1600.1">
    <BatchSequence>
      <Batch>
        <Statements>
          <StmtSimple StatementText="SELECT * 
           FROM dbo.DatabaseLog
" StatementId="1"
           StatementCompId="1" StatementType="SELECT" 
           StatementSubTreeCost="0.576891" StatementEstRows="1597" 
           StatementOptmLevel="TRIVIAL" QueryHash="0xCEA1767217FB35FD" 
           QueryPlanHash="0x2959B5DE1A3E7C7D">
            <StatementSetOptions QUOTED_IDENTIFIER="true" 
              ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" 
              ANSI_NULLS="true" ANSI_PADDING="true" 
              ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
            <QueryPlan CachedPlanSize="16" CompileTime="121" 
              CompileCPU="109" CompileMemory="88">
              <RelOp NodeId="0" PhysicalOp="Table Scan" 
               LogicalOp="Table Scan" EstimateRows="1597" 
               EstimateIO="0.574977" EstimateCPU="0.0019137" 
               AvgRowSize="8593" EstimatedTotalSubtreeCost="0.576891" 
               TableCardinality="1597" Parallel="0" 
               EstimateRebinds="0" EstimateRewinds="0">
                <OutputList>
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                     Schema="[dbo]" Table="[DatabaseLog]" 
                     Column="DatabaseLogID" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                    Schema="[dbo]" Table="[DatabaseLog]" 
                    Column="PostTime" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                   Schema="[dbo]" Table="[DatabaseLog]" 
                   Column="DatabaseUser" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                    Schema="[dbo]" Table="[DatabaseLog]" 
                    Column="Event" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                   Schema="[dbo]" Table="[DatabaseLog]" 
                   Column="Schema" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                   Schema="[dbo]" Table="[DatabaseLog]" 
                    Column="Object" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                    Schema="[dbo]" Table="[DatabaseLog]" 
                    Column="TSQL" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                    Schema="[dbo]" Table="[DatabaseLog]" 
                    Column="XmlEvent" />
                </OutputList>
                <TableScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
Listagem 8. Habilitando plano em XML.
  SET SHOWPLAN_XML ON
  GO
  SELECT * FROM dbo.DatabaseLog
  GO
  SET SHOWPLAN_XML OFF

É possível salvar o plano de execução, e para isso nem é preciso abri-lo. Basta clicar com o botão direito no link disponibilizado e escolher Save as. Da mesma forma o modo gráfico pode ser salvo, apenas clicando com o botão direito do mouse e escolhendo a opção Save as.

Como gerar e ler o plano usando Statistic

Entre as opções possíveis para gerar o plano de execução, existe a opção Statistics XML, a qual vai executar a query antes de trazer o plano de execução, com isso as informações atuais como número de linhas atuais serão disponibilizadas Diferente do comando SHOWPLAN_XML que apenas estima e não executa a query, portanto os dados atuais não aparecem apenas os dados estimados. A Figura 12 traz a execução do Statistics XML feita através dos comandos na Listagem 9.

Listagem 9. Habilitando Statisctics XML.
  SET STATISTICS XML ON
  GO
  SELECT * FROM dbo.DatabaseLog
  GO
  SET STATISTICS XML OFF

Plano de execução – Statistics XML

Figura 12. Plano de execução – Statistics XML.

Parte das informações contidas no link XML gerado pode ser vista na Listagem 10. Nela estão as informações atuais do plano de execução.

Listagem 10. Parte do plano Statisctics XML.
  <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.1600.1">
    <BatchSequence>
      <Batch>
        <Statements>
          <StmtSimple StatementText="SELECT * 
           FROM dbo.DatabaseLog
" StatementId="1" 
           StatementCompId="1" StatementType="SELECT" 
           StatementSubTreeCost="0.576891" StatementEstRows="1597" 
           StatementOptmLevel="TRIVIAL" QueryHash="0xCEA1767217FB35FD" 
           QueryPlanHash="0x2959B5DE1A3E7C7D">
            <StatementSetOptions QUOTED_IDENTIFIER="true" 
             ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" 
             ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" 
             NUMERIC_ROUNDABORT="false" />
            <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" 
             CompileTime="121" CompileCPU="109" CompileMemory="88">
              <RelOp NodeId="0" PhysicalOp="Table Scan" 
               LogicalOp="Table Scan" EstimateRows="1597" 
               EstimateIO="0.574977" EstimateCPU="0.0019137" 
               AvgRowSize="8593" EstimatedTotalSubtreeCost="0.576891" 
               TableCardinality="1597" Parallel="0" 
               EstimateRebinds="0" EstimateRewinds="0">
                <OutputList>
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                   Schema="[dbo]" Table="[DatabaseLog]" 
                   Column="DatabaseLogID" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                   Schema="[dbo]" Table="[DatabaseLog]" 
                   Column="PostTime" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                   Schema="[dbo]" Table="[DatabaseLog]" 
                   Column="DatabaseUser" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                   Schema="[dbo]" Table="[DatabaseLog]" 
                   Column="Event" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                   Schema="[dbo]" Table="[DatabaseLog]" 
                    Column="Schema" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                    Schema="[dbo]" Table="[DatabaseLog]"
                    Column="Object" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                   Schema="[dbo]" Table="[DatabaseLog]" Column="TSQL" />
                  <ColumnReference Database="[AdventureWorks2008R2]" 
                   Schema="[dbo]" Table="[DatabaseLog]" 
                   Column="XmlEvent" />
                </OutputList>
                <RunTimeInformation>
                  <RunTimeCountersPerThread Thread="0" ActualRows="1597" 
                   ActualEndOfScans="1" ActualExecutions="1" />
                </RunTimeInformation>
                <TableScan Ordered="0" ForcedIndex="0" NoExpandHint="0">

Entre as opções de Statistics usadas para gerar e avaliar o plano de execução existe a Statistics IO. As estatísticas de I/O medem a quantidade de atividade em disco gerada pela query. O resultado do Statistics IO pode ser visto na Figura 13 habilitada através do script da Listagem 11.

Listagem 11. Habilitando Statistics IO.
  SET STATISTICS IO ON
  GO
  SELECT * FROM dbo.DatabaseLog
  GO
  SET STATISTICS IO OFF

Plano de execução – Statistics IO

Figura 13. Plano de execução – Statistics IO.

As informações produzidas por esta opção estão descritas a seguir:

· Número de scans/seek em qualquer direção para retornar as informações, depois de o nível folha ter sido alcançado. Por exemplo, uma consulta filtrada pelo índice da tabela a fim de retornar apenas um valor, retorna scan count igual a 0. Isso por que o scan utiliza o índice e o filtro foi feito por apenas por um valor. Portanto, vai haver apenas um critério de encontro de valores e não foi necessário realizar o scan na tabela no nível folha;

· Número de páginas lidas do cache de dados, que seriam as leituras lógicas ou logical reads;

· Número de páginas lidas do disco, que seriam as leituras físicas ou physical reads;

· Número de páginas colocadas no cache para consulta, que seriam as leituras read-ahead;

· Número de text, ntext, image ou páginas com valores altos como varchar(max), nvarchar(max), varbinary(max) lidas do cache de dados, que seriam as leituras lógicas lob ou lob logical reads;

· Número de text, ntext, image ou páginas com valores altos colocadas no cache para consulta, que seriam as leituras físicas lob ou physical reads lob;

· Número de text, ntext, image ou páginas com valores altos colocadas no cache para consulta, que seriam as leituras read-ahead lob.

Existe tambéma opção Statistics Time, a qual informa o tempo em milissegundos para cada operação a ser completada no plano de execução. Habilitada através do exemplo na Listagem 12, os resultados podem ser vistos na Figura 14.

Listagem 12. Habilitando Statistics Time.
  SET STATISTICS TIME ON
  GO
  SELECT * FROM dbo.DatabaseLog
  GO
  SET STATISTICS TIME OFF

Plano de execução – Statistics Time

Figura 14.Plano de execução – Statistics Time.

O momento em que a opção Statistics Time for habilitada e a query for rodada pode trazer variação no tempo informado, dependendo do tamanho do dataset retornado ou concorrência no banco e etc.

Existem outros tipos de Sets Statistics, estes são apenas alguns dos que podem ser utilizados para a análise de performance da query.

Operadores envolvidos no plano de execução

Os exemplos de planos de execução que foram gerados no decorrer deste artigo apresentavam o operador table scan conforme pode ser visto na Figura 15. O que exatamente Table Scan significa?

Table Scan, conforme definido anteriormente, informa que o Optimizer escolheu fazer scan da tabela toda, linha a linha até encontrar o resultado que foi requerido. Esta decisão pode ter sido entre outros motivos por não ter índice na tabela, ou por que a quantidade de dados a ser trazida é tão grande que não vale a pena utilizar o índice. Portanto os índices, caso existam, não estão sendo utilizados.

Se a tabela for pequena, o table scan não chega a ser um problema. Entretanto se for uma tabela grande é interessante avaliar outras estratégias como: reescrever a query, verificar a possibilidade de criação de um índice de acordo com a consulta, ou filtrá-la e limitar os dados para que esta possa a utilizar índices que já existam na tabela.

Operador Table Scan.

Figura 15. Operador Table Scan.

Além do Table Scan, existe outro tipo de operador que utiliza Scan e é muito encontrado nos planos de execução. Este é o Index Scan, o que isso significa dentro do plano de execução?

Index Scan é o scan do índice que pode ser cluster ou não cluster, ou seja, o índice todo ou uma grande parte dele esta sendo escaneado linha por linha até alcançar o resultado desejado.

Os índices são armazenados em B-tree (vários nós que apontam para um parente). Um índice cluster não armazena apenas a estrutura chave como um índice normal, mas também filtra e classifica o armazenamento do dado.

Por isso só existe um índice cluster por tabela. Se um índice scan está sendo retornado no plano de execução, isso significa que o Optimizer entendeu que muitas linhas precisam ser retornadas e é mais fácil escanear o índice inteiro do que simplesmente utilizar as chaves providenciadas por ele.

Neste caso o que pode ser feito é analisar o uso do índice criado, estreitar, se possível, o filtro da cláusula WHERE, podem ser um bom caminho a seguir.

Outro operador comum de ser visualizado no plano de execução é o index seek, conforme pode ser visto na Figura 16. Este não é um operador de scan como os dois anteriores, é um operador de seek que funciona sobre o índice. O que isso significa para o plano de execução?

Index Seek, ao contrário do index scan, ocorre quando o Optimizer conseguiu localizar a informação requerida através do uso do índice que pode ser cluster ou não cluster. Logo através dos valores chaves do índice, rapidamente o valor da linha que precisa ser retornado foi encontrado. Percebe-se que quando há o uso de cluster index seek a propriedade Ordered do plano de execução esta setada para true, ou seja, os dados estão ordenados. Se o índice for não cluster, este pode ou não estar apontando para um índice cluster a fim de realizar o index seek.

Operador Index Seek

Figura 16. Operador Index Seek.

Conclusão

Compreender o que ocorre dentro do SQL e como os planos de execução são gerados permite diferenciar o que de fato é importante para o ganho de performance na construção de uma query do que não é.

Por exemplo, considerando que os planos de execução são baseados em custo e que cada operador tem sua base de custo para o cálculo do plano de execução, pode-se concluir que o custo dos operadores não é baseado na velocidade do hardware. Não se está considerando que o hardware não é importante para performance, ao contrário, é importante, todavia o custo dos operadores independe do hardware.

Logo, o custo do operador calculado será o mesmo em qualquer versão e instalação do SQL. Um fator de influência no cálculo do plano neste caso, entre outros fatores, seria a atualização das estatísticas, pois o cálculo de estimativa do custo leva em consideração esta informação.

Estas informações podem ser concluídas, porque há entendimento de como o SQL funciona e o que acontece quando uma consulta é submetida. Com isso é possível determinar quais as melhores decisões a serem tomadas, as que realmente irão influenciar na performance do código de acordo com o objetivo a ser atingido.

A partir dos próximos artigos, a complexidade das queries vai aumentar e também o número de operadores usados pelo plano de execução. Com isso serão abordados quais desses operadores devem sero foco de atuação para obter ganho de performance

Referências

GRANT, Fritchey. The Art of High Performance SQL Code: SQL Server Execution Plans. Simple-Talk. 2008. p 17-46.

BOLTON Christian; LANGFORD Justin; BERRY Glen; PAYNE Gavin; BANERJEE Amit; FARLEY Rob. SQL Server 2012 Internals and Troubleshooting. John Wiley & Sons, Inc., Indianópolis, Indiana. 2013. p 48-55.

Links

Getting IO and time statistics for SQL Server queries
http://www.mssqltips.com/sqlservertip/1255/
getting-io-and-time-statistics-for-sql-server-queries/

Como funciona a opção de estatística - SET STATISTICS IO
http://msdn.microsoft.com/pt-br/library/ms184361.aspx

SQL Server: Performance Tuning: Understanding Set Statistics IO output
http://www.practicalsqldba.com/2013/07/sql-server-performance-tuning.html

Plan Caching in SQL Server 2008
http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

Links Úteis

Saiba mais sobre SQL Server ;)