Conhecendo Indices Step-By-Step

Tempo de leitura: 4 minutos

Olá Galera, boa noite.

Desta vez escolhi um assunto extremamente importante e que vejo muita gente falando por ai. Porém, muitas pessoas ainda não entendem perfeitamente como funciona perfeitamente. Meu objetivo neste post é fazer com que o leitor entende como o SQL Server armazena e lê os dados. Isso mesmo, irei falar sobre índices. Esse artigo será divido em diversas partes, por ser um assunto com muito conteúdo e de certa forma complexo.  Já cheguei a falar sobre este assunto no blog, porém, de uma forma introdutória. Este post é dedicado a Andressa A. Martins, ela é uma pessoa que tem me ajudado muito na minha mais nova empreitada e me instruiu como os meus posts podem ter maior visibilidade na comunidade SQL Server.

Obs: Nos posts eu não vou traduzir nonclustered indexes, clustered indexes e alguns termos conhecido na documentação, pelo simples motivo de ficar ZUADO a tradução ao pé da letra.

Introdução:

Os índices são criados em colunas de uma tabela ou em view. O índice oferece uma maneira rápida de procurar dados com base nos valores dentro dessas colunas. Por exemplo, se você criar um índice para a chave primária e em seguida, procure uma linha de dados baseado em um dos valores de chave primária, SQL Server primeiro verifica que o valor no índice, e depois usa o índice para localizar rapidamente toda a linha de dados. Sem o índice, uma verificação de tabela teria de ser realizada a fim de localizar a linha, que pode ter um efeito significativo no desempenho.

Você pode criar índices na maioria das colunas de uma tabela ou em uma view. As exceções são principalmente as colunas configuradas com tipos de dados, tais como imagem, texto, e varchar (max) conhecidos como LOB’s. Você também pode criar índices em colunas XML, mas esses índices são ligeiramente diferentes do índice básico.

B-Tree:

No SQL Server, índices são organizados em B-Trees (O B é de Balanced e não de Binário). Cada página de um index em uma B-Tree é chamada de Nó (Node). O topo do nó da da B-Tree é chamado de Nó Root(Root Node). O ultimo nível desta estrutura é chamado de Nivel Folha (Leaf nodes). Qualquer nível entre o nível folha e o root é conhecido como Nível Intermediário.  As páginas de cada nível do índice estão relacionadas em uma lista duplamente ligada. Tendo como a função de um ponteiro de página para página, porém, isso não ocorre apenas em um sentido. Esses “ponteiros” existem em ambos os sentidos das páginas de dados, cada página tem referência para a página anterior (Last Page) e para a próxima página (Next Page).

Organização de uma Tabela:

A tabela contém uma ou mais partições e cada partição contém linhas de uma heap ou clustered index. As páginas de uma heap ou clustered index são gerenciadas em uma ou mais unidades de alocações (allocation unit) dependendo do tipo de dados. Como pode ser visto, podemos ter três diferentes tipos de unidade de alocação que gerenciam este armazenamento, como: Data, LOB e Row-Overflow.

Para verificar as unidades de alocações usadas pelo seu banco de dados, consulte a DMV: select* from sys.allocation_units

No meu caso eu estou mostrando quais as unidades de alocação que estão sendo utilizadas:

Partições:

Tabelas e páginas de índices são contidas em uma ou mais partições. Por padrão, uma tabela ou índice tem somente uma partição que contém toda a tabela ou as páginas de índice. Uma partição reside em apenas um único FileGroup. Quando temos uma tabela ou índice em múltiplas partições, os dados são particionados horizontalmente e um determinado grupo de linhas que é mapeado dentro de uma partição individual. Esse comportamento pode ser verificado em um artigo que falo sobre particiona mento: http://tcalencar.wordpress.com/2012/03/06/particionando-tabelas/. Para verificar as partições do seu banco de dados, consulte a DMV: select* from sys.partitions

E os níveis da B-Tree que você falou, onde estão? Vamos procurar a nossa resposta. O script cria uma tabela, adiciona um clustered index e realiza algumas inserções.

USE tempdb

GO

IF OBJECT_ID(‘dbo.TabelaFramentada ‘) IS NOT NULL

BEGIN

      DROP TABLE dbo.TabelaFramentada

END

 

CREATE TABLE dbo.TabelaFramentada (ID CHAR(800),

Nome CHAR(2100),

UltimoNome CHAR(2500),

Cidade CHAR(2200))

GO

 

CREATE CLUSTERED INDEX [IXNC_FragTable_ID] ON dbo.TabelaFramentada

(

[ID] ASC

) ON [PRIMARY]

GO

 

INSERT INTO dbo.TabelaFramentada (ID,Nome,UltimoNome,Cidade)

SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

‘Thiago’,

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN ‘Bianca’

ELSE ‘Andressa Martin’ END,

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN ‘Pinho’

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN ‘TT Maia’

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN ‘Luiz Henrique’

ELSE ‘Bruno Catapano’ END

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

Para verificar os níveis do índice, executaremos a seguinte query:

SELECT OBJECT_NAME(object_id)

,index_level

,record_count

FROM sys.dm_db_index_physical_stats(DB_ID(‘TempDb’),OBJECT_ID(‘dbo.TabelaFramentada’),NULL,NULL,’DETAILED’)

Comparando o resultado da query com o primeiro print deste post (Estrutura B-Tree) podemos afirmar que temos os seguintes níveis: Um nível folha, dois níveis intermediários e o Nível Root.

No próximo post iremos entender como o SQL Server armazena e lê os dados através de Clustered Indexes, NonClustered Indexes e  Heaps.

 

Referências:

http://blog.sqlauthority.com/2010/07/04/sql-server-index-levels-page-count-record-count-and-dmv-%C2%A0sys-dm_db_index_physical_stats/

http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

Books  On Line

Espero ter ajudado!

[] Thiago Carlos [TC] de Alencar