T-SQL – Execution Plan: Entenda-o e utilize-o!

Quando se fala em "Tuning SQL" a primeira coisa que vem em minha mente é: Execution Plan. Não consigo imaginar um mundo sem Execution Plan, não um mundo de queries bem indexadas e performáticas. Por isso neste artigo vamos entender como utilizar o SQL Server Execution Plan para melhorar nossas consultas em SQL.


Antes de mais nada...

Para os exemplos abaixo (e a maioria dos outros exemplos que veremos neste site) utilizei a base de dados de exemplo da Microsoft: AdventureWorks. 

Visualizando o Estimated Execution Plan:

Primeiramente vamos escrever uma consulta no SQL Server 2005 para visualizarmos o Execution Plan. Copie ou digite (sugiro a quem está aprendendo digitar, pois ajuda a fixar a sintaxe SQL) o código baixo:

SELECT
e.ContactID, e.Title, ad.AddressLine1, ad.City, ad.PostalCode
FROM
HumanResources.Employee e
INNER JOIN
HumanResources.EmployeeAddress ea ON e.EmployeeID = ea.AddressID
INNER JOIN
Person.Address ad ON ad.AddressID = ea.AddressID

Logo em seguida, pressiona CTRL+L e então você verá o Estimated Execution Plan:

Entendendo o Execution Plan:

Não me aprofundarei sobre o que significa cada ação que você está vendo no Execution Plan, para uma descrição detalhada sobre todos os possíveis itens do Execution Plan, clique aqui.
A leitura deverá ser feita da direita para esquerda, de cima para baixo. Traduzindo, temos:
1 - Busca em um índice secundário (AK_EmployeeAddress_rowguid) na tabela EmployeeAddress as informações solicitadas;
2 - Busca em um índice primário (PK_Employee_EmployeeID) na tabela Employee as informações solicitadas;
3 - Compara os dados obtidos nas buscas 1 e 2 e une-os;
4 - Busca em um índice secundário (IX_Address_AddressLine1...) na tabela Employee as informações solicitadas;
5 - Compara os dados obtidos na busca do item 4 e no resultado da comparação do item 3 e une-os;
6 - Exibe as informações solicitadas ao usuário final.

Podemos ver claramente os dois "joins" que escrevemos na consulta no Execution Plan (Hash Match). É importante também observar outro fator: "Cost". Essa porcentagem informada é o custo que essa ação tem em relação ao lote (batch) executado, ou seja, se for necessário fazer alguma intervenção no código escrito acima, temos que atacar as ações que tem um custo (Cost) maior, neste caso, a busca no índice secundário da tabela Address que detêm 55% de todo o custo dessa query.

Para visualizarmos mais detalhes sobre cada ação, posicione o cursor do mouse em cima do ícone de uma ação, para o nosso exemplo, o Index Scan (NonClustered) na tabela Address, conforme imagem abaixo:

Vou explicar os principais itens dessa ação do Execution Plan: 


Physical Operation: Operação a ser realizada fisicamente no HD.
Local Operation: Operação a ser realizada logicamente no HD.
Estimated I/O Cost: Custo de I/O no HD para a execução desta ação. 
Estimated CPU Cost: Custo de CPU para a execução desta ação. 
Operator Cost: Custo da operação (I/O + CPU), comparada a todo o lote (batch) de execução.

Podemos então ver claramente que o maior custo para essa operação está no I/O de disco. Vamos fazer uma pequena alteração no Execution Plan e ver o que acontece?

CREATE INDEX IX_Address_AddLn1CityPostCode ON Person.Address(AddressLine1, City, PostalCode) WITH FILLFACTOR = 100

Execute o código acima e aperte CTRL+L novamente para exibir o Execution Plan abaixo:

Podemos ver que a criação de um índice mais especifico melhorou o desempenho da nossa consulta. Claro que isso não foi um ganho significativo, pois na criação de um índice, existem vários fatores que devem ser considerados, mas o nosso foco é Execution Plan, o exemplo foi para mostrar como uma rápida análise pode trazer benefícios em nossas consultas. Veja abaixo como ficou a ação Index Scan (NonClustered) na tabela Address:

Conclusão:
A utilização do Execution Plan é vital para o desenvolvimento de sistemas com banco de dados. Sempre suspeite daquela query simples que não requer muito da base. Um simples select em uma tabela sem índice poderá gerar locks e dead locks, causando muito transtorno e dor de cabeça para os analistas de suporte, por isso, sempre analise toda consulta que você estiver desenvolvendo, crie o vício de apertar CTRL+L antes do F5.

Se você tem alguma duvida ou quiser fazer algum comentário sobre esse artigo , comente! Seus comentários serão bem vindos e serão respondidos o mais breve possível.

Abs,

Afonso Lage