Índices filtrados de SQL Server para colas y datos activos

Una tabla de trabajo puede guardar millones de registros completados mientras los procesos buscan unos pocos cientos de pendientes. Un índice completo ayuda, pero sigue almacenando toda la historia. Un índice filtrado representa directamente el subconjunto que necesita la consulta activa.

La cantidad importante es el conjunto activo. Cincuenta millones de filas con 200 pendientes no se comportan como veinte millones de pendientes después de una interrupción. Diseña para operación normal y recuperación. El comportamiento de una cola casi vacía no permite predecir el plan cuando el retraso ocupa gran parte de la tabla.

Ajustar el predicado al negocio

En el ejemplo, cero significa pendiente y uno completado. El índice contiene solo pendientes, ordenados por creación e identificador. CustomerId está incluido porque la respuesta lo necesita, pero no se usa para buscar ni ordenar. El resultado esperado son los registros 2 y 3. La muestra comprueba la lógica; no sirve como referencia de rendimiento.

CREATE TABLE #Work
(
    WorkId bigint NOT NULL PRIMARY KEY,
    Status tinyint NOT NULL,
    CreatedAt datetime2(0) NOT NULL,
    CustomerId int NOT NULL
);
INSERT #Work VALUES
(1,1,'2024-01-01',10),(2,0,'2024-01-02',20),
(3,0,'2024-01-03',10),(4,1,'2024-01-04',30);

CREATE INDEX IX_Work_Pending
ON #Work(CreatedAt, WorkId)
INCLUDE(CustomerId)
WHERE Status = 0;

SELECT TOP (20) WorkId, CreatedAt, CustomerId
FROM #Work
WHERE Status = 0
ORDER BY CreatedAt, WorkId;

SELECT i.name, p.row_count, p.used_page_count
FROM tempdb.sys.indexes AS i
JOIN tempdb.sys.dm_db_partition_stats AS p
  ON p.object_id=i.object_id AND p.index_id=i.index_id
WHERE i.object_id=OBJECT_ID('tempdb..#Work');

DROP TABLE #Work;

Con datos reales, compara páginas de índice, lecturas lógicas y filas leídas para profundidades normales y máximas. Indexar únicamente el estado puede dejar una ordenación o muchos accesos adicionales a la tabla. Coloca las columnas de ordenación en la clave y limita las incluidas. Un documento JSON grande normalmente puede recuperarse después de seleccionar un conjunto pequeño de identificadores.

Las opciones SET también importan. Comprueba ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL y ARITHABORT activadas, con NUMERIC_ROUNDABORT desactivada. Si las escrituras fallan desde la aplicación aunque funcionen desde una sesión administrativa, revisa las opciones de la conexión real.

Entender por qué no se utiliza el índice

El optimizador debe demostrar que el índice contiene todas las filas posibles del resultado. Una consulta reutilizable con Status = @Status generalmente no puede depender de un índice limitado a Status = 0. Ese mismo plan podría utilizarse después para registros completados. Probar una ejecución con cero no garantiza seguridad para otros parámetros.

Una consulta dedicada a pendientes con el predicado literal suele ser la opción más clara. Recompilar esa instrucción también puede ser válido si el costo de compilación se justifica. No empieces forzando el índice: una sugerencia incompatible con el filtro puede impedir generar un plan. Prueba la parametrización real de la aplicación, incluida la parametrización forzada si existe.

Las estadísticas filtradas representan el subconjunto y pueden mejorar estimaciones. Aun así, una población pequeña que cambia rápidamente necesita seguimiento. Compara filas estimadas y reales, además de las transiciones de estado. Las estadísticas generales de la tabla no describen automáticamente el trabajo pendiente. Para un filtro IS NULL, comprueba además si incluir la columna filtrada es necesario para conseguir el acceso esperado.

Separar velocidad y corrección de la cola

Cambiar una fila de pendiente a completada elimina su entrada del índice; reabrirla vuelve a insertarla. La historia ocupa menos espacio en ese índice, pero las transiciones siguen costando escrituras. Mide latencia y contención cuando numerosos procesos actualizan el extremo más antiguo de la cola.

El SELECT mostrado no reserva trabajo atómicamente. Dos procesos pueden leer los mismos identificadores antes de que alguno cambie el estado. Una cola de producción necesita una operación transaccional de reserva, propietario o arrendamiento, reintentos y recuperación si el trabajador falla. READPAST y bloqueos de actualización requieren analizar primero el aislamiento.

Revisa el crecimiento durante una caída, los mensajes defectuosos que permanecen pendientes y la velocidad de salida del conjunto activo. Un índice pequeño no compensa trabajadores incapaces de completar las tareas. Tampoco reemplaza una política de retención para los registros terminados.

El criterio operativo es vaciar el retraso sin privar de recursos a la actividad normal. Un índice filtrado bien elegido hace barato encontrar el subconjunto correcto, pero conserva explícitas las reglas de reserva, recuperación y conservación de datos.

Referencias técnicas: Microsoft Learn: Filtered indexes · Microsoft Learn: Index design.