SQL Server

Quand un index datetime aide dans SQL Server et quand il n'aide pas

Decouvrez quand les index datetime et datetime2 sont utiles dans SQL Server, et comment sélectivité, ordre des clés et plages influencent les plans.

Par JaviPublié 15 min de lectureIntermédiaire

Un index datetime2 peut être un excellent chemin d’accès. La question n’est pas de savoir si les horodatages ont trop de granularité, mais si les prédicats, le nombre de lignes retournées, les colonnes demandées et le rythme des écritures rendent cet index plus avantageux que les autres options.

La granularité des valeurs stockées et celle des requêtes sont différentes. Une table peut enregistrer les heures à la milliseconde tandis qu’un rapport demande une journée entiere. La première décrit les valeurs; la seconde détermine le nombre de lignes demandées à SQL Server.

Un journal d’événements reproductible

Le laboratoire suivant crée un million de lignes synthétiques. Il permet de comparer les plans sur votre propre instance SQL Server, sans prétendre à un résultat universel. Exécutez-le dans une base temporaire disposant d’un espace suffisant. GO sépare les lots dans SSMS et sqlcmd.

Requête SQL: Préparation du labo 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 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Notes

  • ligne 1Supprime dbo.EventLog : exécutez le labo dans une base de données créée pour lui.
  • lignes 13–22Six listes de dix chiffres croisées par CROSS JOIN produisent exactement 1 000 000 de nombres, sans boucle ni table de nombres.
  • lignes 24–251 000 événements par jour pendant 1 000 jours, espacés de 86,4 secondes.

Les valeurs générées couvrent 1 000 jours, avec 1 000 événements répartis régulièrement sur chaque journée. Chaque événement a sa propre milliseconde: la clé EventDate a donc une forte cardinalité. Selon le générateur, les nombres attendus (et non des mesures) sont d’environ un événement par minute, 42 par heure, 1 000 par jour, 29 000 par mois et presque un million pour la grande plage. CustomerId suit un cycle déterministe; les données ne reproduisent ni biais réaliste ni pics horaires. Elles ne contiennent aucune donnée client.

Activez Include Actual Execution Plan dans SSMS (Ctrl+M), puis recueillez les E/S, le processeur et le temps pour chaque requête. Exécutez chaque requête plusieurs fois, distinguez les essais avec cache chaud ou froid et consignez le plan réel ainsi que les paramètres. N’effacez pas le cache d’un serveur partagé pour fabriquer un essai froid.

Requête SQL: Comparer les prédicatsT-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 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Notes

  • ligne 1Les lectures et les temps CPU et écoulé s’affichent dans l’onglet Messages, à côté du plan d’exécution réel.
  • ligne 2Cet horodatage correspond exactement à une ligne générée.

EventId est la clé cluster; SQL Server l’utilise comme localisateur de ligne dans l’index non cluster sur EventDate. La projection étroite (EventId, EventDate) est donc couverte sans recherche supplémentaire. La projection large demande EventType, Amount et Description; un plan fondé sur l’index de date doit effectuer des recherches dans la table, sauf si un autre index couvre la requête. Dans le plan réel, relevez Actual Number of Rows et Actual Number of Rows Read, les lectures logiques de STATISTICS IO et le CPU ainsi que le temps écoulé de STATISTICS TIME. Notez l’opérateur utilisé (seek, scan, lookup ou combinaison), sans déduire les performances de sa seule icône. Un seek peut lire beaucoup de lignes, tandis qu’un scan peut coûter moins cher pour une requête portant sur une plage étendue.

Forme Prédicat Lignes renvoyées Accès Lectures logiques / CPU / temps
Horodatage exact EventDate = @ExactTimestamp mesurer Plan réel à mesurer Exécuter le labo; ne pas inventer de valeurs
Une minute Plage semi-ouverte mesurer Plan réel à mesurer Exécuter le labo; ne pas inventer de valeurs
Une heure Plage semi-ouverte mesurer Plan réel à mesurer Exécuter le labo; ne pas inventer de valeurs
Une journée Plage semi-ouverte mesurer Plan réel à mesurer Exécuter le labo; ne pas inventer de valeurs
Un mois Plage semi-ouverte mesurer Plan réel à mesurer Exécuter le labo; ne pas inventer de valeurs
Grande plage Plage semi-ouverte mesurer Plan réel à mesurer Exécuter le labo; ne pas inventer de valeurs
Jour converti CAST(EventDate AS date) =... mesurer Plan réel à mesurer Exécuter le labo; ne pas inventer de valeurs

Aucun benchmark contrôlé sur SQL Server n’a encore été exécuté. Les nombres du tableau sont des attentes déduites du générateur, et non des mesures de performance. Le script fournit un protocole reproductible; pour publier des mesures, consignez la version et le niveau de compatibilité SQL Server, le matériel et le stockage, le nombre de lignes, les index utilisés, le comportement du plan réel et la sortie de STATISTICS IO, TIME. Aucun plan ni aucune statistique mesurée ne sont présentés ici.

Recherche exacte et plage de dates

Pour un horodatage exact qui ne correspond qu’à quelques lignes, une clé temporelle à forte cardinalité peut être très sélective. L’index non cluster localise rapidement la plage de clés correspondante. La recherche d’égalité exacte dépend de la charge de travail; elle ne se déduit pas du type de colonne.

Pour les dates calendaires, utilisez une plage semi-ouverte : >= début et < lendemain. Elle inclut toutes les heures représentables du jour demandé, quelle que soit la précision fractionnaire. Le prédicat ne transforme pas non plus la colonne indexée. Évitez BETWEEN avec une borne de fin à minuit, qui exclut les heures suivantes de cette date, et ne fabriquez pas de limite artificielle à la dernière milliseconde.

CAST(EventDate AS date) = @Date ne signifie pas automatiquement scan. Selon la version et le plan, SQL Server peut parfois utiliser un accès de type seek, mais les estimations ou les lignes lues peuvent différer de la plage explicite. Comparez plans et lectures sur la version et les données visees. Un prédicat SARGable expose un argument de recherche que l’optimiseur peut exploiter directement sur une clé d’index; la plage conserve explicitement ses bornées.

Sélectivité : quelle part de la table faut-il lire ?

Une heure, une journée, un mois et une année peuvent utiliser la même clé temporelle, mais correspondent à des nombres de lignes très différents. Une plage étroite peut rendre un seek intéressant. Une plage étendue peut nécessiter assez de pages feuilles et de recherches dans la table pour qu’un scan coûte moins cher. Il n’existe aucun pourcentage universel auquel SQL Server passe du seek au scan.

La décision dépend de la largeur des lignes et de l’index, de la couverture des colonnes, de l’organisation physique, de la latence du stockage, de la qualité des statistiques, des estimations de cardinalité et du modèle de coût. Un seek qui retourne beaucoup de lignes reste une lecture importante. Un scan n’est pas mauvais si la requête a besoin de la plupart des lignes. Comparez le nombre de lignes retournées et lues, les lectures et le processeur sous une concurrence représentative.

C’est aussi le véritable enjeu de granularité des dates. EventDate distingue de nombreux instants; EventDay les regroupe dans une tranche plus grossière. Si un million de lignes partagent le même jour, une recherche par égalité sur ce jour n’est pas sélective. L’index peut rester utile si la table est beaucoup plus grande ou si la requête est couverte, mais le bucket ne réduit pas, à lui seul, un résultat volumineux.

Un bucket journalier comme accès de première classe

Si les utilisateurs filtrent ou regroupent souvent par jour, rendez cet accès explicite avec une colonne calculée et testez-la:

Requête SQL: Un bucket journalier comme accès de première classeT-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 lignesAffichage en lecture seule. Aucune connexion à une base de données.

La valeur persistée et son index occupent de l’espace et doivent être maintenus lors des insertions et des modifications. La clé du jour a une cardinalité plus faible que l’horodatage complet : une seule valeur peut donc désigner de nombreuses lignes. Cette option est pertinente si le jour sert souvent de filtre ou de critère de regroupement, et si les mesures justifient son coût de maintenance. Sinon, une plage temporelle suffit souvent et évite un index supplémentaire.

Les clés composites suivent la charge de travail

Une requête fréquente peut demander les événements d’un client pour une période. Comparez une clé commençant par le client et une clé commençant par la date:

Requête SQL: Clé par client ou clé par dateT-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 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Pour CustomerId = @Customer avec une plage temporelle, (CustomerId, EventDate) fournit souvent un préfixe d’égalité, suivi d’une plage bornée pour ce client. (EventDate, CustomerId) peut convenir aux charges de travail qui récupèrent surtout une courte période pour plusieurs clients. Aucun ordre n’est universel : examinez les autres filtres, tris, jointures, sélectivités et requêtes. Ne conservez pas les deux uniquement pour laisser le choix à l’optimiseur.

Si le résultat contient EventType et Amount, un index couvrant étroit peut éviter les recherches dans les lignes de base:

Requête SQL: Les clés composites suivent la charge de travailT-SQL
CREATE INDEX IX_EventLog_Customer_EventDate_Cover
ON dbo.EventLog(CustomerId, EventDate)
INCLUDE(EventType, Amount);
T-SQL3 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Les colonnes incluses sont stockées au niveau feuille et ne sont pas des clés de recherche. La couverture peut réduire le coût d’une plage en supprimant des recherches, mais elle augmente la taille de l’index et le travail d’écriture. N’incluez que les colonnes utiles à une requête avérée; une copie large de la table n’est pas un choix par défaut raisonnable.

Le bénéfice en lecture a un coût d’écriture

Chaque index ajoute de l’espace et du travail aux insertions, suppressions et modifications des colonnes clés ou incluses. Les journaux d’événements sont souvent alimentés par ajout; quelques index étroits et mesures valent parfois mieux que plusieurs variantes qui se recouvrent. Évaluez l’usage et le coût d’écriture sur une période représentative; vérifiez les dépendances et les cycles avant de supprimer un index spéculatif ou doublon. Les recommandations Microsoft traitent le bénéfice, l’espace et le coût de modification comme un même problème et mettent en garde contre la sur-indexation spéculative.

Une clé croissante peut être une candidate intéressante pour la clé cluster, car les insertions se font alors vers l’extrémité droite de la plage. Une date/heure n’est pas automatiquement le bon choix. Vérifiez son unicité (ou ajoutez une clé de départage unique), sa largeur et la façon dont la clé cluster est reprise dans les index non cluster; mesurez ensuite la contention sur la dernière page avec des insertions concurrentes. bigint IDENTITY est compact et prévisible; datetime2 encode un ordre utile, mais sa largeur, sa sémantique et le risque de collision diffèrent. Choisissez selon les accès et les mesures opérationnelles, pas selon une règle absolue.

Liste de décision

Indexez une colonne datetime lorsque les prédicats l’utilisent, que les plages sont assez sélectives, que l’ordre des clés correspond aux filtres courants, que l’index réduit les lectures coûteuses et que le coût d’écriture et de stockage reste acceptable. Réévaluez la décision si la plupart des requêtes renvoient de très grandes plages, si l’application transforme inutilement la colonne, si une autre clé d’égalité devrait venir en premier, si l’index fait doublon ou si son coût d’écriture dépasse le gain en lecture.

Pour les notions de base, consultez Les index SQL Server, puis Query Store et les régressions et le suivi des charges de travail SQL Server. Parcourez le hub SQL Server.

Sources et compléments

Captures d’écran possibles

Captures réelles facultatives : plans réels SSMS pour la plage explicite et la conversion de date; sortie de STATISTICS IO, TIME; propriétés des index comparés. Indiquez la version SQL Server, le niveau de compatibilité et l’état du cache. Aucune capture ou mesure n’est inventée ici.

  1. Valeurs d’horodatage
  2. ordonné par heure
    Arbre B EventDate
  3. Plage de début → fin
Datetime range access
  1. Peu de lignes : un seek peut convenir
  2. Comparer lignes lues et coût total
  3. Beaucoup de lignes : un scan peut coûter moins
Selectivity changes the access plan

Étiquettes

  • SQL Server
  • Indexation
  • Performance
  • T-SQL