Conhecendo Indices Step-by-Step III

Tempo de leitura: 6 minutos

Fala galera, blz? Bem-vindo ao terceiro artigo da série Conhecendo Índice Step-by-Step. Hoje iremos “navegar” pelos nossos índices e demonstrar como o SQL Server faz isso por debaixo dos Panos. Literalmente é isso mesmo “NAVEGAR”…rs. Não adianta saber onde um índice pode ajuda-lo ou atrapalha-lo sem saber como o SQL Server decide usá-lo e quando decide usá-lo. Este post é dedicado ao Mestre Fabiano Neves Amorim, um cara que manja demais de QO. Com o treinamento de Avanced Tunning que tive ministrado por ele, abriu muito minha cabeça e isso me ajudou a crescer profissionalmente. Vamos para de falar e mão na massa…..

Obs: Os scripts que foram usados para a carga da tabela pertencem ao Fabiano Amorim.

A tabela que utilizaremos se chama Heap. Essa tabela não possui um índice sequer, o que o SQL Server irá fazer. Primeiros precisamos listar todas as páginas que são alocadas para a nossa heap. Thiago, mas, como faremos isso? Simples! Faremos isso através dos comandos dbcc. O comando abaixo retorna todas as páginas alocadas para a nossa heap e mais algumas informações importantes. Vejamos:

DBCC TRACEON (3604)

DBCC IND (dbMonitoring, Heap, 1)

Todas as informações retornadas são importantes, porém, eu irei falar das colunas que mais considero importante para o nosso propósito:

PageFID – Id do arquivo de banco de dados que no caso da minha base de dados é o três. Isso porque eu tenho um arquivo .ldf (id 2), um arquivo de dados primário .mdf (Id 1) e um arquivo secundário .ndf (id 3) onde encontram-se todos os meus objetos definidos pelo o usuário.

PageId – Id da página de dados.

PageType – Tipo da página de dados:

• 1 – data page
• 2 – index page
• 3 and 4 – text pages
• 8 – GAM page
• 9 – SGAM page
• 10 – IAM page
• 11 – PFS page

Após o comando retorna essas informações, usaremos o PageFID e o PageID no comando dbcc. A sintaxe para o comando é:

DBCC PAGE({‘NomeDoBanco’ | IdDB}, IDArquivo, NumPagina [,printopt={0|1|2|3}])

No nosso caso ficaria da seguinte forma

DBCC PAGE(dbMonitoring, 3, 298, 3) — O número da página passada dever ser o da página IAM

Serão retornadas diversas informações da página de dados com o id 298 (a IAM), eu selecionei dois trechos de informações que considero bastante importantes. Abaixo pode ser visto o primeiro bloco de informações:

No segundo print podemos ver informações abaixo do cabeçalho da nossa página:

Agora iremos realizar um select na nossa tabela heap. Já que uma heap não tem uma ordenação física dos registros, o que o SQL Server irá fazer? Se você pensou em um Table Scan. Parabéns, você está prestando atenção. Vamos fazer um select e analisar o plano de execução:

SELECT * FROM Heap WHERE ID_Produto = 50

O plano de execução mostra para a query é o seguinte:

O produto retornado é o:

Até o momento é correto afirmar que: Um select em uma heap sem um nonclustered index o SQL Server optará por um Table Scan (Se existisse estatísticas para esta tabela, provavelmente o SQL escolheria outro plano, ainda não fiz este teste).

Agora se tivéssemos um nonclustered index? PENSE, antes de dar a resposta. No artigo anterior eu falei como o nonclustered index se comporta. Ele apenas cria a estrutura da B-Tree para as colunas no índice, certo? No nosso exemplo estamos solicitando o retorno de todas as colunas com o “*”. Como o SQL Server “encontra” as demais informações quando temos um nonclustered index em uma Heap? Se você for ao artigo anterior (criar um link aki), você verá que ele usa o RID. Mas o que diabos é o RID? Vamos ver o comportamento na prática. Vamos executar essa consulta novamente com o os statistics io ligado.

–Crio o indice para a tabela Heap

CREATE NONCLUSTERED INDEX IDX_Heap ON Heap(ID_Produto)

Executando a mesma query:

SET STATISTICS IO ON

SELECT * FROM Heap

WHERE ID_Produto = 50

SET STATISTICS IO OFF

Podemos ver que: O SQL Server optou por usar o nonclustered index para encontrar o id_produto = 50. Fazendo um RID Lookup para encontrar as demais informações (devido ao uso do “*”).

Thiago! E quantas leituras o SQL Server fez para encontrar meu registro? A reposta é: 4.

Agora vamos pular para a parte boa do artigo! Iremos simular o que o SQL Server faz nessas quatro leituras.

O primeiro passo é retorna o hexadecimal do índice que o SQL Server utilizou e depois transformar o hexadecimal em decimal para poder passar os valores para o DBCC PAGE. CALMA! Não se preocupem, o nosso amigo Fabiano Neves Amorim criou uma função que recebe o hexadecimal da página root, e retorna o comando dbcc page com os valores já convertidos.

SELECT dbo.fn_HexaToDBCCPAGE(Root), *

  FROM sys.sysindexes

 WHERE name = ‘IDX_Heap’

Vamos executar o commando DBCC retornado e simular as leituras do SQL Server.

DBCC TRACEON (3604)

DBCC PAGE (31,3,10538,3) – 1º Leitura

O retorno traz a página e suas páginas filhas. A pergunta é: Onde está o registros com o id_produto = 50? Simples meu caro Watson, está na ChildPageId 10536. Mas como assim? A segunda linha tem o id_produto a partir do 207516. O número 207516 é menor ou maior que 50? Maior. Completamos a primeira leitura, agora iremos fazer o mesmo DBCC PAGE mudando apenas a página de dados.

DBCC TRACEON (3604)

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

Agora faremos a mesma pergunta que a anterior, a única coisa que irá mudar é o ChildPageID. : Onde está o registros com o id_produto = 50? Está na ChildPageId 10408. Mas como assim? A segunda linha tem o id_produto a partir do 540. O número 540 é menor ou maior que 50? Maior. Segunda leitura concluída. Vamos para a terceira.

DBCC TRACEON (3604)

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

Até agora simulamos 3º leituras. Thiago, já chegamos no valor do Id_produto 50, não acaba por aqui? Simplesmente não, pois, a query ainda retorna mais duas colunas: Descricao e Col1. Agora o SQL Server irá usar o RID para chegar nesses valores.

Primeiro precisamos converter o valor do hexadecimal 0x4890000003003100. Inicialmente temos que querbar esse valor em quatro blocos, ficando assim:

0x4890 0000 0300 3100

Agora precisamos fazer uma alteração neste consjunto de números: O primeiro valor de cada bloco passa a ser o terceiro valor, e o segundo valor passa a ser o quarto valor, ficando da seguinte maneira: 0x9048 0000 0003 0031

Após termos os valores em mãos, basta converter para inteiro no SQL Server e ter nossos dados, para o DBCC PAGE e assim realizar a quarta leitura que o SQL Server fez anteriormente.

SELECT CONVERT(Int, 0x9048)AS Página,CONVERT(Int, 0x0003) AS Arquivo ,CONVERT(Int, 0x0031) AS Slot

Agora só precisamos executar novamente o quarto DBCC PAGE:

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

Como pode ser visto no resultado, nosso registro esta lá No Slot 49 e as três colunas: ID_Produto, Descricao e Col1.

Espero Ter ajudado

Thiago Carlos [TC] de Alencar