Introduction :
Comme vu dans le post précèdent, SQL Server dans sa configuration par défaut optimise automatiquement l’exécution des requêtes, notamment en répartissant son exécution à travers différents threads.
Ce post composé de deux parties à pour objectif de présenter les paramètres suivants :
- MAXDOP (partie 1/2),
- Cost Threshold for Parallelism (partie 2/2)
La partie 2/2 de ce post auras pour objectif de configurer SQL afin que seules les requêtes considérées comme volumineuses ne soient traitées en parallèle.
Explication :
Le paramètre “Count Threshold for Parallelism” permet à SQL de ne paralléliser que les requêtes dont le coût d’exécution d’un plan en série (mesuré en secondes) serait plus élevé que la valeur définie.
L’optimisation réalisée par SQL, bien qu’efficace pour les requêtes “volumineuses”, peut se révéler désastreuse pour l’exécution concurrente d’un grand nombre de petites requêtes, il est donc judicieux de revoir la valeur de ce paramètre en fonction du type de charge exercée sur le serveur SQL.
En règle générale, une valeur comprise entre 20 et 50 est un bon début qui doit être affiné par des tests spécifiques à l’application.
Par défaut, la valeur du paramètre “Count Threshold for Parallelism” est de 5, cette valeur provient du temps nécessaire à l’exécution d’une requête sur le poste de “Nick” un employé de Microsoft membre de l’équipe Query Optimizer lorsque le Query Optimizer était en cours de développement pour SQL Server 7.
Nick devait – entre autre – développer la méthode de calcul du coût d’une requête, il a donc utilisé son ordinateur comme étalon, depuis, le coût d’exécution d’une requête estimé par SQL est….le temps d’exécution qu'aurais pris la machine de Nick.
Bien entendu, les machines utilisées ne sont plus les mêmes et les temps d’exécutions sont donc plus courts :
La machine de Nick.
SQL Server ignore la valeur de l'option cost threshold for parallelism dans les cas suivants :
- L'ordinateur ne dispose que d'un seul processeur.
- Un seul UC est disponible pour SQL Server en raison de l'option de configuration affinity mask.
- L'option max degree of parallelism a la valeur 1.
Réalisation
Le paramètre Count Threshold for Parallelism peut être modifié depuis les propriétés de l’instance SQL concernée dans la partie Advanced->Parallelism :
Ou via la requête suivante (en remplaçant VALEUR par la valeur désirée) :
|
sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
sp_configure 'cost threshold for parallelism', VALEUR;
GO
reconfigure;
GO
|
La modification du paramètre Count Threshold for Parallelism est prise en compte sans qu’il n’y a à redémarrer l’instance.
Sources :