Blog Data Azure Azure SQL SQL Server T-SQL Indexes Performance
Data Azure

Covering indexes en Azure SQL: cuándo un índice cubre una consulta y por qué reduce lecturas

15 min lectura
Diagrama conceptual de una consulta SQL resuelta desde un índice que cubre las columnas necesarias

Un índice no mejora una base de datos por existir. La mejora cuando coincide con una forma concreta de leer los datos. En Azure SQL Database, SQL Server y Azure SQL Managed Instance, un covering index no es un tipo especial de índice con una sintaxis propia, sino una propiedad relativa a una consulta: un índice “cubre” una consulta cuando contiene todo lo que el motor necesita para resolverla sin volver a la tabla base o al índice clustered para recuperar columnas adicionales.

La idea parece simple, pero tiene implicaciones importantes en rendimiento, coste de escritura, mantenimiento y diseño de consultas. Un índice que cubre una consulta frecuente puede reducir lecturas lógicas, CPU y latencia. También puede convertirse en deuda técnica si se añade sin entender qué consulta cubre, cuántas veces se ejecuta y qué impacto tendrá sobre INSERT, UPDATE y DELETE.

Qué significa que un índice cubra una consulta

Un índice cubre una consulta cuando contiene las columnas necesarias para ejecutar esa consulta concreta:

  • columnas usadas en predicados (WHERE);
  • columnas usadas en joins;
  • columnas usadas para ordenar o agrupar, si aplica;
  • columnas devueltas en el SELECT.

Si falta alguna columna, el optimizador puede usar el índice para localizar filas candidatas, pero después necesitará buscar el resto de los datos en otra estructura. Esa búsqueda adicional suele aparecer en el plan de ejecución como Key Lookup cuando la tabla tiene índice clustered, o como RID Lookup cuando la tabla es un heap.

En términos prácticos, un covering index intenta evitar ese segundo salto. El motor lee el índice, encuentra las filas necesarias y obtiene desde sus páginas todas las columnas solicitadas. En tablas pequeñas la diferencia puede ser irrelevante. En tablas grandes, o en consultas que se ejecutan muchas veces por minuto, evitar lookups repetidos puede reducir de forma notable el trabajo total.

La precisión importante es esta: un índice no es cubriente en abstracto; cubre una consulta concreta. El mismo índice puede cubrir una consulta, ayudar parcialmente a otra y no ser útil para una tercera.

Índice clustered, índice nonclustered y lookup

Para entender los covering indexes conviene separar dos conceptos.

Un índice clustered organiza las filas de la tabla según la clave del índice dentro de su estructura B-tree. En SQL Server solo puede haber un índice clustered por tabla. No conviene describirlo como una garantía absoluta de “orden físico” de páginas en disco, pero sí como la estructura principal donde se almacenan las filas de una tabla clustered.

Un índice nonclustered es una estructura separada. Contiene las columnas clave del índice y un localizador hacia la fila completa. Si la tabla tiene índice clustered, ese localizador está basado en la clave clustered. Si la tabla es un heap, el localizador es un RID.

Cuando una consulta filtra por una columna indexada pero selecciona columnas que no están en ese índice nonclustered, el motor puede usar una estrategia en dos pasos:

  1. buscar en el índice nonclustered las filas que cumplen el filtro;
  2. recuperar las columnas que faltan desde la tabla clustered o desde el heap.

Ese patrón puede ser eficiente si devuelve pocas filas. Pero si devuelve muchas, los lookups repetidos pueden convertirse en una parte cara del plan.

Ejemplo: pedidos por cliente

Supongamos una tabla simplificada de pedidos. El objetivo no es modelar todos los detalles de un sistema real, sino mostrar un patrón frecuente: una consulta repetitiva con filtros claros y pocas columnas devueltas.

CREATE TABLE dbo.Orders
(
    OrderId     bigint IDENTITY(1,1) NOT NULL,
    CustomerId  int NOT NULL,
    OrderDate   datetime2(3) NOT NULL,
    Status      varchar(20) NOT NULL,
    TotalAmount decimal(12,2) NOT NULL,
    Notes       nvarchar(1000) NULL,
    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderId)
);
GO

CREATE INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId);
GO

La tabla tiene una clave primaria clustered sobre OrderId y un índice nonclustered básico sobre CustomerId. Ese índice puede ayudar a encontrar los pedidos de un cliente, pero no cubre una consulta que necesita devolver fecha, estado e importe.

La aplicación ejecuta con frecuencia esta consulta:

DECLARE @CustomerId int = 42;

SELECT TOP (25)
    OrderId,
    OrderDate,
    Status,
    TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
ORDER BY OrderDate DESC;

Con solo IX_Orders_CustomerId, el motor puede localizar las filas del cliente. Sin embargo, ese índice no contiene todas las columnas necesarias ni mantiene los pedidos ordenados por OrderDate dentro de cada cliente. Según las estadísticas, el volumen de datos y el coste estimado, el plan podría incluir:

  • lookups al índice clustered para recuperar columnas faltantes;
  • una operación Sort para satisfacer el ORDER BY;
  • o incluso otra estrategia si el optimizador estima que el índice no compensa.

Un índice diseñado para cubrir ese patrón puede reducir ese trabajo.

Diseñar el índice con claves e INCLUDE

Un covering index no consiste en añadir todas las columnas posibles. La decisión importante es separar columnas clave y columnas incluidas.

Las columnas clave forman la estructura ordenada del índice. Son las que ayudan a buscar, filtrar por rangos, ordenar o unir.

Las columnas añadidas mediante INCLUDE se almacenan en el nivel hoja del índice nonclustered. No forman parte de la clave de navegación, pero permiten que el índice contenga columnas necesarias para devolver el resultado.

Para la consulta anterior, una definición razonable sería:

CREATE INDEX IX_Orders_CustomerId_OrderDate_Covering
ON dbo.Orders (CustomerId, OrderDate DESC)
INCLUDE (Status, TotalAmount);
GO

La clave empieza por CustomerId porque el predicado de igualdad filtra por cliente. Después aparece OrderDate DESC, porque la consulta pide los últimos pedidos y ordena por fecha descendente. Las columnas Status y TotalAmount se añaden con INCLUDE, porque se devuelven en el resultado pero no son necesarias para navegar por el índice.

OrderId no se incluye explícitamente en este ejemplo porque es la clave clustered de la tabla. En una tabla clustered, las claves del índice clustered forman parte del localizador de fila en los índices nonclustered, por lo que pueden estar disponibles para satisfacer la consulta.

Nota: este detalle depende del diseño real de la tabla. Si la tabla fuera un heap, o si la clave clustered fuera distinta, habría que revisar la definición del índice y el plan de ejecución antes de asumir que una columna está disponible.

Con este índice, la consulta puede buscar las filas de CustomerId = 42, leerlas en orden de OrderDate DESC y devolver las columnas necesarias desde el propio índice. El resultado esperado es una reducción de lookups y, potencialmente, la eliminación de un Sort.

Cómo validar si realmente ayuda

La validación no debería basarse en intuición. Un covering index debe medirse con planes de ejecución y métricas de I/O. En SQL Server y Azure SQL se puede empezar con SET STATISTICS IO, TIME ON en un entorno controlado:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DECLARE @CustomerId int = 42;

SELECT TOP (25)
    OrderId,
    OrderDate,
    Status,
    TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
ORDER BY OrderDate DESC;
GO

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

El dato más útil para una primera comparación suele ser logical reads. Si el índice está bien diseñado para esa consulta, debería requerir menos lecturas lógicas, especialmente si antes el plan hacía muchos lookups.

También conviene revisar el plan de ejecución para confirmar:

  • qué índice se usa;
  • si han desaparecido Key Lookup o RID Lookup;
  • si se evita una operación Sort;
  • si el número estimado de filas se aproxima al número real;
  • si el plan nuevo introduce otros operadores costosos.

El tiempo de ejecución de una prueba puntual puede ser engañoso por caché, concurrencia o variabilidad del entorno. Las lecturas lógicas suelen dar una señal más estable sobre el trabajo que el motor necesita realizar. En sistemas con carga real, Query Store puede ayudar a observar tendencias, regresiones y cambios de plan a lo largo del tiempo.

El coste: las escrituras también se encarecen

Cada índice adicional tiene coste. Cuando se inserta una fila, se actualiza una columna indexada o se elimina un registro, el motor debe mantener las estructuras de índice afectadas.

Un covering index que acelera una consulta crítica puede ser una gran decisión. Varios índices añadidos para consultas marginales pueden degradar el rendimiento de escritura, aumentar el almacenamiento, consumir más memoria de caché y complicar el mantenimiento.

Las columnas incluidas también ocupan espacio. Incluir columnas grandes o poco usadas para cubrir una consulta de resumen suele ser mala señal. La regla práctica es mantener el índice lo más estrecho posible y orientado a un patrón de lectura concreto.

Advertencia: no conviertas un índice en una copia parcial de la tabla “por si acaso”. Si un índice incluye demasiadas columnas, puede mejorar una consulta aislada y empeorar el comportamiento global de la base de datos.

Cuándo tiene sentido crear un covering index

Un covering index suele tener sentido cuando la consulta cumple varias condiciones:

  • es frecuente o crítica para la experiencia de usuario;
  • tiene un patrón estable de filtros, ordenación y columnas devueltas;
  • devuelve un conjunto relativamente pequeño de columnas;
  • se ejecuta sobre una tabla suficientemente grande o con mucha frecuencia;
  • el plan actual muestra lookups costosos, sorts evitables o muchas lecturas lógicas;
  • la mejora compensa el coste adicional de escrituras y mantenimiento.

No suele tener sentido crear un índice cubriente para consultas exploratorias, informes esporádicos o pantallas que cambian constantemente de filtros y columnas. En esos casos, el índice puede quedar obsoleto rápidamente o competir con otros índices más útiles.

También hay que considerar la selectividad. Si el filtro devuelve una gran proporción de la tabla, un índice nonclustered que cubre la consulta no garantiza que el optimizador vaya a usarlo. A veces un scan puede ser más barato que recorrer un índice y procesar muchas filas.

Orden de columnas: igualdad, rango y ordenación

El orden de columnas en la clave del índice es una de las decisiones más importantes. Una guía práctica habitual es colocar primero columnas usadas en predicados de igualdad, después columnas usadas en rangos y, cuando encaje con el patrón, columnas que ayuden a satisfacer ORDER BY.

No es una ley universal. Depende de las consultas, la distribución de datos y las estimaciones de cardinalidad. Pero evita muchos diseños ineficientes.

En el ejemplo anterior, CustomerId aparece primero porque la consulta filtra por igualdad:

WHERE CustomerId = @CustomerId

Después aparece OrderDate DESC, porque la consulta pide los últimos pedidos de ese cliente:

ORDER BY OrderDate DESC

Si el patrón fuera diferente, el índice también debería cambiar. Por ejemplo, una búsqueda operativa por estado y rango de fechas podría requerir otra clave:

CREATE INDEX IX_Orders_Status_OrderDate_Covering
ON dbo.Orders (Status, OrderDate)
INCLUDE (CustomerId, TotalAmount);
GO

Este índice cubre otro patrón de acceso: filtrar por Status, acotar o recorrer por fecha y devolver cliente e importe. No sustituye necesariamente al índice anterior, porque optimiza una forma distinta de consultar los datos.

Lo esencial es no diseñar índices en abstracto. Se diseñan a partir de consultas reales, datos reales y planes reales.

Composite index no es lo mismo que covering index

Un índice compuesto es un índice cuya clave tiene varias columnas, por ejemplo:

CREATE INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders (CustomerId, OrderDate);

Un covering index, en cambio, es un índice que cubre una consulta concreta. Puede ser compuesto o no. También puede usar columnas incluidas.

Por ejemplo, este índice compuesto podría cubrir una consulta que solo necesita CustomerId, OrderDate y la clave clustered:

CREATE INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders (CustomerId, OrderDate);

Pero si la consulta también devuelve Status y TotalAmount, ese índice ya no cubre completamente la consulta. Para cubrirla, podríamos añadir esas columnas como incluidas:

CREATE INDEX IX_Orders_CustomerId_OrderDate_Covering
ON dbo.Orders (CustomerId, OrderDate DESC)
INCLUDE (Status, TotalAmount);

La diferencia es importante: “compuesto” describe la forma del índice; “covering” describe la relación entre el índice y una consulta.

Antipatrones habituales

Crear un índice para cada consulta lenta

Una consulta puede ser lenta por muchas razones: estadísticas desactualizadas, predicados no sargables, conversiones implícitas, bloqueos, mala estimación de cardinalidad, parametrización problemática o exceso de datos devueltos.

Añadir un índice sin diagnosticar puede ocultar el problema o trasladarlo a las escrituras.

Incluir columnas que no se usan

Si la aplicación solo necesita Status y TotalAmount, no hay motivo para incluir Notes, direcciones, columnas de auditoría o campos grandes. El índice debe reflejar el contrato real de la consulta.

Ignorar el orden de las claves

Un índice sobre (OrderDate, CustomerId) no es equivalente a uno sobre (CustomerId, OrderDate) si la consulta filtra por cliente y ordena por fecha. Contienen las mismas columnas, pero ofrecen rutas de navegación distintas.

No revisar los índices con el tiempo

Las aplicaciones cambian. Algunas consultas desaparecen, otras cambian de forma y algunos índices dejan de usarse. Azure SQL y SQL Server ofrecen vistas de sistema y herramientas de observabilidad para analizar uso de índices, aunque sus métricas deben interpretarse con cuidado porque pueden reiniciarse tras determinados eventos de servicio, reinicios o cambios de entorno.

Una guía mínima de decisión

Antes de crear un covering index en producción, conviene responder estas preguntas:

  1. ¿La consulta es frecuente o crítica?
  2. ¿El plan actual muestra lookups costosos, sorts evitables o muchas lecturas lógicas?
  3. ¿El conjunto de columnas devueltas es pequeño y estable?
  4. ¿El orden de columnas del índice responde al patrón real de filtros y ordenación?
  5. ¿La mejora se ha medido en un entorno representativo?
  6. ¿El coste adicional en escrituras, almacenamiento y mantenimiento es aceptable?
  7. ¿Existe ya un índice similar que pueda ajustarse en lugar de crear otro nuevo?

Si la respuesta a varias de estas preguntas es afirmativa, un índice que cubra la consulta puede ser una optimización adecuada. Si no, probablemente convenga investigar primero otras causas.

Conclusión

Un covering index es una herramienta precisa: permite que una consulta se resuelva desde el propio índice, evitando accesos adicionales a la tabla base, al heap o al índice clustered. En Azure SQL puede reducir lecturas lógicas, CPU y latencia cuando se aplica sobre consultas frecuentes y bien entendidas.

La clave está en diseñarlo desde el patrón de acceso, no desde la tabla. Las columnas de filtro, join y ordenación suelen pertenecer a la clave del índice. Las columnas devueltas que no participan en la navegación suelen encajar mejor en INCLUDE.

Después hay que medir: comparar planes, lecturas lógicas y comportamiento bajo carga real. La higiene en T-SQL no consiste en añadir índices hasta que una consulta mejore en una prueba local. Consiste en mantener una relación sana entre consultas, índices y datos. Un covering index bien diseñado es una de las formas más efectivas de conseguirlo.