Discussion :

[Michel.Priori]

sur chaque serveur la clef autoincrémentée commence à une valeur différentes avec un pas calculé sur le nombre potentiel de machine en réplication.
par exemple si vous pensez n'avoir jamais plus de 100 machines, alors la machine 1 commence à 1 avec un pas de 100 (IDENTITY(1, 100)...), la machine 2, commence à 2 avec un pas de 100...

A chacun ses expériences douloureuses.
Comme indiqué avant cette technique souffre de l'énorme problème des actions "jouant" avec la valeur identity comme par exemple :
- dbcc checkident https://docs.microsoft.com/fr-fr/sql...ql-server-2017
- set identity insert https://docs.microsoft.com/fr-fr/sql...ql-server-2017

Si c'est l'option retenue alors :
1- il convient d'ajouter une contrainte check en jouant du modulo.
2- de vérifier que le type choisi convient au besoin de numérotation (n'oublions pas qu'une valeur identity rollbackée est quand même consommée et qu'on peut purger tout ou partie d'une table sans changer la numérotation) dans le temps :
a- si int : le max est 2 147 483 647 si on prend 100 serveurs le max sera de 21 474 836 pour chacun d'eux, individuellement.
b- si bigint, la capacité devrait aller, mais on ajoute 8 octets à la table pour éviter de faire usage d'une colonne de 16 octets.

Dans le cas a- en adoptant l'index cluster sur la PK et la colonne GUID en tant que clé candidate avec un index non cluster, l'activité propre au serveur devrait bien se passer et l'activité de réplication, qui compare l'intégralité des colonnes pour chaque ligne existante des 2 côtés, le surcoût semble correct.

Dans le cas b- l'écart se resserre. A voir. Pas persuadé que le bilan global soit favorable à cette technique.

J'avoue que je réagit plus par peur de reproduire l'expérience douloureuse que suite à un POC en faisant varier le nombre d'abonnés (par exemple de 2 à 92 par paliers de 10) et la volumétrie, le tout en charge.
Ce serait un bon exercice ça !
L'utilisation de HammerDB (et du TCPH) pour fixer la charge et déterminer la volumétrie pourrait être utile.

Perso je n'ai ni le temps ni les moyens techniques de le faire.
Mais je suis preneur du résultat.
Surtout s'il ne va pas dans mon sens.

[SQLpro]

...
Si c'est l'option retenue alors :
1- il convient d'ajouter une contrainte check en jouant du modulo.

C'est une option fortement recommandée....

2- de vérifier que le type choisi convient au besoin de numérotation (n'oublions pas qu'une valeur identity rollbackée est quand même consommée et qu'on peut purger tout ou partie d'une table sans changer la numérotation) dans le temps :
a- si int : le max est 2 147 483 647 si on prend 100 serveurs le max sera de 21 474 836 pour chacun d'eux, individuellement.
b- si bigint, la capacité devrait aller, mais on ajoute 8 octets à la table pour éviter de faire usage d'une colonne de 16 octets.

Aucune importance. Même phénomène en pire dans NEWSEQUENTIALID() car il est perdu en cas de ROLLBACK et comme il est partagé par toutes les table il n'est pas continu dans une même table !

A +

[Michel.Priori]

Même phénomène, en pire dans NEWSEQUENTIALID(), car il est perdu en cas de ROLLBACK et comme il est partagé par toutes les table il n'est pas continu dans une même table !

Heu... non.

On parle bien de de l'épuisement des valeurs, épuisement comparé entre identity (avec un pas de 100) et NEWSEQUENTIALID().
OK.
L'aspect continu est perdu avec un pas de 100. Donc ce côté les 2 solutions sont équivalentes.

Comparer l'épuisement d'un range sur 4 octets (INT, dont la moitié des valeurs sont en négatif) à un range de 16 octets (UNIQUEIDENTIFIER) représente une large différence opérationnelle.

Le fait que la fonction NEWSEQUENTIALID() donne un nombre unique à travers le temps et les machines ET supérieur à la dernière valeur ne garanti en rien que la moyenne des écarts soit d'environ 100. mais de là à penser qu'elle est de 2^12... y a de la marge.

Ce calcul est d'autant plus inutile car la fonction NEWSEQUENTIALID() repart à chaque redémarrage en fonction d'une valeur aléatoire.
https://docs.microsoft.com/fr-fr/sql...ql-server-2017

Et de toute façon si problème il devait y avoir, vu que réplication de fusion la met en place avec cette fonctionnalité on va devoir faire avec.
La seule question est de savoir si on "double le coup" ou pas.

[SQLpro]

Heu... non.

On parle bien de de l'épuisement des valeurs.

... ???? d'ou vous sortez tout d'un coup cette notion d'épuisement des valeurs ???

... évidemment qu'entre de 4 octets et du 16 octets l'épuisement sera plus rapide; Mais pourquoi vous cantonner au 4 octets (INT) alors qu'il y a le BIGINT (8 octets) ?
Déjà avec le BIGINT, à raison de 1 000 000 de lignes insérées par seconde, il vous faudra 584 554 années, 17 jours, 8 heures, 1 minute et 49 secondes pour épuiser le stocke des valeurs potentielles.
Alors même avec 1000 instances en parallèle, cela vous laisse : 5 845 années soit un peu plus de 58 siècles.
De plus l'identity étant propre à chaque table (contrairement au GUID qui est global), si vous avez 100 tables, vous aurez donc, épuisez toutes les valeurs de toutes les tables dans environ 58 millions d'années.

Quatre inconvénients supplémentaires du fait du GUID par rapport à l'IDENTITY :
1) deux fois plus d'octets => moins de performances car la lecture d'un GUID nécessite 2 passes dans le processeurs alors qu'un INT n'en fait qu'une seule. Et si c'est l'index cluster ce sont tous les index secondaires qui sont impactés....
Ce sont aussi des tables notablement plus grosses, car il faut aussi compter les clefs étrangères qui seront aussi deux fois plus grosses... tout ceci n'est pas négligeable en terme de maintenance (sauvegarde - et la compression n'est pas efficace sur un GUID - maintenance des index et statistiques...).
2) le hot spot créé par le calcul du GUID. En effet les performances sont nettement ralenties lorsqu'il y a une concurrence des INSERT dans différentes tables du fait de différents utilisateurs, car c'est le même bout de code (une API Windows) qui calcule les GUID. Les performances deviennent d'ailleurs erratique en cas de très forte concurrence...
3) la lecture du GUID... Essayez donc au téléphone (dans la cadre d'un SAV applicatif par exemple) de demandez à un utilisateur de vous communiquer la valeur d'un GUID... neuf fois sur 10 il se trompera, alors qu'avec un simple chiffre il y a moins de risque d'erreur !
4) pas de possibilité de récupérer facilement la valeur d'un GUID après insertion, alors que c'est si simple avec un IDENTITY (fonction SCOPE_IDENTITY()...)

Autre inconvénient, mais je ne sais pas s'il est toujours de mise, pour des raisons de dispersion universelle des valeurs, le GUID est calculé par rapport à une lecture de la MAC adresse du PC. En multipliant le nombre de GUID généré par une même machine on peut reconstituer la MAC adresse de la machine. Ceci était très vrai au tout début des algorithmes de génération des GUID....

Pour info, il y a 10 ans, nous avons eut à auditer une base de données gouvernementale, liée à la santé (je ne peut en dire plus, mais plusieurs millions de bénéficiaires...); Toutes les clefs était en GUID et malgré le NEWSEQUENTIALID() c'était une catastrophe.... Il a fallut changer en INT/BIGINT/SMALLINT avec IDENTITY selon le cas pour chaque table.


A +

refs :
https://blogs.msdn.microsoft.com/sql...vs-int-debate/
https://www.mssqltips.com/sqlservert...t-versus-guid/
https://stackoverflow.com/questions/...cifically-rega
https://sqlperformance.com/2015/01/s...its-disk-space
https://sqlserverperformance.wordpre...in-sql-server/

[Invité]

... évidemment qu'entre de 4 octets et du 16 octets l'épuisement sera plus rapide; Mais pourquoi vous cantonner au 4 octets (INT) alors qu'il y a le BIGINT (8 octets) ?
Déjà avec le BIGINT, à raison de 1 000 000 de lignes insérées par seconde, il vous faudra 584 554 années, 17 jours, 8 heures, 1 minute et 49 secondes pour épuiser le stocke des valeurs potentielles.
Alors même avec 1000 instances en parallèle, cela vous laisse : 5 845 années soit un peu plus de 58 siècles.

J'avais fait cette démonstration lorsque l'on m'a imposé un bigint auto-incrémenté en clef primaire pour une table « potentiellement » TRÈS active...
1 an après la mise en prod, on atteignait pas 100 lignes...
j'en ris encore !

[Michel.Priori]

... ???? d'ou vous sortez tout d'un coup cette notion d'épuisement des valeurs ???

Des calculs exprimés dans le point n°2 que vous avez repris en commentaire.

... évidemment qu'entre de 4 octets et du 16 octets l'épuisement sera plus rapide; Mais pourquoi vous cantonner au 4 octets (INT) alors qu'il y a le BIGINT (8 octets) ?
Déjà avec le BIGINT, à raison de 1 000 000 de lignes insérées par seconde, il vous faudra 584 554 années, 17 jours, 8 heures, 1 minute et 49 secondes pour épuiser le stocke des valeurs potentielles.

On est d'accord, avec un bigint on est large.
C'était dis dans le fameux point 2.
Merci pour les calculs

Alors même avec 1000 instances en parallèle, cela vous laisse : 5 845 années soit un peu plus de 58 siècles.
De plus l'identity étant propre à chaque table (contrairement au GUID qui est global), si vous avez 100 tables, vous aurez donc, épuisez toutes les valeurs de toutes les tables dans environ 58 millions d'années.

Là, par contre le calcul me semble foireux : le remplissage des tables n'est pas homogène, ni même le remplissage des bases.
Le point bloquant sera atteint à la 1ere table arrivant à court de n° (et non pas de lignes, déjà expliqué la différence)

Quatre inconvénients supplémentaires du fait du GUID par rapport à l'IDENTITY :
1) deux fois plus d'octets => moins de performances car la lecture d'un GUID nécessite 2 passes dans le processeurs alors qu'un INT n'en fait qu'une seule.

Plusieurs choses :
Occupation de l'espace par rapport au type : UNIQUEIDENTIFIER = 2 BIGINT = 4 INT
Choix d'avoir une colonne UNIQUEIDENTIFIER : rappelons que dans le cas d'une réplication de fusion cette colonne est imposée, donc présente.

C'est la colonne UNIQUEIDENTIFIER qui va être utilisé par le mécanisme de réplication.
On réécrit tout en open source et on en informe bilou ?

Et si c'est l'index cluster ce sont tous les index secondaires qui sont impactés....

Vrai.
Mais on avait déjà parlé de bien gérer ça, non ?

Ce sont aussi des tables notablement plus grosses, car il faut aussi compter les clefs étrangères qui seront aussi deux fois plus grosses...

Re vrai.

tout ceci n'est pas négligeable en terme de maintenance (sauvegarde - et la compression n'est pas efficace sur un GUID - maintenance des index et statistiques...).
2) le hot spot créé par le calcul du GUID. En effet les performances sont nettement ralenties lorsqu'il y a une concurrence des INSERT dans différentes tables du fait de différents utilisateurs, car c'est le même bout de code (une API Windows) qui calcule les GUID. Les performances deviennent d'ailleurs erratique en cas de très forte concurrence...

Ben si la présence de la colonne est imposée, ce sera idem, kifkif bourricot.

3) la lecture du GUID... Essayez donc au téléphone (dans la cadre d'un SAV applicatif par exemple) de demandez à un utilisateur de vous communiquer la valeur d'un GUID... neuf fois sur 10 il se trompera, alors qu'avec un simple chiffre il y a moins de risque d'erreur !

Vaste débat de savoir quand et comment afficher les identifiants techniques aux utilisateurs.
Je préfère avoir à déterminer par moi même l'identifiant de Mme Duchemu, même s'il y en existe plusieurs, que de parler du client 120356.
Et si on doit passer par une liste d'identifiants alors là le bon vieux copier-coller fait l'affaire.
L'avantage est que si on se trompe de table (si, si je l'ai vu ) les valeurs étant globalement unique, la requête ne renvoie rien, plutôt que des lignes erronées.

4) pas de possibilité de récupérer facilement la valeur d'un GUID après insertion, alors que c'est si simple avec un IDENTITY (fonction SCOPE_IDENTITY()...)

Changement de pratique.
On fait comment avec une séquence ?
Au passage faudra le dire à Larry (Oracle) que c'est pas pratique son idée de séquence

Autre inconvénient, mais je ne sais pas s'il est toujours de mise, pour des raisons de dispersion universelle des valeurs, le GUID est calculé par rapport à une lecture de la MAC adresse du PC. En multipliant le nombre de GUID généré par une même machine on peut reconstituer la MAC adresse de la machine. Ceci était très vrai au tout début des algorithmes de génération des GUID....

Pour attaquer un serveur grâce à sa MAC address il faut être dans le même sous réseau dans le cadre de l'unicast.
Il existe des doublons de MAC address : 6 octets c'est plus qu'en IPv4 mais moins qu'en IPV6...
Les machines virtuelles sont aussi des grandes pourvoyeuses de mac en double.

Pour info, il y a 10 ans, nous avons eut à auditer une base de données gouvernementale, liée à la santé (je ne peut en dire plus, mais plusieurs millions de bénéficiaires...); Toutes les clefs était en GUID et malgré le NEWSEQUENTIALID() c'était une catastrophe.... Il a fallut changer en INT/BIGINT/SMALLINT avec IDENTITY selon le cas pour chaque table.

Ben ça je veux bien le croire.
Surtout passer au SMALLINT ça du faire du bien.
Il me manque de savoir si vous avez conservé les colonnes uniqueidentifier dans chaque table.
Si c'est le cas, au moins pour la partie hors réplication, l'écart de performance pour les tables en BIGINT est valable.

Je m'emballe peut être. Est-ce que l'écart de performance a été mesuré globalement ou possède t'on des métriques spécifiques sur les tables dont l'ID est passé en BIGINT ?

Si ce n'est le cas, le travail nécessaire a été fait, et, je pense même, bien fait. Mais l'exemple n'est pas valide dans le sens où ce travail n'est pas transposable à une réplication de fusion.

NOTE : j'espère que notre petit débat intéresse les autres internautes car sinon on pourrait débattre de ça autours d'une bière, non ?

[SQLpro]

Pour info, pour l'IDENTITY tu peut aussi utiliser un DECIMAL et donc monter à 38 chiffres (DECIMAL(38,0)...) soit une quantité d'entier de 10^38 - 1, donc, plus que l'UNIQUEIDENTIFFIER !!! (environ 3 fois plus....)

A +

[Waldar]

Changement de pratique.
On fait comment avec une séquence ?
Au passage faudra le dire à Larry (Oracle) que c'est pas pratique son idée de séquence

Une fois que la séquence a été incrémentée - et je suis d'accord cette restriction pourrait être levée :

Code :

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

select masequence.currval from dual;