Como os �ndices no SQL Server s�o organizados no disco

#	COMANDO
01	SELECT * FROM PESSOA
02	SELECT * FROM PESSOA WHERE COD = 1
03	SELECT NOME FROM PESSOA
04	SELECT NOME FROM PESSOA WHERE NOME = �AGNALDO�
05	SELECT COD, NOME FROM PESSOA WHERE NOME = �AGNALDO�
06	SELECT * FROM PESSOA WHERE NOME = �AGNALDO�

COMANDO	SEM �NDICE	NON-CLUSTERED	CLUSTERED e NON-CLUSTERED
01	TABLE SCAN	TABLE SCAN	CLUSTERED INDEX SCAN
02	TABLE SCAN	TABLE SCAN	CLUSTERED INDEX SEEK
03	TABLE SCAN	INDEX SCAN	INDEX SCAN
04	TABLE SCAN	INDEX SEEK	INDEX SEEK
05	TABLE SCAN	TABLE SCAN	INDEX SEEK
06	TABLE SCAN	TABLE SCAN	CLUSTERED INDEX SCAN

COMANDO	SEM �NDICE	NON-CLUSTERED	CLUSTERED e NON-CLUSTERED
01	TABLE SCAN	TABLE SCAN	CLUSTERED INDEX SCAN
02	TABLE SCAN	TABLE SCAN	CLUSTERED INDEX SEEK
03	TABLE SCAN	INDEX SCAN	INDEX SCAN
04	TABLE SCAN	INDEX SEEK	INDEX SEEK
05	TABLE SCAN	TABLE SCAN	INDEX SEEK
06	TABLE SCAN	TABLE SCAN	CLUSTERED INDEX SCAN

Nesse ponto, temos que sabe o que � a operaç�o:

SCAN: busca em TODOS os elementos da estrutura (que pode ser uma tabela ou um �ndice);
SEEK: busca bin�ria nos elementos de um �ndice.

Devemos atentar para a seguinte observaç�o: SCAN � usado quando a tabela n�o possui �ndices que atendam ao select ou quando a quantidade de registros que a query retorna (em percentual) � grande. O SEEK � usado quando existe um �ndice que � adequado e a quantidade de registros retornados � pequena, percentualmente falando.

As operaç�es executadas nas tabelas/�ndices foram:

TABLE SCAN: Busca em todos os elementos da tabela, de forma seq�encial;
INDEX SCAN: Busca em todos os elementos de um �ndice non-clustered, de forma seq�encial;
CLUSTERED INDEX SCAN: Busca em todos os elementos de um �ndice clustered, de forma seq�encial;
INDEX SEEK: Busca bin�ria num �ndice non-clustered;
CLUSTERED INDEX SEEK: Busca bin�ria num �ndice non-clustered.

Agora, por que o SQL Server criou planos de execuç�o t�o diferentes? Porque ele �sabe� (usando as estat�sticas - assunto que abordarei em outro post) qual tipo de acesso tem maior probabilidade de retornar os dados pedidos no menor tempo poss�vel.

E como o SQL Server escreve os �ndices no disco? Vamos l�.

Para essa explicaç�o, vamos dropar o database que est�vamos usando e recri�-lo, junto com a tabela pessoa. Ap�s isso, vamos inserir as 8 pessoas.

Lembro que, para dropar um banco, usamos os comandos:


USE MASTER
DROP DATABASE EXEMPLO_INDICE

Ap�s a criaç�o da tabela, fazemos um select na tabela indexes do schema sys (que vou chamar, a partir de agora, de sys.indexes):


SELECT *
FROM SYS.INDEXES
WHERE OBJECT_ID = OBJECT_ID('PESSOA')

Vemos que existe uma linha na sys.indexes, mesmo PESSOA n�o tendo �ndice. Na realidade, sys.indexes armazena dados dos �ndices (quando existem) e das tabelas. Sabemos que a tabela n�o possui �ndice clustered porque seu type_desc � HEAP.


DECLARE @DB_ID INT,
      @OBJECT_ID INT
SELECT @DB_ID = DB_ID(�EXEMPLO_INDICE�),
      @OBJECT_ID = OBJECT_ID(�PESSOA�)

DBCC IND(@DB_ID, @OBJECT_ID, -1)

Onde:

PagePID: � o n�mero da p�gina de dados onde a informaç�o est� escrita;
IndexID: � o tipo de estrutura:
- 0 - p�gina de dados;
- 1 - �ndice clustered;
- 2 a 255 - �ndices non-clustered;
PageType: � o tipo do dado armazenado:
- 1 - p�gina de dados;
- 2 - p�gina de �ndice;
- 10 - mapa de alocaç�o de �ndices;
Executando o comando abaixo e fornecendo o valor de PagePID obtido com o script acima:

DECLARE @DB_ID INT SELECT @DB_ID = DB_ID(�EXEMPLO_INDICE�) DBCC TRACEON(3604) DBCC PAGE(@DB_ID, 1, 152, 3) DBCC TRACEOFF(3604)

Temos a descriç�o do conte�do das p�gina em quest�o.

Agora, recriamos o �ndice non-clustered, usando o mesmo comando do post anterior. Em seguida fazemos o select na sys.objects que, nesse momento, nos mostra a exist�ncia do �ndice non-clustered.

Executando o DBCC IND (acima) vemos que foram alocadas p�ginas para o �ndice.

Com o DBCC PAGE (acima) vemos o conte�do da p�gina - temos que substituir o valor 152 (p�gina de dados da tabela) por 154 (p�gina que armazena os dados do �ndice).

Vemos, na figura acima, que existe uma coluna (HEAP RID) que aponta para o endereço do registro na p�gina de dados.

Recriamos o �ndice clustered. Em seguida executamos os mesmos comandos mostrados anteriormente (select na sys.indexes, DBCC IND e DBCC PAGE para o �ndice non-clustered, trocando o n�mero da p�gina, claro - no meu servidor, 158).

Note que o type_desc do select mudou de HEAP para CLUSTERED.

A coluna que aponta para endereço do registro n�o � mais o HEAP RID e sim COD, que � a chave do �ndice clustered. Por isso que uma consulta por cod, nome (o select n�mero 5) na tabela pessoa faz um table scan quando a tabela somente tem o �ndice non-clustered e index seek quando a tabela tem �ndices clustered e non-clustered.

�ndices no SQL Server - parte 2

Já explicamos o que são os índices. Foi uma idéia geral. Mostramos, ainda, um plano de execução simples. Fizemos alguns SELECTs que posso agregar nas tabelas a seguir.

E como o SQL Server escreve os �ndices no disco? Vamos l�.

Artigos relacionados