Filtered Index in SQL Server 2008

Neste post vou falar de um recurso novo no SQL Server 2008 para melhorar o desempenho das consultas: Filtered Index. A partir do SQL Server 2008 pode-se acrescentar ao comando CREATE INDEX uma cláusula WHERE, filtrando linhas da tabela e reduzindo o tamanho do índice.

Para mostrar este recurso utilizei como exemplo a tabela “Production.TransactionHistory” do banco de dados AdventureWorks2008. A consulta abaixo utiliza um “Cluster Index Scan” (leia-se “Table Scan”, pois no nível folha do índice cluster temos a própria tabela) como plano de execução.

set statistics profile on
set statistics io on

select BusinessEntityID,FirstName,MiddleName,LastName,ModifiedDate
from Person.Person where PersonType = 'VC'

-- Cluster Index Scan
-- Table 'Person'. Scan count 1, logical reads 3816O índice ideal para atender a consulta acima seria um Nonclustered Index com a chave na coluna PersonType e Include nas demais colunas do SELECT, veja:

create index IX_Person_PersonType on Person.Person(PersonType)
include (FirstName,MiddleName,LastName,ModifiedDate)
Se executarmos a consulta acima novamente o SQL Server fará Index Seek, reduzindo consideravelmente o I/O.

-- Index Seek (IX_Person_PersonType)
-- Table 'Person'. Scan count 1, logical reads 4
A partir do SQL Server 2008 pode-se criar índices menores, especializados na cláusula WHERE de uma consulta, gerando redução do espaço ocupado. Basta acrescentar ao final de um CREATE INDEX uma cláusula WHERE:

create index IX_Person_PersonType_Filter on Person.Person(PersonType)
include (FirstName,MiddleName,LastName,ModifiedDate)
where PersonType = 'VC'
Se executarmos a consulta do início do artigo novamente o SQL Server irá gerar o mesmo plano de execução, porém utilizando o novo índice:

-- Index Seek (IX_Person_PersonType_Filter)
-- Table 'Person'. Scan count 1, logical reads 4
Comparando os dois índices com a instrução abaixo, podemos observar a redução no tamanho do índice de 137 para 4 páginas de 8k.

select i.name Indice,used_page_count,row_count
FROM sys.dm_db_partition_stats P INNER JOIN sys.indexes I
ON I.index_id = P.index_id AND I.OBJECT_ID = P.OBJECT_ID
WHERE p.OBJECT_ID = OBJECT_ID('Person.Person')
Até o próximo post,
Landry.