Tuning - Plano de Execução - Parte 2

Olá!

Aprenderemos agora a efetuar a leitura do Plano de Execução Gráfico de uma query, assunto que iniciei na coluna anterior.

A leitura de um plano de execução deve ser efetuada da direita para a esquerda e de cima para baixo. Cada objeto sinaliza uma operação distinta, e existem setas indicando o caminho a seguir. Vamos analisar o plano de um select na tabela Orders , localizado no database NorthWind :

PS: Para geração do plano, selecione Query...Display Estimated Execution Plan na barra de ferramentas do Query Analyzer ou aperte em conjunto <CTRL>+L

O simbolo indica que está sendo efetuada uma varredura sequencial na tabela Orders (=Clustered Index Scan) , tendo por base o índice cluster PK_Orders.

O símbolo indica que após o processo de varredura sequencial na tabela Orders , o resultado da seleção será apresentado ao cliente.

Vamos buscar informações de outro empregado na tabela Orders e observar o plano de execução:

Nesse plano existe dois simbolos novos: representa um processo de Index Seek , e indica que a busca do empregado para employeeId=9 foi uma busca pontual, realizada com o auxílio de um índice não-cluster.

Verificando a composição do índice utilizado na pesquisa com o comando sp_HelpIndex Orders , nota-se que esse índice é composto apenas pela coluna employeeId . Como a query executada necessita de todas as colunas da tabela Orders , será necessário acessar a página de dados através de um ponteiro localizado na estrutura do índice. Esse processo de busca da página de dados à partir de ponteiros localizados na estrutura do índice é conhecido por bookmark lookup, representado no plano de execução pelo símbolo .

Agora vamos a uma pergunta básica :

Como tirar proveito desse tipo de leitura?

Podemos enumerar vários detalhes importantes:

1)  Acrescentar e/ou alterar filtros na cláusula where tem o poder de “modificar” um plano de execução. Nas queries executadas foram desenvolvidos planos de execução diferentes para empregados diferentes. Porque o plano foi diferente ? Ora, na tabela Orders existem 43 linhas para employeeId=9 e 156 linhas para employeeId=4 . O esforço requerido para pesquisar 156 linhas na estrutura do indice e, para cada uma dessas linhas acessar a correspondente página de dados é maior do que acessar diretamente as páginas de dados sem auxílio de índice. Normalmente o otimizador utiliza um índice não-cluster somente se conseguir descartar 95% dos dados existentes na tabela. Aplicando o percentual de 95% sobre 830, chegamos numa média de 42 linhas, o que explica a utilização do indice somente no segundo select.

2) Processos de Scan são lentos : para executar o primeiro select , foi necessário uma leitura sequencial das de todas as linhas presentes na tabela Orders . Como a tabela é pequena (possui somente 830 linhas!) esse problema passa desapercebido. Agora imagine se, ao invés de 830, a tabela Orders possuísse 1.500.000 linhas ... Processos de scan devem ser sempre evitados, principalmente em tabelas com grande número de linhas.

3) Processos de Seek são rápidos: buscas com operadores do tipo seek são buscas pontuais e específicas. Para executar o segundo select foram necessárias 43 leituras num universo de 830 linhas existentes. Como sei disso? Basta passar com o mouse sobre o plano de execução, para que informações detalhadas sejam apresentadas sob a forma de um “post-it”. Procure pela linha “Estimated Row Count” ao passar com o cursor sobre o símbolo “Index Seek” (veja figura abaixo).

3) Processos de Seek em indices não-cluster são eficientes : um processo de seek é rápido porque utiliza a estrutura otimizada do índice para ler SOMENTE a informação desejada. Assim, o segundo select leu SOMENTE as linhas da tabela que continham employeeId=9.

4) Processos de Seek em indices cluster são muiiiiito mais eficientes: um processo de seek num índice cluster pode(*) desencadear um processo BookMark Lookup para localização da página de dados da tabela em questão. Índices cluster ordenam a própria página de dados da tabela, portanto não requerem estruturas em separado para prover a funcionalidade do índice. Assim, pesquisas em índices cluster são mais eficientes porque NÃO REQUEREM processos de bookmark lookup, pois a página de dados é a própria página do indice.

(*) Nem toda pesquisa em índice não-cluster irá gerar um processo de bookmark lookup . O bookmark lookup irá existir somente quando o índice não comportar em sua estrutura todas as colunas explicitadas no comando select.

Bem pessoal por hoje é só, mas continuaremos a explorar esse assunto na próxima coluna.

Paulo Ribeiro (psribeiro@hotmail.com) é Microsoft MCDBA e membro da equipe editorial da SQL Magazine. Atua como DBA sênior em SQL Server na Livraria e Papelaria Saraiva S/A.