SQL Server

Cuándo ayuda un índice datetime en SQL Server y cuándo no

Aprende cuándo ayudan los índices datetime y datetime2 en SQL Server y cómo influyen la selectividad, el orden de claves y las consultas por rango.

Por JaviPublicado 15 min de lecturaIntermedio

Un índice datetime2 puede ser una excelente ruta de acceso. La pregunta útil no es si las marcas de tiempo tienen demasiada granularidad, sino si los predicados, las filas devueltas, las columnas solicitadas y el ritmo de escritura hacen que ese índice sea más barato que las alternativas.

La granularidad de los valores almacenados y la granularidad de la consulta son distintas. Una tabla puede guardar horas con precisión de milisegundos mientras un informe pide un día completo. La primera describe los valores; la segunda determina cuántas filas solicita a SQL Server.

Un registro de eventos reproducible

Este laboratorio crea un millón de filas sintéticas. Sirve para comparar planes en tu propia instancia de SQL Server, no para prometer un resultado universal. Ejecútalo en una base desechable con espacio suficiente. GO es un separador de lotes que entienden SSMS y sqlcmd.

Consulta SQL: Preparación del laboratorio EventLogT-SQL
DROP TABLE IF EXISTS dbo.EventLog;
GO
CREATE TABLE dbo.EventLog
(
    EventId bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_EventLog PRIMARY KEY,
    EventDate datetime2(3) NOT NULL,
    CustomerId int NOT NULL,
    EventType varchar(20) NOT NULL,
    Amount decimal(10,2) NOT NULL,
    Description varchar(80) NOT NULL
);
GO
WITH digit(d) AS
(
    SELECT d FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS v(d)
), n AS
(
    SELECT CONVERT(int, ROW_NUMBER() OVER
        (ORDER BY d6.d, d5.d, d4.d, d3.d, d2.d, d1.d) - 1) AS n
    FROM digit AS d1 CROSS JOIN digit AS d2 CROSS JOIN digit AS d3
    CROSS JOIN digit AS d4 CROSS JOIN digit AS d5 CROSS JOIN digit AS d6
)
INSERT dbo.EventLog(EventDate, CustomerId, EventType, Amount, Description)
SELECT DATEADD(millisecond, CONVERT(int, (CONVERT(bigint, n % 1000) * 86400000) / 1000),
           DATEADD(day, n / 1000, CONVERT(datetime2(3), '2024-01-01'))),
       1 + (n % 10000),
       CASE n % 4 WHEN 0 THEN 'purchase' WHEN 1 THEN 'view'
                  WHEN 2 THEN 'refund' ELSE 'login' END,
       CONVERT(decimal(10,2), (n % 50000) / 100.0),
       CONCAT('Synthetic event ', n % 1000)
FROM n;
GO
CREATE INDEX IX_EventLog_EventDate ON dbo.EventLog(EventDate);
UPDATE STATISTICS dbo.EventLog WITH FULLSCAN;
GO
T-SQL35 líneasVista de solo lectura. No está conectada a ninguna base de datos.

Notas

  • línea 1Elimina dbo.EventLog: ejecuta el laboratorio en una base de datos creada para él.
  • líneas 13–22Seis listas de diez dígitos combinadas con CROSS JOIN generan exactamente 1.000.000 de números, sin bucle ni tabla de números.
  • líneas 24–251.000 eventos por día durante 1.000 días, separados 86,4 segundos.

Los valores generados cubren 1.000 días, con 1.000 eventos distribuidos uniformemente a lo largo de cada día. Cada evento tiene su propio milisegundo, por lo que EventDate presenta alta cardinalidad. Según el generador (cantidades esperadas, no resultados de benchmark), un minuto selecciona cerca de un evento, una hora unos 42, un día 1.000, un mes unos 29.000 y el intervalo amplio casi un millón. CustomerId sigue un ciclo determinista: los datos no reproducen sesgos realistas ni picos horarios. Son sintéticos y no contienen información de clientes.

Activa Include Actual Execution Plan en SSMS (Ctrl+M) y recoge las E/S, la CPU y el tiempo de cada consulta. Ejecuta cada consulta varias veces, distingue las pruebas con la caché caliente de las realizadas con la caché fría y registra el plan real y los parámetros. No vacíes la caché de un servidor compartido para simular una prueba en frío.

Consulta SQL: Comparar los predicadosT-SQL
SET STATISTICS IO, TIME ON;
DECLARE @ExactTimestamp datetime2(3) = '2024-02-10T01:00:28.800';
DECLARE @Start datetime2(3) = '2024-02-10T00:00:00';
DECLARE @End datetime2(3) = '2024-02-11T00:00:00';
DECLARE @MinuteEnd datetime2(3) = '2024-02-10T00:01:00';
DECLARE @HourEnd datetime2(3) = '2024-02-10T01:00:00';
DECLARE @MonthStart datetime2(3) = '2024-02-01T00:00:00';
DECLARE @MonthEnd datetime2(3) = '2024-03-01T00:00:00';
DECLARE @BroadEnd datetime2(3) = '2027-01-01T00:00:00';
DECLARE @Customer int = 42;

-- Exact timestamp: a very selective equality predicate.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDate = @ExactTimestamp;

-- Half-open minute range; the generator spaces events across the day.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDate >= @Start AND EventDate < @MinuteEnd;

-- Half-open day range: includes all times on the day, not midnight only.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDate >= @Start AND EventDate < @End;

-- Narrow one-hour range.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDate >= @Start AND EventDate < @HourEnd;

-- Wider projection: these columns are not in the date index.
-- Compare lookup work with a scan as the date range grows.
SELECT EventId, EventDate, EventType, Amount, Description
FROM dbo.EventLog WHERE EventDate >= @Start AND EventDate < @End;

-- Compare a conversion on the indexed column with the range form.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE CAST(EventDate AS date) = CONVERT(date, @Start);

-- Broader intervals: change parameters to one hour, one month, then a large span.
SELECT EventId, EventDate
FROM dbo.EventLog
WHERE EventDate >= @MonthStart AND EventDate < @MonthEnd;

-- Broad range covering most of the generated table.
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDate >= @Start AND EventDate < @BroadEnd;

-- Customer equality plus time range, with no customer-leading index yet.
SELECT EventId, EventDate, EventType, Amount
FROM dbo.EventLog
WHERE CustomerId = @Customer AND EventDate >= @Start AND EventDate < @End;
SET STATISTICS IO, TIME OFF;
T-SQL50 líneasVista de solo lectura. No está conectada a ninguna base de datos.

Notas

  • línea 1Las lecturas y los tiempos de CPU y transcurrido aparecen en la pestaña Mensajes, junto al plan de ejecución real.
  • línea 2Esta marca de tiempo coincide exactamente con una fila generada.

En el plan real, revisa Actual Number of Rows y Actual Number of Rows Read, las lecturas lógicas de STATISTICS IO y el CPU y tiempo transcurrido de STATISTICS TIME. Registra el operador (seek, scan, lookup o una combinación), pero no deduzcas el rendimiento solo por su icono. Un seek puede leer muchas filas y un scan puede ser la opción menos costosa para una consulta amplia.

Consulta Predicado Filas devueltas Acceso E/S lógicas / CPU / tiempo
Marca exacta EventDate = @ExactTimestamp Medir Medir el plan real Ejecuta el laboratorio; no inventes cifras
Un minuto Rango semiabierto Medir Medir plan real Ejecuta el laboratorio; no inventes cifras
Una hora Rango semiabierto Medir Medir el plan real Ejecuta el laboratorio; no inventes cifras
Un día Rango semiabierto Medir Medir el plan real Ejecuta el laboratorio; no inventes cifras
Un mes Rango semiabierto Medir Medir el plan real Ejecuta el laboratorio; no inventes cifras
Periodo amplio Rango semiabierto Medir Medir el plan real Ejecuta el laboratorio; no inventes cifras
Día convertido CAST(EventDate AS date) =... Medir Medir el plan real Ejecuta el laboratorio; no inventes cifras

Los resultados del benchmark no se publican todavía porque el banco de pruebas aún no se ha ejecutado en un entorno controlado de SQL Server. El procedimiento permite reproducir la prueba. Si se publican mediciones, deben incluir la versión y el nivel de compatibilidad de SQL Server, el hardware y el almacenamiento, el número de filas, las definiciones de índices, el comportamiento del plan real y la salida de STATISTICS IO, TIME. Aquí no se presentan planes ni estadísticas medidos.

Búsqueda exacta y rango de fechas

Para una marca de tiempo exacta que coincide con pocas filas, una clave temporal de alta cardinalidad puede ser muy selectiva. El índice no cluster localiza rápidamente el rango de claves coincidente. Que la aplicación busque esa igualdad exacta depende de la carga de trabajo; no debe suponerse por el tipo de columna.

Para fechas de calendario, usa un rango semiabierto: >= inicio y < día siguiente. Incluye todas las horas representables del día con independencia de la precisión fraccionaria. También deja intacta la columna indexada en el predicado. Evita BETWEEN con un final a medianoche, porque excluye el resto del día, y no fabriques una frontera artificial de último milisegundo.

CAST(EventDate AS date) = @Date no implica necesariamente un scan. Según la versión y el plan, SQL Server puede usar en algunos casos un acceso similar a seek, pero las estimaciones o el número de filas leídas pueden diferir de las del rango explícito. Compara los planes y las lecturas en la versión y con los datos de destino. Un predicado SARGable expone un argumento de búsqueda que el optimizador puede aplicar directamente a una clave de índice; el rango deja claros sus límites.

Selectividad: ¿qué parte de la tabla necesitas leer?

Una hora, un día, un mes y un año pueden usar la misma clave temporal, pero solicitan cantidades muy distintas de filas. Un rango estrecho puede favorecer un seek. Un intervalo amplio puede requerir tantas páginas hoja y búsquedas en la tabla que un scan resulte más barato. No existe un porcentaje universal a partir del cual SQL Server cambie de seek a scan.

La decisión varía con el ancho de fila e índice, la cobertura de columnas solicitadas, la organización física, la latencia de almacenamiento, la calidad de estadísticas, las estimaciones de cardinalidad y el modelo de costos. Un seek que devuelve muchas filas sigue implicando una lectura grande. Un scan no es automáticamente malo cuando se necesitan la mayoría de las filas. Compara filas devueltas y leídas, E/S y CPU bajo concurrencia representativa.

Esta es también la preocupación real sobre la granularidad de fechas. EventDate distingue muchos instantes; EventDay los agrupa en un bucket más grueso. Si un millón de filas comparte un día, la igualdad por día no es selectiva. El índice puede ayudar si la tabla es mucho mayor o la consulta queda cubierta, pero el bucket por sí solo no reduce un resultado grande.

Un bucket diario como acceso de primer nivel

Si los usuarios filtran o agrupan con frecuencia por día natural, haz explícito ese patrón con una columna calculada y pruébalo:

Consulta SQL: Un bucket diario como acceso de primer nivelT-SQL
ALTER TABLE dbo.EventLog
ADD EventDay AS CONVERT(date, EventDate) PERSISTED;
CREATE INDEX IX_EventLog_EventDay ON dbo.EventLog(EventDay);

DECLARE @Day date = '2024-02-10';
SELECT EventId, EventDate
FROM dbo.EventLog WHERE EventDay = @Day;
T-SQL7 líneasVista de solo lectura. No está conectada a ninguna base de datos.

El valor persistido y el índice ocupan almacenamiento y deben mantenerse al insertar o modificar filas. La clave del día tiene menor cardinalidad que la marca de tiempo completa, por lo que un mismo valor puede corresponder a muchas filas. Esta opción tiene sentido si el día se usa con frecuencia como filtro o criterio de agrupación y las mediciones justifican el coste de mantenimiento. De lo contrario, suele bastar el rango temporal y se evita otro índice.

Las claves compuestas siguen la carga de trabajo

Supón que una consulta frecuente solicita los eventos de un cliente durante un intervalo. Compara una clave que empiece por cliente con otra que empiece por fecha:

Consulta SQL: Clave por cliente frente a clave por fechaT-SQL
DROP INDEX IF EXISTS IX_EventLog_Customer_EventDate ON dbo.EventLog;
DROP INDEX IF EXISTS IX_EventLog_EventDate_Customer ON dbo.EventLog;
CREATE INDEX IX_EventLog_Customer_EventDate
ON dbo.EventLog(CustomerId, EventDate);
UPDATE STATISTICS dbo.EventLog WITH FULLSCAN;
-- Run the CustomerId + EventDate query and record its plan and reads.
DROP INDEX IX_EventLog_Customer_EventDate ON dbo.EventLog;
CREATE INDEX IX_EventLog_EventDate_Customer
ON dbo.EventLog(EventDate, CustomerId);
UPDATE STATISTICS dbo.EventLog WITH FULLSCAN;
-- Run the same query and workload mix; compare results, then drop the candidate.
DROP INDEX IX_EventLog_EventDate_Customer ON dbo.EventLog;
T-SQL12 líneasVista de solo lectura. No está conectada a ninguna base de datos.

Para CustomerId = @Customer junto con un rango temporal, (CustomerId, EventDate) suele ofrecer un prefijo de igualdad seguido de un rango acotado para ese cliente. (EventDate, CustomerId) puede convenir a cargas de trabajo que consultan principalmente intervalos breves entre muchos clientes. Ningún orden es universal: considera los demás predicados, el ordenamiento, las uniones, la selectividad y la mezcla completa de consultas. No conserves ambas claves solo para dar opciones al optimizador.

Si la consulta devuelve EventType y Amount, un índice de cobertura estrecho puede evitar búsquedas de filas base:

Consulta SQL: Las claves compuestas siguen la carga de trabajoT-SQL
CREATE INDEX IX_EventLog_Customer_EventDate_Cover
ON dbo.EventLog(CustomerId, EventDate)
INCLUDE(EventType, Amount);
T-SQL3 líneasVista de solo lectura. No está conectada a ninguna base de datos.

Las columnas incluidas se almacenan en las páginas hoja; no son claves de búsqueda. La cobertura puede abaratar un plan de rango al eliminar búsquedas en la tabla, pero aumenta el tamaño del índice y el coste de escritura. Incluye solo columnas que respondan a una consulta demostrada; una copia ancha de la tabla no es un valor predeterminado sensato.

El beneficio de lectura tiene un coste de escritura

Cada índice añade almacenamiento y trabajo a las inserciones, eliminaciones y actualizaciones de columnas clave o incluidas. Los registros de eventos suelen recibir principalmente inserciones, por lo que puede ser preferible mantener unos pocos índices estrechos y medidos en vez de varias variantes de fecha solapadas. Revisa el uso y el impacto en las escrituras durante un periodo representativo; comprueba las dependencias y los ciclos de carga antes de eliminar índices especulativos o duplicados. La guía de índices de Microsoft considera conjuntamente el beneficio para las consultas, el almacenamiento y el coste de modificación, y advierte contra la sobreindexación especulativa.

Una clave creciente puede ser una buena candidata a clave de clustering porque las inserciones tienden al extremo derecho del rango. Una fecha no es automáticamente la mejor opción. Comprueba si es única (o añade una clave única de desempate), su anchura y cómo se incorpora la clave de clustering a los índices no cluster; mide la contención de la última página con inserciones simultáneas. bigint IDENTITY es compacto y predecible; datetime2 puede codificar un orden útil, pero difiere en anchura, semántica y riesgo de colisión. Decide según los patrones de acceso y la evidencia operativa, no por una regla general.

Lista práctica de decisión

Indexa una columna datetime cuando los predicados la usan, los rangos son suficientemente selectivos, el orden de las claves coincide con los filtros habituales, el índice reduce lecturas costosas y el coste de escritura y almacenamiento es aceptable. Reconsidera la decisión si la mayoría de las consultas devuelve intervalos enormes, la aplicación transforma la columna sin necesidad, otra clave de igualdad debería ir primero, el índice duplica uno existente o el coste de escritura supera el beneficio de lectura.

Para empezar, consulta Índices en SQL Server, continúa con regresiones de Query Store y monitorización de cargas de trabajo de SQL Server. Explora el hub de SQL Server.

Fuentes y lecturas adicionales

Plan de capturas

Capturas reales opcionales: planes de ejecución reales de SSMS para el rango explícito y la conversión de fecha; salida de STATISTICS IO, TIME; y propiedades de los índices comparados. Indica la versión de SQL Server, el nivel de compatibilidad y las condiciones de caché. No se inventan capturas ni cifras de benchmark.

  1. Timestamp values
  2. order by time
    EventDate B-tree
  3. Start to end range
Datetime range access
  1. Few rows: seek may suit the consulta
  2. Compare rows read and total cost
  3. Many rows: scan may cost less
Selectivity changes the access plan

Etiquetas

  • SQL Server
  • Indexación
  • Rendimiento
  • T-SQL