Índices no SQL Server - parte 2

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.