Introduction :
Dans mon précédent article, je suis resté sur le fait que je ne pouvais indexer des tables avec un grand nombre de partitions.
Je parle ici de la résolution 'définitive' du problème.
Rappel du problème :
Lors de création d'index j'obtenais systématiquement une erreur 701 mémoire système insuffisante pour éxécuter cette opération.
1 ère phase de résolution :
J'ai d'abord essayé de comprendre le problème, et , en l'absence de documentations et malgré de nombreuses recherches sur le Web, j'ai du me résoudre à procéder par une méthode de mesurses et déductions.
- La création d'un index demande d'autant plus de mémoire qu'il y a de partitions
- La création d'un index demande d'autant plus de mémoire qu'il y a de lignes
- La création d'un index demande d'autant plus de mémoire que la taille des clés est importante
Cela peut se résumer par une formule du style :
Mémoire = RP * NbePartitions + RL * NbeLignes * TailleClé
RP et RL étant des constantes que j'ignore (n'ayant pas développé le process de create index) mais, sur mon système,, il semble que RP soit voisin de 20 Mo.
RL me semble moins important dans la consommation de mémoire.
Tout ceci avec un index partitionné comme la table (aligné donc).
Voici quelques mesures faites (sur mon portable 4 Go de RAM bi processeur) avec une table de 2 millions de lignes :
Nbe partitions | Mémoire consommée par create index | Remarque |
1 | < 900 Mo | Index non partitionné sur groupe PRIMARY |
31 | 927 Mo | |
62 | 1300 Mo | |
100 | 2100 Mo | J'ai du passer le serveur en mode AWE pour atteindre cela |
366 | Plante | Nos estimations sont de 8 Go de RAM donc beaucoup plus que ce dont je dispose sur mon portable |
Pour passer en mode AWE .
EXEC sys.sp_configure N'awe enabled', N'1'
GO
RECONFIGURE
WITH
OVERRIDE
GO
sp_configure
go
sp_configure
'min server memory', 1024
RECONFIGURE
GO
sp_configure
'max server memory', 3072
RECONFIGURE
GO
Hélas ! le mode AWE ne permet pas de dépasser les 2 Go (environ) alloués à la création de l'index.
Par ailleurs, j'ai du toucher le paramètre de mémoire reservée aux index ainsi :
sp_configure
'index create memory (KB)',100000
reconfigure
sp_configure
'index create memory (KB)'
Il m' a donc fallu trouver une autre solution (l'objectif client est de 600 millions de lignes sur 366 partitions)
2ème phase une solution :
J'ai donc du me replier sur cette solution :
- Table partitionnée sur les 366 groupes, ainsi que l'index clustered
- Index non partitionné mais défini sur un file group bien séparé des données
Et là, une 1ère mesure de performances ( sur la machine client 48Go de RAM, 16 processeurs) donne ceci
Creéation des index sur 2 millions de lignes, 366 partitions :
Nbe de cœurs utilisés maxdop | Temps |
1 | 52 secondes |
2 | 40 secondes |
4 | 31 secondes |
8 | 17 secondes |
16 | 12 secondes |
On voit clairement, que, quand la RAM est bien allouée, les performances sont très acceptables.
Passons aux 600 millions de lignes :
SQL Server \endash Temps d'exécution :
, Temps UC = 22607783 ms, temps écoulé = 5895397 ms.
SQL Server \endash Temps d'exécution :
, Temps UC = 22607861 ms, temps écoulé = 5895571 ms.
Soit 1heure 38 minutes pour un index : normal compte-tenu de la volumétrie….
Mesures de performances :
Comme prévisible, les lectures sont plus rapides avec cette stratégie de partitionnement :
On va d'abord dans l'index non clustered, non partitionné puis dans le cluster qui lui est partitionné : donc un accès sélectif à la partition.
Par contre, les mises à jour peuvent être pénalisées car gérer des index plus gros peut coûter…
Conclusion :
Une grosse galère qui prend fin avec une solution hybride qui se rapproche de ce que font les DBA Oracle…