Introdução

• 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.

Demonstrações

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

FROM AdventureWorks.Production.Product

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)

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

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

 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.

 Mais somente por curiosidade, consultando as dm’v’s sys.indexes, sys.partitions esys.system_internals_allocation_units, vemos um significante ganho de páginas no índiceFIidxNCL_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!!!!