Discussion :

[ypelissier]

Bonjour,
J'ai une base de données SQL Serveur 2019 dans laquelle je mets les logs de mon application.
Elle peut rapidement être volumineuse donc une "purge" est déjà envisagée à l'échelle du mois ou de l'année (pas encore tranché).

Toujours est-il que s'est posée la question de mettre une clé primaire (ce qui ne parait pas pertinent semble-t-il) mais ce qui parait pertinent est de mettre un index sur le champ d'horodatage du log.

1 - Me trompe-je pour la clé primaire ?
2 - Comment fonctionne les index sous SQL Server

Voici un extrait de la conversation eyu avec CinéPhil sur le chat :

11:06 [ypelissier]: Et pour des logs d'activité, doit-on mettre une clé primaire ? Il me semble que non pour ce cas là... Me trompe-je ?
11:32 [CinePhil]: La clé primaire n'est jamais obligatoire. Il faut se poser la question de l'utilité d'une clé primaire sur une table de log... Je suppose qu'il y aura une colonne horo-datée ?
11:32 [CinePhil]: Et que la recherche dans cette table se fera sur cet horo-datage ?
11:33 [ypelissier]: Oui mais je pense que l'horadatage ne sera pas primaire d'où l'absence de clé primaire
11:33 [ypelissier]: Oui, la recherche se fera avec un horodatage
11:34 [ypelissier]: En tout cas index sur la date
11:35 [CinePhil]: Après je ne sais pas comment fonctionnent les index sur SQL Server. Je suppose qu'il utilise un numéro interne de ligne ou un pointeur pour l'associer à la colonne d'index ? Auquel cas la clé primaire n'est peut-être pas utile sur cette table.
11:35 [CinePhil]: Peut-être y a t-il un renseignement là-dessus chez SQLPro. Ou lui demander son avis par message privé...
11:36 [CinePhil]: Parce qu'ici ça devient désert !
11:37 [ypelissier]: Alors là, pour moi aussi... Mais je vais demander à SQLPro, il passe par ici de temps en temps ? Ou plutôt directement en message privée sur le forum ? Ou mieux, sur le forum directement ?
11:42 [CinePhil]: Sur le forum ce serait mieux. Tout le monde en profite. Il ne passe ici que rarement.

Merci d'avance

[SQLpro]

Vous vous trompez lourdement.

1) une clef primaire améliore TOUJOURS les performances. Pour SQL Server, la meilleure clef primaire est un auto incrément. Compte tenu du nombre de ligne potentiel, un BIGINT me parait nécessaire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE ??? ADD ???_ID BIGINT IDENTITY PRIMARY KEY

2) plutôt qu'une purge un partitionnement sur mois années avec la compression me parait judicieux.... Un jour on vous demandera l'historique dur 5 ans pour le big data... Or on ne fais pas du BIG DATA avec les données des 3 derniers jours...

3) il existe plusieurs types d'index dans SQL Server :
3.1 - de type B-TREE :
3.1.1 CLUSTERED en général pour la PK (par défaut pour la PK)
3.1.2 NONCLUSTERED pour tous les autres (par défaut pour tous les index sauf PK, max 32 colonnes)
3.2 - de type "verticaux" (à raison d'un seul par table, max 1024 colonnes)
3.1.1 CLUSTERED COLUMNSTORE (donc toutes les colonnes de la table sont indexées)
3.1.2 NONCLUSTERED COLUMNSTORE (colonnes à choisir)

Les index de type CLUSTERED ne peuvent cohabiter car c'est la table qui est organisée sous forme d'index. Donc un seul index CLUSTERED possible soit "ordinaire" (c'est à dire en "row store) soit columnstore (donc vertical).

Les index verticaux sont intéressant si plusieurs millions de ligne. Ils deviennent indispensable si plusieurs milliards de lignes... et aussi indispensable si le nombre de critère de la clause WHERE est élevé (5 ou plus.... par exemple 20 critères...).

Compte tenu qu'il s'agit d'une table de LOG, le mieux est de compresser les données.... des index et de la table. Soit sur toutes les données, soit sur les partitions d'archives.
La compression des données améliore les temps de réponse des requêtes et diminue la volumétrie à stocker.

A +

[ypelissier]

Salut,
En déplacement ces derniers temps, je n'ai pas eu le temps de revenir vers toi.

J'ai bien compris la situation et je vais faire ce qu'il faut pour que ce soit impeccable. Je ne connaissais pas tous ces index mais je vais me renseigner (vers la fin du mois) pour les utiliser à bon escient.

Merci à toi (je laisse le point ouvert si j'ai des questions lié à ça).