Porque usar Filtered Indexes?

Posted on março 11, 2012

4


Bem, o filtered index é um índice não cluster otimizado, isso porque ele apresenta filtros e predicados que fazem com que os dados sejam mais bem “selecionados” durante a criação de um índice.

 

Sendo assim podemos ter melhoras significamantes em:

 

* Qualidade de uma plano de execução

* Aumento na performance de uma pequisa

* Redução de tempo e manutenção de índices

* Redução de espaço em disco

 

Para que seja criado índices efetivos, temos alguns cenários nos quais ele se encaixa com perfeição, fazendo assim com que tenhamos grandes ganhos de performance.

 

Algumas situações das quais seria interessante a criação desse índice:

 

* Colunas que possuam valores NULL

* Dados heterogêneos

* Range de valores

 

Usando assim nosso famoso banco de dados do SQL Server AdventureWorks, irei mostrar como os filtered indexes podem nos ajudar no dia a dia de uma DBA.

 

Demo:

 

SELECT ProductID, Name, Color, StandardCost, ListPrice, Size, Class, SellStartDate, SellEndDate

FROM AdventureWorks.Production.Product

 

 

image

 

 

image

 

Supondo que nossa consulta irá se basear nos campos SellEndaDate, Name e SellStartDate  e que só seria necessário as informações do campo SellEndDate não nulas poderiamos criar o seguinte índice

 

CREATE NONCLUSTERED INDEX idxNCL_Product_SellEndDate

ON Production.Product (Name, SellStartDate, SellEndDate)

 

image

 

Vemos claramente que a quantidade de registros nulos são maiores do que os  não nulos fazendo assim com que os não nulos possua uma maior seletividade, Sendo assim poderiamos ganhar criando um índice no qual teria somente os valores importantes para nosso negócio.

 

 

CREATE NONCLUSTERED INDEX FIidxNCL_Product_SellEndDate

ON Production.Product (Name, SellStartDate)

WHERE SellEndDate IS NOT NULL

 

image

 

Agora podemos comparar o custo efetivos das duas consultas, assim temos:

 

SELECT P.ProductID, P.Name, P.SellStartDate

FROM Production.Product AS P WITH(INDEX(idxNCL_Product_SellEndDate))

WHERE P.SellEndDate IS NOT NULL

 

SELECT P.ProductID, P.Name, P.SellStartDate

FROM Production.Product AS P WITH(INDEX(FIidxNCL_Product_SellEndDate))

WHERE P.SellEndDate IS NOT NULL

 

image 

 

Concluímos que o Filtered Index neste caso é muito mais eficiente, performático e efetivo.

Claro que cada caso é uma caso, mais o que quero mostrar é que esse recurso é excelente e pode nos otimizar um bom tempo durante a manutenção de índices.

 

Adicionando…..

Obs: Como dito pelo nosso MVP de SQL Server Luti, o ganho nesta consulta não seria tão grande, ganhariamos mesmo em espaço e manutenção e concordo com isso.

Mais somente por curiosidade, consultando as dm’v’s sys.indexes, sys.partitions e sys.system_internals_allocation_units, vemos um significante ganho de páginas no índice FIidxNCL_Product_SellEndDate, fazendo com que esse índice possua somente 98 registros.

 

WITH ProductionProductIndexes AS

(

SELECT object_id, name, index_id, type_desc, filter_definition

FROM sys.indexes AS SI

WHERE SI.object_id = object_id(‘Production.Product’)

)

  , ProductionProductDados AS

(

SELECT *

FROM sys.system_internals_allocation_units AS AU

INNER JOIN SYS.Partitions AS P

ON AU.Container_id = P.Partition_id

WHERE P.Object_ID = object_id(‘Production.Product’)

)

SELECT A.object_id, allocation_unit_id, A.index_id, name, A.type_desc, filter_definition, total_pages,

        used_pages, data_pages, first_page, root_page, first_iam_page, rows

FROM ProductionProductIndexes AS A

INNER JOIN ProductionProductDados AS PD

ON A.index_id = PD.index_id

 

Dado a proporção, o índice filtrado possui 80,5% a menos de dados do que o índice sem filtro, legal não!!!!

 

image