En SQL Server los índices filtrados son un tipo optimizado de índices non-clustered que permite el uso de un filtro para mejorar el rendimiento de las consultas en una base de datos. Si bien su sintaxis no dista mucho (casi nada) de un índice tradicional, este tipo de índice no es muy sonado y a veces se desconoce de su existencia y su potencial valor.

En este post, te mostraré un caso de uso práctico de un índice filtrado, analizaremos el espacio ocupado por el mismo y compararemos los planes de ejecución de una consulta que usa un índice filtrado con uno que no.

Escenario: Tenemos una aplicación que trabaja con la base de datos AdventureWorks2016 y con la tabla BillOfMaterials, que luce así:

Las consultas a esta aplicación usualmente involucran filtrar los registros cuyo EndDate sea NOT NULL, por ejemplo:

SELECT ProductAssemblyID, ComponentID, StartDate   
FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL
AND ComponentID = 5
AND StartDate > '01/01/2008' ;  
GO  

Al ejecutar esta consulta, el optimizador nos muestra el siguiente plan de ejecución:

Para obtener el resultado de esta consulta, SQL optó por usar el índice clúster de la tabla (AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate) y el índice nonclustered ‘BillOfMaterialsWithEndDate’. Malabares con un Index Seek, un Key Lookup, seguido de un Nested Loop. No es un plan tan terrible que digamos (SQL está haciendo lo mejor que puede con lo que tiene), pero.. podemos mejorarlo?

Creando un índice filtrado

Si las consultas a la base de datos requieren frecuentemente obtener registros cuyo EndDate sea NULL, entonces podemos simplemente agregar un índice que filtre sólo esos valores, por ejemplo:

CREATE NONCLUSTERED INDEX Filtered_BillOfMaterialsWithEndDate
ON Production.BillOfMaterials (ComponentID, StartDate)
WHERE EndDate IS NOT NULL
GO

Así de simple acabamos de crear un índice filtrado. Nota que hemos usado un WHERE en la creación del índice, esto nos permite ser más selectivos y sólo incluir en el índice los registros que cumplen con la condición dada.

Ahora, si comparamos los tamaños de ambos índices, a primera vista nos daremos cuenta que el índice filtrado pesa mucho menos, esto es porque al aplicársele un filtro, este almacena menos data (Imagen 3):

En este caso, los índices escogidos son muy pequeños como para hablar de un súper ahorro en almacenamiento, pero a gran escala esto puede representar ahorros mucho mayores. Debes tener en cuenta que la cantidad de ahorro de espacio dependerá de que tan selectivo sea tu filtro.

Por último, si volvemos a ejecutar la consulta veremos que el optimizador escogió el índice filtrado, lo que resulta en un plan más eficiente que realiza menos operaciones e involucra sólo un index seek:

A partir de este sencillo ejemplo hemos podido demostrar que la creación de un índice filtrado bien aplicado puede traer beneficios, tanto en el aspecto de rendimiento como en el de almacenamiento.

Si tienes alguna duda, comentarios o sugerencias, me puedes enviar un correo a bitácoradeundba@gmail.com.