Discussion :

[olivtone]

Bonjour Tout le monde

je voulais savoir si quelqu'un avait eu un retour d'experience concernant :

- Le rajout de plusieurs fichiers sur une BDD au niveau des filegroup et avoir leur retour de performance dessus (surtout au niveau IO )
- l'option PARAMETERIZATION FORCED a ON quels ont été les impacts positives ou négatives sur le sujet?
- l'option max degree of paralelism, Quelle est valeur pour vous correspond le mieux en fonction de vos BDD? et pourquoi ?

Je trouve sur ces sujets a boire et a manger sur ces sujets, mais bon pleins d'avis differents...

merci a tous et bonne soirée

[Boubou2020]

Bonjour Tout le monde

je voulais savoir si quelqu'un avait eu un retour d'experience concernant :

- Le rajout de plusieurs fichiers sur une BDD au niveau des filegroup et avoir leur retour de performance dessus (surtout au niveau IO )
- l'option PARAMETERIZATION FORCED a ON quels ont été les impacts positives ou négatives sur le sujet?
- l'option max degree of paralelism, Quelle est valeur pour vous correspond le mieux en fonction de vos BDD? et pourquoi ?

Je trouve sur ces sujets a boire et a manger sur ces sujets, mais bon pleins d'avis differents...

merci a tous et bonne soirée

POUR La premiére point je pense que le partitionement de vos table et de mettre vos ancien data dans une group de fichier avec un mode READ ONLY ,et de mettre vos données activé sur des group de ficier filegroup surtout sur des secteurs disque s’épater va vous permettre d'améliorer vos performance

[bvesan]

Bonjour,
Ces trois sujets ne sont pas simples à aborder !


Multiplication des fichiers de données:
Dans l'absolu, il est plus "propre" de créer un Filegroup dédié aux données, ceci pour mieux cloisonner les données utilisateur des données système sur une base. Concrètement, certaines bases respectent ce cloisonnement, d'autres non.

En terme de performance, multiplier les fichiers de données (dans un même filegroup ou dans des filegroups différents) n'a de sens que lorsque ces fichiers sont placés sur des partitions NTFS différentes (il n'y a pas de gain de perfromance à attendre lorsque les différents fichiers d'une base sont placés sur la même partition). SQL Server utilise un worker thread pour chaque partition NTFS, les demandes de lecture sur des partitions différentes peuvent donc être traitées en parallèle. Bien entendu, si ces partitions sont sur un même disque physique (ou une même grappe RAID), il est possible que le disque soit saturé en terme de demande d'entrées/sorties et que le fait de paralléliser les demandes de lectures ne permette pas de gagner en performances.
Donc, pour résumer, du point de vue des performances:

• Plusieurs fichiers sur une même partition: pas de gain
• Plusieurs fichiers sur des partitions différentes d'un même disque: gain potentiel mais risque de saturation du disque
• Plusieurs fichiers sur des disques physiques différents: gain potentiel

Utilisation de l'option PARAMETRIZATION à FORCED:
Pas de recette miracle (d'où beaucoup d'avis différents exposés), mais il faut bien comprendre les tenants et aboutissants de cette fonctionnalité.
Par défaut (parametrization à SIMPLE), le plan d'exécution que stocke SQL Server pour une requête n'est valable que pour les valeurs explicitement définies dans la clause where (par exemple: "WHERE COL1 <50"). Le plan sera donc peu réutilisé (un nouveau plan sera compilé pour "WHERE COL1 <51"), donc un plus grand nombre de requêtes sera impacté par les compilations de plans. Mais, a priori, chaque plan sera optimal.
Lorsque parametrization vaut FORCED, le plan d'exécution calculé pour une requête est quasi systématiquement (les exceptions sont listées ici) réutilisable pour toutes les valeurs possibles dans la clause WHERE (donc le plan compilé pour "WHERE COL1<50 AND COL2 = 23" sera par réutilisé pour "WHERE COL1<10000 and COL2 = -1"). Beaucoup moins de requêtes passeront donc par la phase de compilation du plan, mais les plans utilisés ont de plus grand risques de ne pas être optimaux.

Concrètement, l'utilisation de ce paramètre est dangereuse, principalement lorsque les requêtes traitent des données qui ne sont pas équitablement réparties. Ces problèmes de "parameter sniffing" ou "bind variable peeking" peuvent avoir des conséquences désastreuses sur la performance des traitements en base (et ce problème n'est pas dédié à SQL Server, voir par exemple cette anecdote de Tom Kyte sur Oracle). Mon retour d'expérience personnelle sur le sujet est donc: Ne surtout pas utiliser cette option systématiquement, elle ne pourra être envisagée que dans des cas très spécifiques.

Degré de parallélisme:
Ici non plus, pas de recette miracle.

Par défaut, SQL Server va se permettre d'utiliser en parallèle, POUR UNE OPERATION, autant de worker threads que de CPUs logiques mis à disposition (max degree of parallelism à 0). On s'accorde aujourd'hui à dire que sur des machines récentes, sur lesquelles on trouve un grand nombre de CPUs logiques présentés à une instance SQL Server, il est raisonnable de limiter cette valeur (4, 6 ou 8, mais rarement plus). Ceci permet d'éviter un potentiel goulot d'étranglement sur les CPUs.
On s'accorde aussi (quoi que...) sur le fait que désactiver complètement le parallélisme (max degree of parallelism à 1) est une mauvaise idée, et qu'il est intéressant de repousser la limite pour laquelle SQL Server envisagera le parallélisme pour un plan d'exécution (cost threshold for parallelism, largement revu à la hausse, par exemple à 30 ou 50).
Mais ces recommandations restent des bonnes pratiques et la configuration du parallélisme dépend complètement des traitements exécutés sur l'instance, du nombre de CPUs à disposition, et de la consommation observée sur ces CPUs.

Si le profile d'activité de votre instance montre clairement une saturation des CPUs alors que les disques sont peu sollicités (on parle d'activité CPU bound), alors il faut regarder l'impact du parallélisme (attentes CX_PACKET, SOS_SCHEDULER_YIELD), et éventuellement affiner cette configuration (directive MAXDOP pour certaines requêtes, cost threshold for parallelism, max degree of parallelism). Si le profile d'activité montre que le goulot d'étranglement est ailleurs (disque en général, on parle d'activité IO bound), alors inutile de chercher à jouer avec la configuration du parallélisme. C'est en tout cas comme ça que j'aborde le sujet du parallélisme...

[SQLpro]

Je rajouterais quelques éléments :
1) un élément important pour les perf est de mettre "optimize for ad hoc workloads" à 1
2) pour les fichiers il est bon :
d'avoir plusieurs disques INDÉPENDANTS sur un système de stockage DÉDIÉ (par exemple un SAN mutualisé avec des LUNs taillés au couteau dans un agrégat RAID englobant tous les disques est parfaitement contre performant).
d'entrelacer les fichiers de journalisation et les fichiers de données des différentes bases afin de mieux ventiler les IOs. Les uns étant écrits tous en même temps, de manière asynchrone au niveau du CHECKPOINT, les autres étant décrit de manière synchone...)
NOTA : les fichiers de données de la base tempdb doivent aussi être ventilés sur plusieurs disques
3) le parallélisme doit être géré à différents niveau : RAM (nombres de nœuds NUMA), nombre de disques physique pour la ventilation des IOs et nombre de CPU/core. Par exemple si vous avez 2 nœuds NUMA et ventilé les données de chaque base (tempdb incluse) sur 4 fichiers alors le bon parallélisme devrait être situé entre 2 et 4.
Vous devez aussi jouer sur le seuil de cout du déclecnhement du parallélisme, beaucoup trop faible aujourd'hui en standard. Moi je le met entre 12 et 25 au lieu de 5.

Pour tout cela, sur un serveur en prod, vous pouvez faire des essais.
Pour les fichiers, l'outil SQLIOsim, pour le parallélisme prenez les 25 requête les plus couteuses et regardez les STATISTICS IO et TIME en augmentant progressivement le parallélisme (OPTION (MAXDOP...)).

D'autres considérations importantes figurent dans notre livre... Un chapitre entier étant consacré au stockage et un autre à la configuration du serveur...



A +

[olivtone]

Merci Beaucoup a Vous deux de votre retour c'est plus clair dans mon petit esprit