Discussion :

[Pfeffer]

Bonjour à tous,

Je dois faire l'update d'une table et j'ai quelques soucis de performance, cela fait déja 8h que mon script tourne et c'est toujours pas fini.

Pour info:
- La table contient plusieurs dizaines de millions de données, partitionnée avec une notion de date.
- Mon update est bien structuré avec une clause Where. C'est un update, tout ce qu'il y a de plus basique.
- La table est convenablement indexée et ne possède aucun trigger.
- Par contre sur ma table, j'ai une douzaine de vue qui pointent dessus.

Questions:
- J'ai lu dans certains forums, qu'il faudrait désactiver les index, et ensuite les reconstruire. Qu'en pensez vous ? Est ce que je vais vraiment gagner en temps ?
- Est ce qu'il faut que je dissocie mes vues et le recrée après mon update ?

Merci pour vos avis.

[Michel.Priori]

bonsoir Pfeffer,

La restriction
1- porte sur quel % de la volumétrie (à la louche) ?
2- porte au moins en partie sur l'information de partition ?

Le temps d'acquisition des verrous peut être long (8 heures c'est un peu beaucoup) car les select, même par le biais des vues posent des verrous, à moins d'utiliser le niveau d'isolation SNAPSHOT.
Que disent les compteurs de performances à ce propos ?
Toutes les partitions sont libres ?

Pour les bonnes pratiques : tout dépend du % de lignes mises à jour.
Si on modifie 1 ligne, il va de soit qu'on ne touche pas aux contraintes - de check -de FK (- d'unicité aussi mais c'est plus complexe sous SQL server de ne pas perdre l'index)

En tous les cas, supprimer un index pour faire un chargement/une modification doit bien s'évaluer : si vous mettez 8 heures pour faire un update restreint, il vous faudra combien de temps pour relire toutes les lignes, trier les valeurs afin de pouvoir construire l'index supprimé ?

Je veux bien entendre qu'après une modification massive il faille reconstruire (rebuild) les index mais les scénarios pour lesquels la suppression de l'index suivi de sa reconstruction est la solution gagnante ne sont pas nombreux.

Faut pas toujours croire ce qu'on trouve sur internet

[Pfeffer]

Bonjour,

Je partage totalement votre avis sur la question des index.
Le REBUILD risque de prendre beaucoup de temps et je n'aurai rien gagné.

1- porte sur quel % de la volumétrie (à la louche) ?

L'update porte sur une partition qui contient 471.693.339 lignes.

2- porte au moins en partie sur l'information de partition ?

Oui je retrouve bien la notion de "date" dans mon Where, la même que celle que j'utilise pour créer ma partition

Le temps d'acquisition des verrous peut être long (8 heures c'est un peu beaucoup) car les select, même par le biais des vues posent des verrous, à moins d'utiliser le niveau d'isolation SNAPSHOT.
Que disent les compteurs de performances à ce propos ?
Toutes les partitions sont libres ?

De quels compteur de performances parle-t-on ?
Vous entendez quoi par partition libre ?

[aieeeuuuuu]

Bonjour,

concernant les vues, si elles sont indexées, alors même remarque que ci-dessus pour les indexes (comparer le cout suppression/reconstruction d'index par rapport au surcout de mise à jour avec index)

si elle ne sont pas indexées : aucun impact sur la mise à jour, donc on peut les laisser.

Peut-on avoir la structure de la table (avec indexes) ainsi que la requête UPDATE ?

Avez-vous vérifié qu'il ne s'agit pas simplement de verrous posés sur les données à mettre à jour ?

[Pfeffer]

Voici la structure de ma table, avec les index et les partitions.
Et voici un exemple d'update chronophage.

Code :

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

1
2
3
4
 
UPDATE gtm_crb_consolide_pt10
      SET puissance_arenh = NULL
    WHERE year (dateadd (minute, -10, crb.datepoint)) = @iYear

[aieeeuuuuu]

votre clause WHERE empêche l'utilisation d'indexes telle qu'elle est écrite.

Il faudrait la réécrire de telle sorte à ne pas faire de calcul sur la colonne datepoint

Code :

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

1
2
3
 
WHERE datepoint >= @debut
AND datepoint <@fin

L'update porte sur une partition qui contient 471.693.339 lignes

Combien de lignes remplissent la condition year (dateadd (minute, -10, crb.datepoint)) = @iYear.

Comme vous ne mettez à jour que la colonne puissance_arenh, seuls les indexes contenant cette colonne sont éventuellement à désactiver le temps de la mise à jour.

[SQLpro]

Deux choses TRES IMPORTANTES pour les UPDATES en masse :
1) dimensionnez correctement votre journal de transaction pour absorber la totalité de la charge… Sinon vous allez passer la majorité du temps à rabouter le fichier du JT
2) découpez votre requête UPDATE en "paquets" pour éviter une saturation du cache. Ceci peut être fait à l'aide d'un script du genre ;
WHILE EXISTS(SELECT …)
UPDATE TOP(100000) MaTable
SET …

A +

[Michel.Priori]

Remarques sur le partitionnement :

1- Partitionnement sur date
En général c'est fait pour avoir un stockage "rotatif" sur un nombre de périodes glissantes constant.
Dans ce cas il convient que les index soient "alignés".

2- Nombre de partitions
Selon la version de SQL le nombre maximum évolue.
Faire des partitions plus "serrées" que le besoin "normal" (ici, le besoin : l'année, partitionnement au mois voire à la semaine) permet de bénéficier de méthodes d'accès concurrentielles (plus ou moins drastique en fonction de la version et de l'édition ; reste une bonne pratique)

3- Le stockage des partitions
Il est recommandé de ne pas utiliser PRIMARY pour les données utilisateur.
Il est recommandé d'allouer différents groupes de fichiers pour les partitions

[Pfeffer]

Que pensez vs de créer un index sur l'année en faisant:
Create index year_index on year(datepoint)

[Michel.Priori]

Create index year_index on year(datepoint)

Quel est l'intérêt ?
Est-ce que l'index est réellement ignoré pour vos traitement (voir le plan d’exécution) ?

Le fait d'ajouter un index va pénaliser les écritures et ajouter du volume à backuper.
La colonne datepoint est déjà indexée
Est-que la solution de aieeeuuuuu n'est réellement pas envisageable ?

Ce qui me gène par rapport à la requête et face au partitionnement, c'est la soustraction de 10 minutes.
Potentiellement ça ramène des lignes de début 2019 en 2018, du coup ça oblige à parcourir les 2 partitions et vu qu'à elles 2 elles représentent 80% du nombre de lignes... ça peut être long !

Encore une fois si vous pouvez faire quelque chose, il me semble important -a minima- de revoir la stratégie de partitionnement (groupes de fichiers & nombre de partition & alignement des index sur ces mêmes partitions).
Selon la version et l'édition de SQL et des contraintes associées voir si le columnstore est envisageable, et sinon, étudier si la compression peut être utile.

[Pfeffer]

Alors plusieurs plans d'action que j'envisage dès ce lundi:

- Revoir le schéma de partition pour éventuellement passer sur une granularité mensuelle. 12 partions par année, sachant qu'on aura au max 5 ans glissantes, donc 60 partitions. Est ce qu'un schéma ça se change facilement ? Sur une table de 70Go ?

- Ajouter des filegroups supplémentaires, quelle strategie adoptée ? 1 filegroup pour les données de M-12 jusqu'à M+x et un filegroup avec les données archivees beaucoup moins sollicitées, réparties physiquement sur 2 disque dur différents.

- Eviter le year() dans mes WHERE et vérifier mes plans d'exécution

[Michel.Priori]

Bonsoir,

Alors plusieurs plans d'action que j'envisage dès ce lundi:

- Revoir le schéma de partition pour éventuellement passer sur une granularité mensuelle. 12 partions par année, sachant qu'on aura au max 5 ans glissantes, donc 60 partitions. Est ce qu'un schéma ça se change facilement ? Sur une table de 70Go ?

Faire ça sur un serveur de qualification et extrapoler pour le serveur de production.
Trop de facteurs influent sur la rapidité.
Prévoir une fenêtre de maintenance.

J'insiste lourdement :
1- Aligner tous les index
2- Si la version le permet, utiliser la compression de données.

- Ajouter des filegroups supplémentaires, quelle strategie adoptée ? 1 filegroup pour les données de M-12 jusqu'à M+x et un filegroup avec les données archivees beaucoup moins sollicitées, réparties physiquement sur 2 disque dur différents.

1- créer 1 FG qui sera le nouveau FG par défaut (et ainsi ne plus utiliser PRIMARY)
2- créer un minimum de 60 FG, 1 par partition. PAS de migration de lignes d'un FG à l'autre.
3- La conversation du nombre de fichiers est dépendante de sous système disque (DAS, NAS, SAN, ...) et de ce qu'on peut en espérer.
Un script sera le bien venu.
Encore une fois les tests des scripts ne se font pas sur le serveur de production.

- Eviter le year() dans mes WHERE et vérifier mes plans d'exécution

De manière générale, vérifier les plans d'execution

[Pfeffer]

1- Aligner tous les index

Par contre, je ne comprends pas trop bien ce que veut dire "aligner tous les index".
Un peu plus de précision ?

[SQLpro]

Si votre table est partitionnée, alors tous vos index doivent aussi être alignés.... Problème cela nécessite généralement de modifier la PK si elle ne possède pas le critère de partitionnement comme premier élément de la clef.

A +

[Pfeffer]

Ok dans ce cas c'est bon. Ma PK est bien aligné avec la partition.

Par contre si je comprends bien, vu que ma partition est "glissante", cela veut dire que chaque année je dois modifier le schéma de partition pour que l'année courante pointe sur "PRIMARY" ?

[Michel.Priori]

Bonsoir,

Un petit tour sur la doc me semble important :
index aligné : https://docs.microsoft.com/fr-fr/sql...#aligned-index

Le stockage des index rentre dans la conversation des périodes glissantes.
Si vous maintenez 20 mois de données, vous avez 2 choix :
- tous les jours faire migrer les lignes d'une partition à une autre (pas bien rapide tout ça) et pour le lignes qui dépassent la période DELETE
- à chaque période (schedule) on crée une nouvelle partition et on supprime l'ancienne (beaucoup mieux)

Le scénario conseillé est le 2ieme.
Du coup la table est un sorte d'assemblage de lot de lignes. En fin de période on supprime le lot (et non pas les lignes) c'est super rapide !
MAIS si les index ne sont pas alignés on perd une grosse partie du bénéfice.

[hmira]

En complément de ce qui a été dit, à juste titre, par SQLPro, sur l'alignement des index

....cela nécessite généralement de modifier la PK si elle ne possède pas le critère de partitionnement comme premier élément de la clef..

Oui tout à fait, et qui dit modification de la PK, dit modification des FK référençant la dite PK. Ce qui dans la pratique, et selon le modèle de données, risque de poser d'autres problèmes, etc. Bref, le partitionnement et l'alignement ou non des index NC sont des sujets délicats et qui réservent bien des surprises !

A+

[Michel.Priori]

Oui tout à fait, et qui dit modification de la PK, dit modification des FK référençant la dite PK. Ce qui dans la pratique, et selon le modèle de données, risque de poser d'autres problèmes, etc.

Et oui.
On notera qu'à ce jeux là c'est Oracle qui gagne avec le partitionnement par référence (https://docs.oracle.com/cd/B28359_01...n.htm#CACIHDII)
On attendra que M$ comprenne le problème

En l'occurrence et d'après les informations fournies, les 4 index présents ont bien la colonne "datepoint" dans leur définition.
Aurais-je raté un truc ?

Ah, si !
La compression

[Pfeffer]

Le partitionnement est en cours ... bien évidemment sur l'environnement de DEV !! Je me pose une question ... le fait d'avoir partitionné sur les mois, que va t-il se passer lorsque je vais faire une requête sur une année ?

Est ce que d'un point de vue performance je serai aussi rapide par rapport à l'ancienne méthode de partitionnement ou je faisais une partition par an ?

[Michel.Priori]

Est ce que d'un point de vue performance je serai aussi rapide par rapport à l'ancienne méthode de partitionnement ou je faisais une partition par an ?

Il est utile pour les autres internautes de faire des retours d'expérience