Plano de Execução – Parte 2

Olá pessoal!

Em minha série de artigos para entender a leitura de um plano de execução e a modelagem correta de um banco de dados, estaremos abordando sobre as chaves primárias.

Este é o segundo artigo da série que iniciou com o  Plano de Execução – Parte 1.

Primary Keys

Uma questão importante na modelagem de banco de dados é definir o campo(s) que identificará o registro em sua tabela. Chamamos isto de Chave Primária (Primary Key = PK) e que não pode conter valores nulos, desta forma, iremos alterar nossa coluna para NOT NULL:

[sourcecode language=”sql”]
ALTER TABLE [dbo].[tblClientes]
ALTER COLUMN [codigo_cliente] [int] NOT NULL;
[/sourcecode]

Agora definiremos que a coluna codigo_cliente possuirá o valor que identificará nossa tabela e que será único, ou seja, não existirá um cliente com código duplicado. A definição de uma coluna como Primary Key ocorre da seguinte forma:

[sourcecode language=”sql”]
ALTER TABLE [dbo].[tblClientes]
ADD CONSTRAINT [PK_tblClientes] PRIMARY KEY (codigo_cliente);
[/sourcecode]

Você visualizará em seu SQL Server Management Studio, que a tabela ficou da seguinte forma:

Tabela_Estrutura_PK
Visualizando a estrutura da tabela

Podemos observar em nossa tabela – tblClientes – que após a criação da Primary Key foi criado automaticamente um índice do tipo Clustered e uma estatística.

Clustered Indexes

O Clustered Index é uma estrutura que ordena e armazena as informações da tabela baseado na informação chave, normalmente pela coluna(s) definada na chave primária, isto não é regra, já que pode também ser criado uma PK do tipo NONCLUSTERED.

Este é um excelente índice a ser utilizado em pesquisas, mas podemos ter um por tabela apenas. Isto porque, não poderíamos ter uma única tabela ordenadas de formas diferentes em um único local. Se analisarmos nossa tabela, chegaremos à conclusão que procurar um cliente pelo seu código é muito mais performático do que procurar algum cliente pelo seu nome e isto é resultado da existência do índice.

Teoricamente os índices no SQL Server são estruturas organizadas como B-Trees onde cada página de dados no índice é um index node. O nó superior é chamado de root e o nível mais baixo dos nós são chamados de leaf nodes, entre eles temos os níveis indermediários. Quer entender melhor como funciona um índice B-Tree dá uma olhada nesse link:

https://www.cs.usfca.edu/~galles/visualization/BTree.html

A seguir temos como funciona uma estrutura de um Clustered Index:

Retirado de https://technet.microsoft.com/en-us/library/ms177443%28v=sql.105%29.aspx
Clustered Index estrutura B-tree

Você terá ao final desta árvore binária as informações sobre o registro da(s) coluna(s) indexada(s) em seu Clustered Index, não havendo “pontes” necessárias para se chegar a informação.

Sendo assim, o fato é que buscar uma informação utilizando um índice Clustered, você tem um ganho imenso pela estrutura que é criada e que suas pesquisas irão usufruir. A imagem abaixo, ilustra isso muito bem, onde temos em Sequential Search o mesmo funcionamento de um Table Scan, enquanto em Binary Search é semelhante a uma pesquisa que chamamos de Index Seek Scan:

Créditos http://www.penjee.com
Pesquisa sequencial x binária

Um exemplo do beneficio do Clustered Index Seek Scan no SQL Server, podemos visualizar da seguinte forma no Plano de Execução:

Table Scan x Clustered Index Seek Scan

A query 2 onde foi lida uma tabela sem estrutura de índices, foi 100% do custo de processamento de todo o tempo. Além disso, ativado o STATISTICS TIME e STATISTICS IO, podemos concluir a eficiência em tempo e no volume de dados lidos:

(Query 1: Com Clustered Index)
Table ‘tblClientes’. Scan count 1, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 267 ms.

(Query 2: Com Table Scan)
Table ‘tblClientes_tablescan’. Scan count 1, logical reads 5559, physical reads 9, read-ahead reads 5558, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 748 ms.


Assista o vídeo abaixo, onde explico detalhadamente tudo nesse artigo:

Espero que tenham gostado, comentem suas dúvidas ou sugestões.

Obrigado por estar aqui!

Vithor da Silva e Silva | DBA SQL Server
vithor@vssti.com.br


Outras fontes

Microsoft – Clustered and Nonclustered Indexes Described

Clustered Index Structures

Ebook – SQL Server Execution Plans (Grant Fritchey);

Ebook – Complete Showplan Operators (Fabiano Amorim);

Deixe uma resposta