Blog – Fabiano Neves Amorim

Introdução ao Query Processor

Autor – Fabiano Neves Amorim

Início

Escolher o melhor caminho para chegar a determinado lugar pode ser considerado para muitos uma arte, isso porque sempre existem vários caminhos que levam ao mesmo destino. Executar uma tarefa da forma mais eficiente possível requer que o caminho percorrido seja o melhor dentre as centenas de variáveis que podem influenciar na escolha do melhor percurso.

No SQL Server o responsável por calcular a maneira mais eficiente de acesso aos dados é chamado de Query Processor, ele é dividido em duas partes, o Query Optimizer e o Query Execution Engine. Veremos neste artigo como o Query Optimizer funciona e quais os passos necessários para execução de um comando T-SQL.

Entender como funciona e como interpretar o trabalho do Query Optimizer é uma das melhores maneiras de aprimorar seus conhecimentos em SQL Server. Esse conhecimento será de grande valor quando você precisar fazer algum trabalho de tunning em banco de dados.

Ambiente

Para melhor entendimento dos exemplos deste artigo criaremos uma tabela, com alguns dados e uma visão, que servirão como base para os testes que serão apresentados. A Listagem 1 contém o script para criação destes objetos.

O Script cria a tabela Funcionarios com algumas informações (ID, Nome, Salário, Telefone e Cidade) e, em seguida, são inseridos alguns registros. Logo após, uma view (vw_Funcionarios) é criada. A grosso modo, podemos dizer que Views são tabelas virtuais definidas por uma consulta T-SQL. A nossa view, criada na Listagem 1, retorna o nome e o salário de todos os funcionários que ganham mais de R$ 900,00.

CREATE TABLE Funcionarios(ID Int IDENTITY(1,1) PRIMARY KEY,

Nome VarChar(30),

Salario Numeric(18,2),

Telefone VarChar(15),

Cidade VarChar(80));

INSERT INTO Funcionarios(Nome, Salario, Telefone, Cidade)

VALUES(‘José’, 850.30, ’11-55960015′, ‘São Paulo’);

INSERT INTO Funcionarios(Nome, Salario, Telefone, Cidade)

VALUES(‘Antonio’, 950, ’11-81115544′, ‘São Paulo’);

INSERT INTO Funcionarios(Nome, Salario, Telefone, Cidade)

VALUES(‘João’, 1200, ’11-44123321′, ‘São Paulo’);

CREATE VIEW vw_Funcionarios

AS

SELECT Nome, Salario FROM Funcionarios

WHERE Salario > 900

Listagem 1. Script para criação dos objetos de teste

Query Optimizer

Quando um comando T-SQL é executado no SQL Server o Query Processor entra em ação para gerar um plano de execução. Este plano dirá qual é a melhor maneira de acessar os dados gastando menos recursos e com o desempenho mais eficiente possível.

Podemos observar na Figura 1 a ação do Query Optimizer (em vermelho) e uma série de passos para compilar e executar um comando T-SQL. Vamos analisar melhor este comportamento.

Figura 1. Fluxograma de passos necessários para gerar um plano de execução

Supondo que um SELECT simples, por exemplo, SELECT * FROM Funcionarios, seja enviado ao servidor, a primeira tarefa que o Query Processor fará com o comando é verificar se o mesmo está no Cache Plan (mais informações sobre o Cache Plan no final do artigo). Caso ele não esteja em cache, o Query Processor enviará o comando para os processos de Parse e Bind.

O Parse/Bind executa um processo conhecido como Algebrizer. Durante este processo o SQL tenta encontrar possíveis erros de escrita na sintaxe e lógica do comando. Por exemplo, o comando “select id from tab1 group by nome” gera uma exceção, pois a coluna id não pertence ao group by e não está utilizando uma função de agregação (SUM, COUNT, …). O Algebrizer também expande as definições do comando, isso significa que ele troca “select *” por “select col1, col2, col3…”, ou “select col1 from View” pelo nome das tabelas envolvidas na view.

Sempre que uma view é referenciada em uma consulta, o SQL Server acessa as tabelas que contém os dados. Na Figura 2, por exemplo, podemos visualizar que o SQL acessa a tabela Funcionarios para ler os valores das colunas Nome e Salario.

Figura 2. Ilustração de uma View acessando uma tabela.

Outro passo será resolver os nomes e tipos de objetos envolvidos na consulta. Pode acontecer de haver um sinônimo para uma determinada tabela que está em outro servidor. Quando isso acontece, o SQL precisa identificar que este sinônimo faz referência a um objeto que está em outro banco de dados, e este banco pode estar até mesmo ligado a outro servidor utilizando um Linked Server.

Após estas análises o Parse/Bind retorna um binário chamado Query Processor Tree, que é uma representação lógica dos passos necessários para a execução do comando SQL. O Query Processor Tree é enviado para o próximo passo da execução da consulta, que é a análise do Query Optimizer.

É importante destacar que nem sempre um comando é enviado para a análise do Query Optimizer. Por exemplo, alguns comandos DDL, tais como o CREATE Table, que são de definição das estruturas dos dados, não têm necessidade de uma análise do Query Optimizer, pois só há uma forma de o SQL executar esta operação.

Quando o Query Optimizer recebe o Query Processor Tree, ele dará inicio a uma série de análises a fim de encontrar qual é a maneira mais eficiente de acessar os dados desejados.

O Query Optimizer trabalha baseado no custo de cada operador de acesso a dados, ou seja, ele tenta encontrar a maneira que gastará menos recursos para retornar os dados. Também é levada em consideração a velocidade de execução da consulta, por exemplo, ele pode decidir por utilizar paralelismo a fim de retornar os dados mais rapidamente. Ao optar por este recurso, o SQL utiliza mais de um processador para executar uma consulta. Dessa forma, o gasto com recursos se torna maior, já que mais processadores serão utilizados, porém, o tempo de retorno tende a ser menor comparado a execução em apenas um processador.

Durante a fase de análise o Query Optimizer realiza algumas tarefas, entre elas: identificar todos os possíveis argumentos de pesquisa que podem estar especificados na cláusula WHERE e verificar se existem Joins entre tabelas que devem ser otimizados. Baseado nestas informações, ele analisa quais as opções de acesso aos dados, quais índices deve utilizar, em qual ordem os Joins devem ser realizados e qual o melhor algoritmo de Join para cada operação.

Na análise do Query Optimizer também existe um processo chamado “Simplification”, que é executado logo no inicio da otimização. Uma das tarefas do “Simplification” é conhecida como “Predicate PushDown”, onde ele tenta reescrever sua consulta jogando os argumentos de pesquisa para a cláusula where (veja a Listagem 2).

SELECT * FROM Funcionarios

INNER JOIN Funcionarios AS Funcionarios2

ON Funcionarios.ID = Funcionarios2.ID

AND Funcionarios.Nome = ‘Antonio’

WHERE Funcionarios.ID > 1

Listagem 2. Consulta com argumentos de pesquisa especificados no join

Com base na consulta da Listagem 2, quando o comando for executado, durante a fase de Simplification, o SQL irá reescrever a segunda consulta deslocando o argumento de pesquisa (Funcionarios.Nome = ‘Antonio’) para a cláusula where, como pode ser visto na Listagem 3.

SELECT * FROM Funcionarios

INNER JOIN Funcionarios AS Funcionarios2

ON Funcionarios.ID = Funcionarios2.ID

WHERE Funcionarios.ID > 1

AND Funcionarios.Nome = ‘Antonio’

Listagem 3. Consulta com argumentos de pesquisa especificados no where

Na Figura 3 temos uma simples representação gráfica de como o Query Optimizer funciona. Como pode ser visto, o resultado da análise será o Query Plan, ou plano de execução.

Figura 3. Query Optimizer

Na Figura 4 podemos visualizar a representação gráfica de um plano de execução simples.

Com base na estrutura da tabela Funcionarios, podemos verificar na Figura 4 que para o comando SELECT * FROM Funcionarios, o Query Optimizer gerou um plano de execução que acessa a tabela Funcionarios utilizando o operador Clustered Index Scan.

Durante a criação de uma tabela o SQL Server automaticamente cria um índice clustered para as colunas definidas como primary key. Este é o comportamento default, e pode ser alterado definindo a primary key como nonclustered no comando de criação da tabela. O SQL utilizou este índice clustered para ler os dados da tabela Funcionarios.

Veremos mais informações sobre operadores na seção Operadores.

Figura 4. Execution Plan gerado pelo Query Optimizer

Visualizando o Plano de execução

Existem três maneiras de visualizar um plano de execução: o modo gráfico, modo texto e XML. Vamos analisar os dois modos mais utilizados para análise: gráfico e texto.

Para visualizar o plano de execução em modo gráfico, basta digitar um código T-SQL no SQL Server Management Studio e pressionar ctrl+l. Neste modo diversas informações complementares são exibidas. Estas informações são chamadas de hints. Os hints contêm informações importantes sobre o objeto que está sendo acessado ou sobre a operação que será executada, por exemplo, dados sobre a utilização de memória, CPU e custo de execução de cada operador dentro de todo o plano de execução. Também são apresentados dados informando se o resultado do operador está ordenado ou não, número estimado de linhas que serão retornadas e etc.

Para exibir os hints de um determinado operador, basta posicionar o cursor do mouse sobre o operador desejado.

Na Figura 5, por exemplo, podemos analisar diversas informações em relação aos custos gerados pelo operador Clustered Index Scan. Entre elas:

• Actual Number of Rows: Número de linhas retornadas pelo operador;
• Estimated I/O Cost: Valor relativo ao custo de I/O necessário para executar o operador. É desejado que este valor seja o menor possível;
• Estimated CPU Cost: Valor relativo ao custo de CPU necessário para executar o operador. É desejado que este valor seja o menor possível;
• Estimated Operator Cost: Este é o custo do operador dentro de todo o plano de execução. Um percentual de custo é exibido entre parênteses.

Uma lista completa com a descrição de cada valor exibido pode ser encontrada no Help do SQL Server.

Figura 5. Hint com dados do plano de execução

Como podemos perceber, o valor exibido nas informações de Cost é um número sem um real sentido, pois não é o custo em milissegundos, nem o número de IO. Este valor é utilizado internamente pelo Query Optimizer para identificar qual será o melhor operador a ser utilizado.

O modo gráfico de visualização é a maneira mais simples de analisar planos de execução. Seguem alguns pontos que são importantes de serem destacados:

• Possui uma interface que facilita a visualização dos hints e a compreensão do plano de execução;
• Fácil de encontrar possíveis pontos de contenção, isto é, operadores que possuem alto valor de custo. Cada operador possui um valor de 0 a 100%, onde é apresentada a porcentagem de custo de execução de cada um em relação a todo o plano de execução. Ao analisar o plano, podemos facilmente identificar quais são estes operadores;
• Pode ser salvo em formato XML e aberto no SQL Server Management Studio (somente SQL Server 2005 e posterior).

Outra maneira de visualizar os planos de execução é utilizando o modo texto. Para habilitar a exibição do plano de execução neste modo é necessário ativar uma das opções de ShowPlan, são elas:

• SET SHOWPLAN_TEXT ON: Retorna o plano de execução de cada comando do batch. Os comandos não são executados, portanto o plano de execução é o estimado, e não o atual;
• SET SHOWPLAN_ALL ON: Semelhante ao SHOWPLAN_TEXT, a diferença entre eles é que o SHOWPLAN_ALL retorna mais informações sobre o plano;
• SET STATISTICS PROFILE ON: Retorna o plano de execução completo, semelhante ao SHOWPLAN_ALL, com a diferença de que o PROFILE executa os comandos do batch, gerando o plano atual;
• SET SHOWPLAN_XML ON: Retorna o plano de execução em um XML formatado. Não executa os comandos do batch.
• SET STATISTICS XML ON: Semelhante ao SHOWPLAN XML, com a diferença de que o STATISTICS XML executa o batch.

Como exemplo, execute o comando SET SHOWPLAN_TEXT ON e depois execute a consulta SELECT * FROM Funcionarios. O resultado será a exibição do plano de execução em modo texto, como pode ser visto Listagem 4.

SELECT * FROM Funcionarios

|–Clustered Index Scan(OBJECT:( [dbo].[Funcionarios].[ PK_Funciona__3214EC277D430130]))

Listagem 4. Exemplo de consulta e plano de execução em modo texto

Este plano é exatamente o mesmo que visualizamos na representação gráfica da Figura 4. Ele representa uma simples leitura dos dados da tabela Funcionarios, utilizando operador Clustered Index Scan, que acessa o índice PK__Funciona__3214EC277D430130.

Na Listagem 5 podemos visualizar um plano de execução em modo texto para um update na tabela Funcionarios. Observe que o texto apresentado contém muitas funções de processos internos do SQL Server, o que acaba fazendo com que o plano fique complexo de entender. Entretanto, muitas vezes ele é a melhor maneira de se analisar o que o SQL está fazendo. Sobre este modo, os pontos que merecem destaque são:

• Mais fácil para analisar grandes planos de execução, pois o plano fica bem organizado em uma árvore hierárquica. A área de visualização dos planos em modo gráfico exige que tenhamos que ficar arrastando as barras de rolagem de um lado para o outro, tornando a análise um pouco mais difícil;
• Todas as informações já são exibidas, não precisa dos hints;
• Fácil para exportar para Excel ou enviar por e-mail.

UPDATE Funcionarios SET Nome = 50

WHERE ID = ’10’

UPDATE [Funcionarios] set [Nome] = @1 WHERE [ID]=@2

|–Clustered Index Update(OBJECT:( [dbo].[Funcionarios].[PK…]), SET:( [dbo].[Funcionarios].[Nome] = [Expr1003]), DEFINE:([Expr1003]=CONVERT_IMPLICIT(varchar(30),[@1],0)), WHERE:( [dbo].[Funcionarios].[ID]=CONVERT_IMPLICIT(int,[@2],0)))

Listagem 5. Exemplo de consulta e plano de execução em modo texto

Operadores

Os ícones que são exibidos nos planos de execução (Figura 6) são conhecidos por operadores. Eles são os responsáveis por executar as operações necessárias para rodar um comando T-SQL.

Existem diversos operadores e ao longo dos artigos sobre o Query Optimizer veremos mais alguns deles. Dessa forma, vamos entender um pouco mais sobre como eles se comportam, quando e porque são necessários.

Operadores executam e calculam tarefas geradas pelo plano de execução, por exemplo, um operador de Nested Loop executa um Join. Operadores de Index Seek fazem leitura de um range de valores em um índice, por exemplo, uma consulta que deseja retornar os dados de uma tabela onde o valor da coluna Código esteja entre 5 e 10, em uma tabela que contém registros de 0 a 100, o SQL pode usar o operador de Index Seek para ler apenas as páginas de dados do índice que contém as linhas com os valores de 5 a 10. Operações de agregação como MAX, MIN, SUM, COUNT e AGV são calculadas pelo operador de Stream Aggregate. O Stream Aggregate agrupa todas as linhas de uma tabela por uma ou mais colunas, calculando o valor do comando de agregação utilizado na consulta. Por exemplo, uma consulta que deseja retornar quantos pedidos os clientes fizeram em 2008 agrupado pelo nome do cliente; neste caso, o SQL irá utilizar o Stream Aggregate para agrupar todos os clientes em uma linha e calcular o SUM da quantidade de pedidos.

Como já mencionado, para produzir o melhor plano de execução o Query Optimizer analisa qual é o melhor tipo de operador para executar determinada tarefa, tentando encontrar qual deles executará o processo mais rápido e/ou utilizando a menor quantidade de recursos.

Todo operador executa os métodos Open(), GetRow() e Close(). Devido a esta característica, os operadores são independentes, e, por isso, o SQL consegue gerenciá-los com maior facilidade. Como não existe dependência, um operador SORT pode ser utilizado para ordenar um resultado ou pode ser utilizado para ordenar uma coluna, a fim de saber qual é o maior valor (MAX) da tabela. Isso faz com que o Query Optimizer tenha mais opções de escolha sobre qual operador utilizar.

Uma característica importante a ser destacada é que existem duas categorias de operadores: os operadores “nonblocking” e os operadores “blocking” ou “stop-and-go”:

• Os operadores nonblocking são aqueles que lêem as linhas do input e produzem o output ao mesmo tempo. No método conhecido por GetRow(), conforme a leitura das linhas vai acontecendo, o operador executa sua tarefa e retorna o output para o próximo operador fazer seu trabalho. O operador de Nested Loop é um bom exemplo desse comportamento. Assim que a primeira linha é recebida (GetRow()) o SQL faz o Join com a tabela do outer, e caso o relacionamento seja verdadeiro a linha é retornada no output. Este processo se repete até o fim da leitura da tabela do Join;
• Já os operadores “blocking” necessitam primeiro ler todas as linhas (normalmente no método Open()) do input para depois retorná-las para o output. Um exemplo clássico de operadores blocking é o Sort, que necessita primeiro ler todas as linhas do input para fazer a ordenação e, por fim, retornar as linhas ordenadas.

Existem alguns cenários onde cada tipo de operador é melhor aproveitado, por exemplo, operadores nonblocking são preferíveis quando utilizamos as cláusulas TOP, Hint FAST N ou mesmo um Exists. Como exemplo vamos utilizar o comando Exists. Este comando é utilizado para validar se um valor existe dentro de um contexto. Normalmente ele é utilizado junto com subqueries para validar se um valor existe em uma tabela.

Durante a execução do Exists, se ao menos uma linha for retornada para o output, isso já caracteriza uma condição verdadeira. Ou seja, se houve algum retorno, isso significa que o valor existe, e neste caso o Query Execution (responsável por executar os planos gerados pelo Query Optimizer) encerra o processo de leitura (GetRow).

Apenas uma linha é o suficiente para saber se o valor procurado existe ou não. Se tivermos que esperar todas as linhas serem lidas para depois retornar o output, como os operadores blocking fazem, esta vantagem de encerrar o processo assim que encontrar alguma linha não seria aproveitada.

Existem pouco mais de 100 operadores, entre eles: Scan, Compute Scalar, Aggregate, Sort, Spools e Key Lookup, apresentados na Figura 6.

Figura 6. Alguns operadores do plano de execução

Para finalizar esta seção é importante citar que não existe o melhor operador para fazer um Join ou uma Agregação. Cada operador é melhor para uma determinada situação.

Cache

Como podemos observar, o processo de geração de um plano de execução é bem complexo e requer um tempo extra na execução da consulta. Quanto maior e mais complexa for sua consulta, mais tempo o Query Processor leva para analisar todas as variantes, e por fim gerar um plano de execução para o comando T-SQL. Toda essa operação de gerar o plano de execução causa delay e normalmente faz alto uso de CPU. Por isso, o SQL Server usa um mecanismo para evitar que toda vez que uma consulta for enviada para o banco, o Query Processor tenha que gerar um novo plano de execução. É justamente este o papel do Cache Plan: evitar que uma consulta que já tenha passado pelo Query Optimizer seja enviada novamente para otimização.

Conforme podemos observar no fluxograma (Figura 1), o primeiro processo que o Query Processor faz é procurar no cache se já existe um plano de execução para a consulta enviada ao servidor. Isso significa que quando um plano de execução é gerado para uma determinada consulta, o SQL grava este plano em uma área de memória a fim de reutilizar o mesmo plano de execução para consultas semelhantes.

A área de memória utilizada para armazenamento dos planos de execução é exatamente a quantidade de memória disponível para uso do servidor SQL Server. No SQL Server 7.0 havia uma área específica para o armazenamento dos planos, chamada “procedure cache”. Porém, a partir da versão 2000 isso foi alterado para que o SQL pudesse utilizar toda a memória disponível ao SQL Server. Na verdade, no SQL Server 7.0 só eram armazenados planos de execução de procedures (por isso se chamava “procedure cache”), consultas ad-hoc não tinham o plano armazenado em cache.

Reutilizando planos de consulta

Em muitas ocasiões, reutilizar o plano de execução pode ser uma grande vantagem, já que economizamos todo o tempo que seria gasto pelo Query Processor para analisar um novo plano. Entretanto, reutilizar um plano de execução que está armazenado em cache pode ser um problema, pois nem sempre a mesma consulta retorna a mesma quantidade de dados. Por isso, o SQL Server é bem cauteloso quando estamos falando de reutilização de planos de execução, já que pode haver situações onde um mesmo plano não seja o ideal para consultas parecidas. Por exemplo, vamos imaginar que uma consulta que retorna apenas 10 linhas seja enviada pela primeira vez para o SQL Server, e o Query Processor gerou um plano de execução que utiliza um índice nonclustered mais um bookmark para ler uma pequena quantidade de registros. Nestas condições o Query Engine executa este plano e depois o armazena em cache.

Agora imagine que uma nova consulta, igual à primeira, seja enviada para o SQL Server, mas desta vez a condição de busca informada no where requeira que milhares de linhas sejam retornadas. Se o SQL optar por reutilizar o plano de execução isso não vai ser bom, pois é bem provável que seja melhor fazer um Scan em toda a tabela do que utilizar o índice mais o bookmark. O cenário descrito é bastante comum de acontecer.

Outro fator importante de ser observado é que algumas operações podem deixar um plano que está em cache desatualizado. Por exemplo, pode acontecer do schema que envolve a consulta ser alterado, ou um novo índice ser criado ou até mesmo apagado. Nestas situações o SQL não conseguirá reutilizar o plano de execução, o que resulta em uma nova análise do Query Processor.

Existem diversas maneiras de saber se um plano de execução foi reutilizado ou não. Para isso abra uma consulta no SQL Server Management Studio e execute o código da Listagem 6.

Na primeira linha é executado um comando (DBCC FREEPROCCACHE) para limpar a área de cache atual, apagando todos os planos de consulta que estão armazenados em memória. Feito isso, são executadas três simples consultas para retornar os dados de um determinado funcionário especificado na cláusula where. Por fim, selecionamos os registros da sys.dm_exec_cached_plans, que é uma view do sistema que retorna os planos de execução que estão em cache. Esta view retorna uma coluna chamada plan_handle que é do tipo binário. Para converter este valor binário para um texto, o SQL disponibiliza uma function do sistema chamada sys.dm_exec_sql_text. Ela lê uma entrada em binário e retornar uma coluna chamada text, que contém o plano em modo texto.

DBCC FREEPROCCACHE;

GO

SELECT * FROM Funcionarios WHERE Nome = ‘José’

GO

SELECT * FROM Funcionarios WHERE Nome = ‘Antonio’

GO

SELECT * FROM Funcionarios WHERE Nome = ‘José’

GO

SELECT usecounts, cacheobjtype, objtype, "text" AS Comando_SQL

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text (plan_handle)

WHERE cacheobjtype = ‘Compiled Plan’

AND "text" NOT LIKE ‘%dm_exec%’;

Listagem 6. Exemplo de consulta e acesso ao cache plan

O resultado do comando acima deve ser parecido com os registros da Tabela 1.

UseCounts	CacheObjType	Objtype	Comando_SQL
1	Compiled Plan	Adhoc	SELECT * FROM Funcionarios WHERE Nome = ‘Antonio’
2	Compiled Plan	Adhoc	SELECT * FROM Funcionarios WHERE Nome = ‘José’

Tabela 1. Valores que estão em cache retornados pela DMV sys.dm_exec_cached_plans

A DMV (Dynamic Management Views) sys.dm_exec_cached_plans retorna todos os planos de execução que estão armazenados no cache do SQL Server. Você pode utilizar esta view para saber se um determinado plano foi reutilizado ou não, a quantidade de memória que um plano está utilizando e também para verificar quantas vezes um plano foi reutilizado.

A coluna UseCounts retorna a quantidade de vezes em que o plano de execução foi reutilizado. A coluna CacheObjType retorna o tipo do objeto em cache. Ela pode apresentar os seguintes valores:

· Compiled Plan: Representa um plano de execução completo;

· Compiled Plan Sub: Existe uma propriedade do banco chamada “optimize for ad hoc workloads”. Quando habilitada, assim que uma consulta AdHoc é enviada pela primeira vez ao SQL, ele grava um Plan Sub no cache, não com o plano completo, mas somente com um Plan Sub. Isso vai permitir que o SQL reconheça que um plano já foi gerado para esta consulta. A partir da segunda execução deste comando, o SQL substitui este plano pelo plano completo;

· Parse Tree: Representa o parse de uma view, function, etc.;

· Extended Proc: Grava o plano para extended procedures, por exemplo, a xp_cmdShell;

· CLR Compiled Func: Funções criadas utilizando CLR;

· CLR Compiled Proc: Procedures criadas utilizando CLR.

A coluna Objtype retorna qual o tipo do objeto que está armazenado, por exemplo, pode ser um comando Adhoc, proc, view, trigger, entre outros.

Observe na Tabela 1 que a coluna UseCounts contém o valor 2 para a consulta SELECT * FROM Funcionarios WHERE Nome = ‘José’, mostrando que o SQL Server utilizou duas vezes o mesmo plano de execução.

Uma observação importante em relação ao cache plan é que para consultas adhoc, que não estejam parametrizadas (ou seja, iguais a que utilizamos acima), para que o Query Processor consiga fazer proveito do plano que está em cache as consultas precisam ser escritas exatamente iguais. Inclusive os valores no where terão que ser os mesmos. Repare que no exemplo da Listagem 6 o SQL só reutilizou o plano de execução em cache porque a terceira consulta enviada para o servidor é idêntica à primeira consulta, inclusive o WHERE Nome = ‘José’.

Isso significa que cada caractere será analisado e comparado com o comando em cache. Uma simples quebra de linha ou uma letra maiúscula ou até mesmo um código de comentário já será o suficiente para inviabilizar o uso do plano em cache, acarretando na criação de um novo plano de execução.

Parametrização

Sempre que possível o SQL Server tenta alterar sua consulta para torná-la apta à reutilização dos planos de execução. Este comportamento chama-se parametrização. Consultas parametrizadas têm mais chances de terem seu plano de execução reutilizado.

No SQL Server 2005 existem dois tipos de parametrização, o Simple Parameterization (conhecido como auto-parameterization, no SQL Server 2000) e Forced Parameterization. Ao utilizar a parametrização Simple, o SQL é mais cauteloso em relação às quais consultas parametrizar, já a opção forced tenta parametrizar a maior quantidade de consultas possível.

Quando um banco de dados é criado o padrão de parametrização é o Simple. Para alterar esta opção acesse as propriedades do banco de dados e selecione a opção Forced na propriedade Parameterization.

Quando a opção Forced está selecionada o SQL tenta parametrizar a maior quantidade de consultas possíveis. Existe uma lista de instruções que impedem o uso de parametrização. Esta lista pode ser acessada no help do SQL Server.

Durante o processo de parametrização o SQL Server tenta alterar o valor no where por uma variável, e futuramente ele altera este valor pelo novo valor informado na consulta.

O SQL Server é bem cauteloso em relação a quando parametrizar uma consulta. Este processo somente será adotado para consultas que são consideradas seguras. Uma consulta é segura quando o plano selecionado não muda caso os parâmetros (filtros) mudem. Vejamos um exemplo de consulta considerada segura:

• Para uma consulta que busca por um valor em uma tabela, passando um filtro para uma coluna que contém um índice único, o SQL sabe que apenas um valor será retornado. Independente do valor do parâmetro recebido, pois a presença do índice único obriga que exista apenas um registro com o mesmo valor em toda a tabela.

Vejamos um exemplo de consulta considerada não segura:

• Fazer um seek, ou seja, ler um range de informação a partir de um índice pode ser uma ótima escolha. Por outro lado, utilizar este mesmo plano para uma consulta que retorna várias linhas pode não ser a melhor opção.

Vamos analisar um exemplo de parametrização e reutilização do plano de execução. Para isso execute o código da Listagem 7. Neste código, executamos duas simples consultas que retornam os dados dos funcionários, com base no ID especificado na cláusula where. Como a coluna ID é a chave primária da tabela funcionários, os valores de ID não podem se repetir. Isso caracteriza esta consulta como segura para reutilização do plano de execução.

Podemos observar na Tabela 2 que o SQL reutiliza o mesmo plano para as duas consultas.

SELECT * FROM Funcionarios

WHERE ID = 1

GO

SELECT * FROM Funcionarios

WHERE ID = 2

GO

SELECT usecounts, cacheobjtype, objtype, "text" AS Comando_SQL

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text (plan_handle)

WHERE cacheobjtype = ‘Compiled Plan’

AND "text" NOT LIKE ‘%dm_exec%’;

Listagem 7. Exemplo de consulta parametrizada

O resultado de execução da Listagem 7 deve ser parecido com os registros da Tabela 2.

UseCounts	CacheObjType	Objtype	Comando_SQL
1	Compiled Plan	Adhoc	SELECT * FROM Funcionarios WHERE ID = 2
1	Compiled Plan	Adhoc	SELECT * FROM Funcionarios WHERE ID = 1
2	Compiled Plan	Prepared	(@1 tinyint)SELECT * FROM [Funcionarios] WHERE [ID]=@1

Tabela 2. Valores que estão em cache retornados pela DMV sys.dm_exec_cached_plans

Como podemos observar nas colunas UseCounts e Objtype, o SQL Server gerou um plano de execução “prepared” e ele foi utilizado duas vezes.

As outras consultas continuam aparecendo, mas elas não contem o plano de execução, elas apontam para a linha que tem o plano de execução “prepared”. Infelizmente com as DMVs e recursos disponibilizados pelo SQL Server, não é possível visualizar esta ligação entre os planos Adhoc e o plano Prepared, este é um código interno do SQL. Estas consultas Adhoc são conhecidas como Shell Queries, elas são armazenadas para agilizar o processo de reutilização do plano. Veja na Figura 7 um exemplo de como estas consultas podem ajudar na performance da execução do comando.

Figura 7. Fluxograma de procura por plano de execução em cache

Nesta figura podemos observar que quando um mesmo comando for executado, caso ele contenha um shell query no cache, o SQL pula uma etapa do processo de localização do plano.

Observe na Tabela 2 que a linha 3 possui o plano de execução parametrizado, que é o plano prepared. O plano prepared foi gerado baseado nas consultas executadas pelo código da Listagem 7. Como podemos observar, o SQL Server trocou o valor no where por uma variável @1 e declarou a variável como datatype tinyint. Ao parametrizar uma consulta o SQL tenta encontrar qual é a melhor opção de variável a ser utilizada, neste caso, ele optou pelo tinyint por ela ocupar apenas 1 byte de armazenamento.

O datatype tinyint consegue tratar valores de 0 a 255, isto é, caso o valor informado no where seja 300 o SQL não conseguirá utilizar este plano de execução, causando uma nova recompilação do comando.

Figura 8. Exemplo de consulta parametrizada

Na Figura 8 podemos visualizar como funciona o conceito de parametrização. Ao parametrizar o valor de where Prikey, o SQL consegue reutilizar o mesmo plano de execução para as três consultas apenas alterando o valor de “?”.

Conclusão

Vimos neste artigo quais os métodos que o SQL Server utiliza para executar uma consulta SQL e como o Query Processor trabalha para acessar os dados armazenados nos arquivos de banco de dados. Ao acessar os dados, estudamos um pouco dos operadores de acesso a dados, bem como os tipos de operadores.

Também vimos como analisar um plano de execução e as análises em modo gráfico e modo texto. Por último foi apresentado o conceito de Cache Plan e parametrização de consultas.

Linked Server: É um mecanismo que permite que o SQL Server execute comandos em outro banco de dados OLE DB. Ao criar um linked Server o SQL cria uma conexão com o banco de dados destino, permitindo que os dados que estão armazenados no banco destino sejam acessados a partir da própria instancia do SQL Server. Por exemplo, é possível abrir uma consulta no SSMS e fazer um update em uma tabela do SQL Server lendo os dados de uma base Oracle.

O banco utilizado no Linked Server pode ser remoto ou local. Podemos criar um linked Server com um banco de dados Access, Oracle, MySQL, ou qualquer banco de dados que contenha um driver OLE DB.

OLE DB: (Object Linking and Embedding, Database) é um pacote que contém as informações necessárias para conexão e gerenciamento da leitura dos registros armazenados em uma fonte de dados (arquivos txt, xls, entre outros) ou bancos de dados.