Identity qui saute une plage

Par Arian Papillon le mercredi 24 juillet 2019, 17:21 - Lien permanent

Problème déjà rencontré à plusieurs reprises chez des clients : les valeurs auto-incrémentées d'une colonne IDENTITY se mettent de temps en temps à sauter une grande plage, faisant par exemple un saut de 1000, 10000 ou même 200000 valeurs...!

Ce problème peut survenir :

  • à partir de la version 2012 de SQL Server
  • si vous faites un redémarrage ou un failover "brutal" de votre instance

Ce fonctionnement est considéré comme normal et n'est pas vraiment un bug : depuis SQL Server 2012, le moteur ne génère plus les valeurs IDENTITY pour chaque insertion, mais en réserve tout un lot. Seule la valeur la plus élevée est écrite dans le journal de transactions. L'objectif est de limiter les écritures dans le journal pour améliorer les performances. En cas de crash, seule la dernière valeur de la plage peut être lue dans le journal de transactions, ce qui provoque le saut de toute la plage.

Si ce problème est gênant pour vous, sachez qu'il est possible, avant SQL Server 2017, d'activer le traceflag 272 (mettre -T272 dans les paramètres de démarrage pour l'appliquer à toute l'instance), afin de revenir au fonctionnement des versions précédentes. Autre alternative, à la place d'IDENTITY utilisez une séquence avec l'option NO CACHE.

Et depuis SQL Server 2017, il existe une option de configuration IDENTITY_CACHE = ON | OFF, à configurer avec la commande ALTER DATABASE SCOPED CONFIGURATION.

Pour ces différentes solutions, bien évidemment, on perd l'avantage éventuel en performances sur la journalisation car un enregistrement de journal de transactions sera écrit à chaque nouvelle valeur incrémentée...