Filtered Index SQL Server

Luan Moreno

Maio, 2014

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

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

| Home | Artigos Técnicos | Comunidade