Recentemente o Luciano Moreira publicou um post no seu blog perguntando porque as vezes o SQL Server não usa um determinado índice, aproveitando a deixa resolvi escrever um pouco sobre isso.

O link para o post do Luciano é este Exibir artigo...

DROP TABLE TMP

CREATE TABLE TMP(ID INT IDENTITY(1,1) PRIMARY KEY, NOME VARCHAR(200), Nome2 VarChar(200))

CREATE INDEX ix_Teste on TMP(Nome)

SET NOCOUNT ON

DECLARE @I INT

SET @I = 0

WHILE @I < 1000

BEGIN

  INSERT INTO TMP(NOME, Nome2) VALUES('A', NewID())

  INSERT INTO TMP(NOME, Nome2) VALUES('B', NewID())

  INSERT INTO TMP(NOME, Nome2) VALUES('C', NewID())

  INSERT INTO TMP(NOME, Nome2) VALUES('D', NewID())

  INSERT INTO TMP(NOME, Nome2) VALUES('E', NewID())

  SET @I = @I + 1;

END

-- Foram incluidos 5000 registros na tabela TMP

SELECT COUNT(*) FROM TMP

/*

  Pressione CTRL-M para incluir o plano de execução no resultado da consulta e execute o comando abaixo.

  O Select irá selecionar 1/5 dela

*/

SELECT * FROM TMP

WHERE Nome = 'A'

/*

  Repare que o SQL Server não utilizou o indice pela coluna Nome e achou

  melhor fez um clustered scan, Vamos analisar melhor este comportamento.

  Primeiramente vamos ver quantas páginas o SQL Server terá que ler para

  retornar os dados da consulta, para isso vamos ligar o statistics io e rodar

  o select novamente.

*/

SET STATISTICS IO ON

SELECT * FROM TMP

WHERE Nome = 'A'

/*

Table 'TMP'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Podemos observar que o SQL leu 37 páginas de dados para retornar o resultado.

Agora vamos forçar o uso do indice por nome para fazer o Seek e não o Clustered Scan,

repare no plano de execução que o SQL fez um Seek e um bookmark para

pegar o valor da coluna Nome2 que não faz parte do indice nonclustered ix_teste.

No indice cluster(no nosso exemplo é o campo ID que é a primary key) temos as informações de todos

os dados da tabela, portanto sempre que uma consulta seleciona campos a mais do que os cobertos pelo

indice nonclustered o SQL irá no indice cluster ler esta informação.

No nosso exemplo abaixo isso aconteceu porque a coluna Nome2 não pertence ao indice nonclustered,

portanto o SQL fez o lookup para ler esta informação no indice cluster.

*/

SELECT * FROM TMP WITH(INDEX = ix_teste)

WHERE Nome = 'A'

/*

Table 'TMP'. Scan count 1, logical reads 2076, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Podemos observar que desta vez o SQL leu 2076 páginas de dados para retornar o resultado.

Com isso podemos concluir que o Lookup é MUITO "caro" para sua consulta e o

SQL só irá optar fazer o lookup se a quantidade de linhas que será retornada for

aproximadamente menor que 0,1% do total de sua tabela.

Uma solução para forçar o uso do indice sem perder tanto em performance

seria incluir a coluna Nome2 no indice noncluster,

No SQL 2000

CREATE INDEX ix_Teste on TMP(Nome, Nome2)

No SQL 2005 poderiamos utilizar a clausula INCLUDE

CREATE INDEX ix_Teste on TMP(Nome) INCLUDE(Nome2)

*/

/*

Vamos incluir uma linha na tabela TMP com o valor X e efetuar

o select para retonar apenas o valor X e ver o plano de execução.

*/

INSERT INTO TMP(NOME, Nome2) VALUES('X', NewID())

SELECT * FROM TMP

WHERE Nome = 'X'

GO

SELECT * FROM TMP

WHERE Nome = 'A'

GO

/*

Repare que no primeiro select o SQL usou o Indice e no

segundo não usou porque irá retornar muitas linhas

*/

/*

Um outro comportamento interessante é quando o valor do filtro é passado

por uma variável, vimos no select acima que o SQL usou o indice para

o filtro nome = 'X', vamos passar o valor 'X' para uma variável

e utiliza-la no where.

*/

DECLARE @var VarChar(200)

SET @var = 'X'

SELECT * FROM TMP

WHERE Nome = @var

/*

Desta vez o SQL não usou o indice e fez o Scan mesmo a consulta retornando

apenas 1 registro, isso acontece porque o SQL não sabe o valor de @var

por este motivo ele opta por fazer o Scan porque ele não sabe se @var irá retornar

1 linha ou 5000.

Existem várias soluções para este problema, a idéia a fazer o SQL compilar

o código com o valor de @var.

Você poderia utilizar a SP_EXECUTESQL

Ou então criar uma procedure e executar a consulta na procedure

Ou então utilizar o Hint RECOMPILE

Veja um exemplo do uso das 3 opções

*/

-- SP_EXECUTESQL

DECLARE @SQL NVARCHAR(200)

SET @SQL = 'SELECT * FROM TMP WHERE Nome = @Var'

EXEC SP_EXECUTESQL @SQL, N'@Var VarChar(200)', @Var = 'X'

-- Procedure

-- Criei a proc utilizando a clausula WITH RECOMPILE

-- mas esse assunto fica pra uma 2 Parte do post

CREATE PROC st_Teste @Var VarChar(200)

WITH RECOMPILE AS

SELECT * FROM TMP

WHERE Nome = @var

GO

EXEC st_Teste @Var = 'A'

-- HINT - RECOMPILE

DECLARE @var VarChar(200)

SET @var = 'X'

SELECT * FROM TMP

WHERE Nome = @var

OPTION(RECOMPILE)

_________________________________________________________________
Fabiano Neves Amorim
Análise - NewCon Enterprise
* fabiano@cnpm.com.br - http://fabianosqlserver.spaces.live.com/