Blog – Fabiano Neves Amorim

O que você acha, Um Index Seek é melhor ou pior que um Index Scan?

A reposta é depende, existem casos onde com certeza fazer um Index Scan é a melhor opção. Vamos a um exemplo que criei.

— Script para criação da base de teste.

USE master

GO

IF DB_ID(‘INSIDE’) IS NULL

CREATE DATABASE INSIDE

GO

USE INSIDE

GO

ALTER DATABASE INSIDE SET AUTO_UPDATE_STATISTICS ON

GO

IF OBJECT_ID(‘TabTeste’) IS NOT NULL

DROP TABLE TabTeste

GO

CREATE TABLE TabTeste (ID INT IDENTITY(1,1) PRIMARY KEY,

Nome VarChar(200),

Nome2 VarChar(250) DEFAULT NEWID())

GO

Abaixo vamos alimentar a tabela TabTeste que criamos com o código acima, irei incluir 10000 mil registros com o valor A e apenas 10 com o valor B.

SET NOCOUNT ON

DECLARE @i Int

SET @i = 0

— Vamos inserir 10000 registros com o valor A

WHILE @i < 10000

BEGIN

INSERT INTO TabTeste (nome) VALUES(‘A’)

SET @i = @i + 1;

END

SET @i = 0;

— Agora vamos inserir apenas 10 registros com o valor B

WHILE @i < 10

BEGIN

INSERT INTO TabTeste (nome) VALUES(‘B’)

SET @i = @i + 1;

END

/*

Ao efetuar um select na tabela TabTeste o SQL só tem uma opção de acesso ao seus dados

fazer um Clustered Index Scan, ou seja, ler toda a sua tabela e verificar quais as linhas

onde o nome é igual a ‘A’ e então retornar somente estas linhas.

*/

SELECT * FROM TabTeste

WHERE Nome = ‘A’

GO

/*

Mas e se tivermos um indice na coluna Nome?

Neste caso o SQL terá 2 opções de acesso aos dados da tabela.

1º – Optar por ler os dados dados utilizando o indice gerando assim um Index Seek mais um

bookmark lookup já que estou querendo todas as colunas da tabela(*).

2º – Fazer o Clustered Index Scan.

*/

— Vamos criar o indice e ver como ele se comporta.

CREATE NONCLUSTERED INDEX ix_Nome ON TabTeste(Nome)

GO

/*

Um mesmo assim ele não usou o indice? Ok isso porque não vale a pena fazer o bookmark

para saber porque ele optou em não usar o indice leia o artigo "Indice / Lookup / Recompile"

http://fabianosqlserver.spaces.live.com/Blog/cns!52EFF7477E74CAA6!230.entry

Na consulta abaixo o SQL verificar quantas páginas serão necessárias

*/

SELECT * FROM TabTeste

WHERE Nome = ‘A’

GO

/*

Se eu fizer um select passando o valor ‘B’ o SQL irá validar quantas linhas meu select

irá retornar e verificar se vale a pena fazer o bookmark ou clustered scan para

retornar os dados da minha coluna Nome2.

*/

SELECT * FROM TabTeste

WHERE Nome = ‘B’

/*

Agora ele fez um Index Seek

Vamos entender o que aconteceu aqui:

O SQL foi nas estatisticas e verificou que seriam retornadas aproximadamente 10 linhas

na consulta portanto valia a pena fazer o bookmark para o indice cluster.

*/

DBCC SHOW_STATISTICS (TabTeste, ix_Nome)

/*

Mas e se as estatisticas estiverem desatualizadas o que vai acontecer?

Pois é, dai o SQL Server irá fazer pensar que vale a pena fazer um bookmark

pois serão retornados poucos dados mas isso não vai acontecer porque irei incluir

mais dados na tabela com o valor ‘B’. Vamos aos testes.

*/

— Vamos desabilitar o AUTO_UPDATE_STATISTICS para fazer as estatisticas

— ficarem desatualizadas

ALTER DATABASE INSIDE SET AUTO_UPDATE_STATISTICS OFF

GO

— Vamos inserir mais 1000 mil registros com o valor B

DECLARE @i Int

SET @i = 0

WHILE @i < 100

BEGIN

INSERT INTO TabTeste (nome) VALUES(‘B’)

SET @i = @i + 1;

END

GO

/*

O que acontece abaixo é que o SQL continua achando que o valor ‘B’ irá retornar apenas

10 linhas, vamos rodar o DBCC SHOW_STATISTICS para confirmarmos.

*/

DBCC SHOW_STATISTICS (TabTeste, ix_Nome)

/*

Nas consultas abaixo fiz um select igual ao anterior, e outro select forcando o uso

do clustered index scan repare que o número de IOs necessários para o select com o

Index Seek é BEM maior que o Clustered Scan.

*/

SET STATISTICS IO ON

SELECT * FROM TabTeste

WHERE Nome = ‘B’

— Table ‘TabTeste’. Scan count 1, logical reads 222, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

GO

SELECT * FROM TabTeste WITH(INDEX=0)

WHERE Nome = ‘B’

–Table ‘TabTeste’. Scan count 1, logical reads 73, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SET STATISTICS IO OFF

/*

Podemos terminar com 2 conclusões.

1º Nem sempre um Index Seek será melhor que um Index Scan.

2º Atualize as estatisticas de suas tabelas.

*/