Conhecendo Indices Step-by-Step V

Tempo de leitura: 5 minutos

Fala Galera, blz? Bem vindos ao quinto artigo da série “Conhecendo Indices Step-By-Step”. Esse post é dedicado a Bianca Almeida de Oliveira, que esta sempre ao meu lado , minha incrível esposa. Hoje iremos ver como o SQL Server acessa os dados de um índice nonclustered em uma tabela clusterizada. Na seção três vimos como o SQL Server usa o RID para encontrar um registro especifico, conhecido como RID Lookup. A técnica aplicado para o caso de hoje é o que chamamos de KeyLookup. Vamos a prática:

Neste exemplo usaremos uma tabela chamada TB_CLIENTES que possui apenas um índice clustered na coluna ID_CLIENTE. Fazendo a query a seguir, o SQL Server optou por usar um Clustered Index Scan, vejamos:

SET STATISTICS IO ON

SELECT ID_Cliente, Nome, Col1

FROM TB_CLIENTES

WHERE Nome = ‘Colin B9A7D004’

SET STATISTICS IO OFF

Abaixo o resultado e o plano de execução:

Plano de execução:

Voltando as posts iniciais desta série é correto afirmar que os índices são construídos em estruturas B-Tree. No nosso plano o que foi apresentado foi um clustered index scan, mas, será que não seria interessante criarmos um nonclustered index por nome? Vamos avaliar?

CREATE NONCLUSTERED INDEX IXNC_Nome ON TB_CLIENTES(Nome)

Ao criar o índice, podemos afirmar que o SQL Server utilizará o índice para buscar os nomes, porém, como foi dito nos posts anteriores: O SQL server cria uma estrutura B-Tree pra cada índice e neste caso é o nome. Pare um pouco e pense caro leitor, a query apenas solicita o nome? NÃO. Ela ainda precisa retornar para o cliente a coluna Col1 e o Id_cliente, como o SQL Server busca essa informação? Ao executar a mesma query do inicio do artigo, podemos perceber que o SQL Server apenas efetuou seis leituras lógicas e fez o que chamamos de KeyLookup:

O KeyLookup pode ser visto como um dos responsáveis por alto custo no plano de execução:

Thiago! Não entendi, como o SQL Server fez isso? Vamos simular a leitura dos índices para entender esse comportamento.

Obs: Lembrando que o conceito, é muito parecido com os posts anteriores.

Tendo como base o comando DBCC PAGE gerado, vamos começar a leitura.

SELECT dbo.fn_HexaToDBCCPAGE(Root), *

FROM sys.sysindexes

WHERE name = ‘IXNC_Nome’

AND id = OBJECT_ID (‘TB_CLIENTES’)

DBCC TRACEON (3604)

DBCC PAGE (31,3,303,3) — 1º Leitura

Baseando-se no resultado do print, foram retornados dois registros. A pergunta é: A letra “C” vem antes ou depois da letra “J”? Resposta fácil: Que dizer que nossa próxima leitura é na ChildPageId 26528.

DBCC PAGE (31,3,26528,3) — 2º Leitura

Na nossa segunda leitura podemos ver que o cliente Colin B9A7D004, pode estar dentro da página 26661 ou 26662. Mas qual será a página certa? Posso afirmar com certeza que é a página 26661, pois, o cliente Colin que procuramos começa com B e em uma ordenação, os números sempre veem antes das letras. Que dizer que o cliente Colin 90026186 está antes do Colin B9A7D004. Vamos para a terceira leitura:

DBCC PAGE (31,3,26661,3) — 3º Leitura

EURECA! Encontramos nosso cara! Se você que está lendo, vem acompanhando todos os artigos da série, você deve se lembrar que quando fizemos essa mesma quantidade de leitura para uma Heap, encontramos uma coluna de nome “Heap RID (Key)” com valores em hexadecimais. Como estamos falando de uma tabela Clusterizada, temos a referência do nosso índice clustered (Coluna id_cliente(Key)) dentro do índice nonclustered. Thiago! Que dizer que todos nonclustered indexes possui o clustered index? CORRETO.

Obs: Muito cuidado ao escolher seus clustereds index. Caso você tenha uma tabela com um índice clustered composto por três colunas. E nesta tabela você tenha dois nonclustered indexes…as três colunas do índice clustered irá aparecer no índice nonclustered. Isso é um grande vilão de performance. Fique sempre atento: PERFORMANCE COMEÇA NA MODELAGEM.

Voltando ao assunto do post: A partir daqui fizemos três leituras, mas, o SQL Server apenas sabe qual o nome do cliente que ele está buscando no índice e automaticamente seu id_cliente que é 77939. Mais uma pergunta! Então, até agora apenas navegamos na estrutura B-Tree do índice nonclustered Thiago? Sim. Agora o que o SQL Server faz é pegar a “chave” e navegar na estrutura do índice clustered. Vamos então a quarta leitura? Mas, primeiro precisamos retornar o DBCC PAGE do índice clustered:

SELECT dbo.fn_HexaToDBCCPAGE(Root), *

FROM sys.sysindexes

WHERE name = ‘PK’

AND id = OBJECT_ID (‘TB_CLIENTES’)

DBCC PAGE (31,3,295,3) — 4º Leitura

Novamente a pergunta que o SQL Server faz. O valor de id_cliente 77939 é maior que o id_clente 40899? A resposta é sim. Vamos a quinta leitura, tendo como base a ChildPageID 19328.

DBCC PAGE (31,3,19328,3) — 5º Leitura

Como podemos ver, o cliente de id 77939 encontra-se dentro da página 20241 onde o id_cliente 77913 é menor que o id_cliente 77939. Vamos para a sexta e última leitura:

DBCC PAGE (31,3,20241,3) – 6º Leitura Encontramos o ID_Cliente = 77939

Pronto! Simulamos as seis leituras lógicas do SQL Server. Thiago uma pergunta! Como faço pra evitar que o SQL Server faça as últimas três leituras adicionais? Não seria mais performático pra ele achar a Col1 quando chegar no nível folha do índice nonclustered? A resposta é: Sim, seria mais fácil. No próximo post sobre índices, veremos uma das features que surgiu a partir do SQL Server 2005, o Covered Index que particularmente acho incrível.

Espero ter ajudado

Thiago Carlos [TC] de Alencar