Blog – Fabiano Neves Amorim

Para quem não leu o 1º Post, segue o link.

Quiz – Porque o SQL não utiliza meu indice?

É isso ai pessoal, vamos entender melhor porque o SQL não pode confiar no índice para retornar nossas informações.

Para entender melhor este caso, temos que pensar como se fossemos o Otimizador de consultas.

Seguindo nessa lógica, vamos lá.

Lembrando que o papel do Otimizador é de calcular qual é a maneira mais rápida de retornar os dados de uma determinada consulta, baseado nas informações que ele tem em relação ao schema da tabela, estatísticas, e recursos de hardware disponíveis no momento da execução.

Bom, no caso de nossa tabela, o Otimizador sabe que ele tem apenas 2 maneiras de acessar os dados, são elas: Utilizar o Índice Cluster, ou utilizar o Índice NonCluster. Os dois índices tem todos os dados necessários para retornar a consulta gravados em suas páginas.

O ponto chave aqui é, qual é o melhor índice o Cluster que esta ordenado por ID, ou o NonCluster que esta ordenado por Nome1 e Valor1.

De primeira vista, podemos olhar o nonclustered e dizer que ele sem dúvida é a melhor maneira de acessar os dados, já que ele está ordenado por Nome1 e Valor1, que são exatamente as colunas utilizadas na clausula WHERE da consulta. Porém, existe um fator determinante para que esta escolha não seja viável, a clausula ORDER BY que está solicitando que os dados sejam retornados pela coluna ID.

Bom mas você pode estar pensando, ora, a coluna ID pertence ao índice cluster, então porque não pegar ela e retornar os dados? A resposta é: – Porque o SQL não pode confiar que os dados estarão na ordem correta. E neste caso ele teria ler os dados do índice e depois fazer um SORT pela coluna ID. Isso pode ser comprovado caso nós forcemos o uso do índice. Vamos ver o como ficaria o plano de execução.

SELECT ID

  FROM TabTeste WITH(INDEX = ix_TesteSem_Include)

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

 ORDER BY ID

Como podemos observar, ao forçar o uso do Índice o SQL gera um operador de SORT onde este processo representa 82% de custo, quase que o total da consulta.

Ok, entendido que o SQL não pode usar o índice noncluster, porque neste caso ele teria que ordenar o resultado por ID, mas, porque ele não pode confiar no índice? Os dados não estão no Índice?

O que acontece é que o índice está ordenado por Nome1,Valor1 e ID, e fazendo esta leitura o SQL não pode confiar que os registros retornados estarão na ordem correta. Perai, ficou meio confuso né? Vamos fazer o seguinte, vamos ver na prática, acho que vai ficar mais fácil de entendermos.

Vamos ver como o índice está ordenado fisicamente nas páginas de dados.

Primeiro temos que pegar o valor Hexadecimal que diz qual é a primeira página de dados do índice.

select * from sysindexes

where id = Object_id(‘TabTeste’)

O valor da coluna root contem o ponteiro para a primeira página raiz do índice.

Vamos converter o valor Hexadecimal para um Inteiro. Basta reajustar a ordem dos valores e usar a função CAST do próprio SQL.

select CAST(0x040D AS INT)

Ok, então sabemos que a página raiz do índice Nonclustered é o 1037, vamos ver como os dados estão armazenados nesta página? Para isso vamos usar o DBCC PAGE. Os parâmetros passados são,

2 = ID do banco de dados, como estou no TempDB o valor será sempre 2. Caso você esteja logado em outro banco pode pegar usando select DB_ID(DB_name())

1 = Arquivo físico onde a página está armazenada, no Hexadecimal os 4 últimos valores irão dizer qual é o arquivo, no nosso exemplo foi retornado 0100 fazendo a reorganização dos valores teríamos 0001, portanto 1.

1037 = Página onde os dados estão localizados

3 = Forma de visualização dos dados da página. Pode ser 1, 2 ou 3. Como quero ver os dados em uma grade usei o 3.

DBCC TRACEON(3604)

GO

DBCC PAGE(2,1,1037,3)

Ok estes são os dados da página nível raiz, vamos descer os níveis, para ver os dados… só lembrando… A arquitetura do índice é…

Na coluna ChildPageID temos qual é a página filho do valor atual, vamos analisar a primeira página de dados que é a  PageID 1034. Nesta página teremos os dados do índice, e poderemos aplicar o filtro do where.

DBCC PAGE(2,1,1034,3)

.

.

.

Repare que temos o valores dos dados gravados no índice, e eles estão corretamente ordenados por Nome1, Valor1, e ID que é a chave do índice Cluster. Se você continuar a ler os valores das páginas 1036 e 1038 verá que a tabela inteira estará armazenada e ordenada conforme requerimento de nossa consulta(Nome1 e Valor1 para o filtro, e ID para ordenação).

Vamos ver o final do índice, que é a página 1038.

DBCC PAGE(2,1,1038,3)

Sendo assim podemos facilmente visualizar que para a consulta abaixo o SQL poderia simplesmente ler os dados na ordem em que os valores estão sendo apresentados, que ele iria retornar tudo certo, inclusive os dados já estão ordenados por ID. Na teoria, não precisa do SORT, gerado quando forçamos o uso do índice.

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

 ORDER BY ID

Porque então não fazer isso? Votando a pensar como o Otimizador, me responda uma coisa, baseado nas informações que você tem, schema, estatísticas.. Você realmente pode confirmar que SEMPRE os dados estarão conforme nossa visualização? Sim? Talvez? Não sei? …

Caso o SQL pense que o índice já está ordenado corretamente e não gere o SORT, O que aconteceria se eu por ex, incluísse uma linha com os seguintes  valores?

SET IDENTITY_INSERT TabTeste ON

INSERT INTO TabTeste(ID, Nome1, Valor1) VALUES(-1, ‘aaaa’, 1)

SET IDENTITY_INSERT TabTeste OFF

Para ver o dano, vamos rodar a consulta mas sem o ORDER BY, apenas para tentar forçar que o SQL use o Índice, ou seja, você quer trazer os dados pela ordem em que eles estão armazenados no índice,

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

Como podemos observar os dados não foram retornados na ordem esperada, ou seja, ordenados por ID, se o SQL ler os dados do índice ele terá que gerar um sort para reordenar os valores por ID.

Vamos ver como os dados ficaram na última página do índice.

DBCC PAGE(2,1,1038,3)

Podemos ver que, a tabela é ordenada Nome1 e Valor1, como o Valor1 que foi inserido é “1” então ele deve vir depois do ultimo “0”, mas o ID “-1” é menor que “1000”. Seguindo a ordem, a regra é primeiro por Nome1 e depois por Valor1, e não por Nome1 e depois por ID.

Fiz essa inclusão para mostrar que o SORT é necessário já que no índice nonclustered a coluna ID não está ordenada conforme desejamos.

Se alterarmos a consulta para trazer apenas um Nome1 e apenas 1 Valor1, então o índice é utilizado, e não é mais necessário o SORT, pois o Otimizador já sabe que ele pode confiar que para os valores nome1 = ‘aaaa’ e valor1 = 0, os registros estarão ordenados corretamente por ID.

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 = 0

 ORDER BY ID

Com isso podemos aprender que o Otimizador pode optar por, evitar fazer um SORT mesmo que isso cause uma não utilização de um índice. Operações de SORT acontecem no TEMPDB e são extremamente pesadas.

Ao criar um índice utilizando a opção Include, o SQL Server grava os valores do INCLUDE depois das chaves do índice, ou seja, trocamos a ordem de Nome1, Valor1, ID por Nome1, ID, Valor. Fazendo isso o SQL pode ter certeza de que os valores estarão ordenados por ID, já que o filtro no Nome1 é uma constante ou seja, é um valor único.

CREATE NONCLUSTERED INDEX ix_Teste_Include ON TabTeste(Nome1) INCLUDE(Valor1)

GO

SELECT ID

  FROM TabTeste

 WHERE Nome1 = ‘aaaa’

   AND Valor1 <= 10

 ORDER BY ID

Repare que para a consulta que utiliza o índice com Include, o valor passado como SeekPredicate para o Índice foi o Nome1 = ‘aaaa’, já o Valor1 <= 10 é passado como Predicate, ou seja, ele não será utilizado para fazer o seek no índice. Depois eu o seek por Nome1 for feito, será aplicado um filtro neste resultado para que apenas as linhas onde Valor1 <= 10 sejam retornadas.

O Gustavo Maia fez uma analise bem interessante em relação a estes testes, segue o link para o doc que ele escreveu.

http://cid-52eff7477e74caa6.skydrive.live.com/self.aspx/Publica/Gustavo%20Maia%20-%20Desafio.doc

Bom pessoal, semana que vem estou de férias, portanto que Deus Abençoe a todos e tenham um Feliz Natal.

Abraço.