Skip to content

Indices filtrados en SQL Server

01/10/2012

  Si estamos en proceso de optimizar una consulta o procedimiento en SQL Server, una de las herramientas que siempre debemos considerar es el uso de índices. Un índice bien implementado puede mejorar el tiempo de ejecución de una consulta de horas a segundos. También ayuda a minimizar el uso de recursos durante la ejecución y a evitar la aparición de bloqueos. Lamentablemente el uso de índices no es recomendable para todos los casos posibles y debemos analizar cuidadosamente los datos de las tablas antes de determinar si es viable indexarlas de la manera requerida.

Afortunadamente con SQL Server tenemos una nueva característica que nos permite el uso de índices eficientes y de un tamaño muy reducido. Se trata de los índices filtrados (filtered indexes).

La idea detrás de los índices filtrados es simple. Implementar un índice tradicional, pero no sobre una tabla completa, sino sobre la aplicación de un filtro a esta tabla. Esto nos permite aplicar índices en casos donde antes no era conveniente hacerlo. Pongamos un caso concreto. Tenemos una tabla de clientes, con unos 500.000 registros. Uno de los campos de esa tabla corresponde al país de establecimiento del cliente. Supongamos además que en esta tabla 495.000 clientes son de Argentina, mientras que el resto se distribuye entre otros varios países. Si analizamos la selectividad de un posible índice sobre el campo país, vemos que el mismo es muy poco selectivo para el valor ‘Argentina’ y muy selectivo para cualquier otro valor. Este caso plantea un problema para los índices tradicionales, ya que tendríamos un índice relativamente grande y con una selectividad baja en la mayoría de los casos. Pero supongamos además que tenemos una nueva consulta a optimizar donde se seleccionan los clientes que no están en Argentina. Para esta consulta el índice sería conveniente, pero por su tamaño y costo de mantenimiento descartaríamos esa opción.

Aparece entonces la idea de los índices filtrados, pudiendo generar un índice sobre la tabla de clientes, con la condición (filtro) que Pais<>’Argentina’. Este índice sería mucho más pequeño ya que contendría solo 5.000 registros y además sería altamente selectivo para cualquiera de sus valores. El caso ideal!
Veamos entonces como implementar este tipo de índices con un ejemplo de tablas de la base de datos AdventureWorks y analicemos su rendimiento.

Primero generaremos un índice filtrado, mediante la siguiente instrucción TSQL:

–Creación de un índice filtrado
CREATE NONCLUSTERED INDEX [FilteredIndex] ON [Sales].[Customer]
(
[TerritoryID] ASC
)
WHERE ([TerritoryID]=(2))
ON [PRIMARY]
GO

Ya con el índice creado, vemos los planes de ejecución de 2 consultas, una de ellas con una condición compatible con el índice filtrado y otra con una condición distinta y comparando el uso del índice filtrado y de un índice ya existente sobre toda la tabla, por el mismo campo TerritoryID:

–Consulta con una condición distinta a la del índice filtrado
SELECT CustomerID
FROM sales.Customer
WHERE TerritoryID<>2

–Consulta con la misma condición distinta que el índice filtrado
SELECT CustomerID
FROM sales.Customer
WHERE TerritoryID=2

Como podemos apreciar, en la consulta dónde se realiza un filtro por TerritoryID=2 (la misma condición del índice filtrado) la operación que elige el motor es la de una búsqueda sobre el índice filtrado (index seek).

En la segunda consulta sin embargo, al filtrar por una condición incompatible con el índice filtrado, se hace una búsqueda sobre el índice que contiene la tabla completa.
Pero además de estos distintos planes de ejecución, es importante considerar el tamaño de nuestro nuevo índice filtrado, en comparación con un índice sobre el mismo campo pero conteniendo todos los datos de la tabla. Lo podemos apreciar aquí:

Las diferencias son notorias en cuanto al tamaño, donde el índice de la clave primaria tiene 35 páginas y el índice filtrado tiene 1 página. Pero más importante aún, podemos observar que la cantidad de niveles del índice sobre la tabla completa es 2, mientras que el índice filtrado posee un solo nivel. Esto mejora muchísimo las operaciones de lectura puntuales sobre el índice filtrado (index seek)

Los índices filtrados son una excelente herramienta de optimización y nos pueden ayudar aún en casos en donde tradicionalmente no se aplicaría la optimización por índices. Por sus características y definición presentan un tamaño reducido y una cantidad de niveles inferior a los índices generados sobre toda una tabla. Cabe mencionar que esta funcionalidad está disponible para las versiones de SQL Server desde la 2008 en adelante.

From → Microsoft, SQL Server

One Comment

Trackbacks & Pingbacks

  1. Indices filtrados en SQL Server « MSExpertos

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: