SQL – Etienne ZINZINDOHOUE

Un article de zinzineti    3 Commentaires

Le nombre et le type de processeur influencent la performance d’un serveur. Une fois le serveur SQL mis en place, il est généralement difficile de décider après de changer de type de CPU ou d’en augmenter le nombre; comme on peut le faire avec les autres composants matériel de la machine : mémoire ou disque dur. En cas de dégradation des performances liées à l’utilisation de CPU que faut-il faire ?
Quatre approches de solution :
¤ Checkup des options avancées relatives à la configuration du CPU.
¤ Identifier et optimiser les requêtes les plus consommatrices de CPU : Reécriture des requêtes, création des bons index,..(En général, pour une base de données en production il n’est plus possible de modifier le model de données)
¤ Tester l’effet (ON/OFF) de l’Hyperthreading. L’option ON/OFF de l’Hyperthreading se modifie dans le BIOS.
¤ Envisager la mise en place du « gouverneur de ressources » (introduit depuis SQL SERVER 2008) pour répartir des ressources mémoires et CPU selon la charge de travail.

Commençons d’abord par regarder le nombre de processeurs disponibles sur la machine

 <br />
SELECT cpu_count AS [Nb_cpu_logique]  <br />
,cpu_count/hyperthread_ratio AS [Nb_CPU_Physique] <br />
FROM sys.dm_os_sys_info; <br />

Vérifions ensuite les options avancées relatives à la configuration du CPU.

 <br />
EXEC sp_configure 'affinity mask' <br />
 <br />
EXEC sp_configure 'lightweight pooling' <br />
 <br />
EXEC sp_configure 'max worker threads' <br />
 <br />
EXEC sp_configure 'priority boost' <br />
 <br />
EXEC sp_configure 'lightweight pooling' <br />
 <br />
EXEC sp_configure 'max degree of parallelism' <br />
 <br />
EXEC sp_configure 'cost threshold for parallelism' <br />
 <br />

De façon générale les options avancées CPU doivent être positionnées sur les valeurs par défaut. Un petit commentaires sur ces options.

–> CPU Affinity Mask
——————–
Pour faire du multitâche, l’OS transfère parfois des processus entre différents processeurs.
Cette activité peut réduire les performances de SQL Server du fait des charges système élevées,
puisque chaque cache de processeur est rechargée à chaque transfert de processus.
L’affectation de processeurs à des tâches spécifiques permet d’améliorer les performances
en réduisant les changements de contexte. Cette liaison entre tâche et processeur est
appelée CPU Affinity Mask

Comment configurer CPU Affinity Mask ?
Voici un exemple de configuration de l’Affinity mask pour un système à 8 processeurs

Remarque
————–
Microsoft affirme que cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server.
Évitez d’utiliser cette fonctionnalité dans de nouveaux travaux de développement,
et modifiez dès que possible les applications qui utilisent actuellement cette fonctionnalité.

–> Max Worker Threads
——————–
L’option « Max worker threads » permet de définir le nombre de threads pour les processus SQL Server.
Selon la configuration de la machine, l’attribution d’une valeur spécifique à l’option « Max worker threads » permet d’accroître les performances.
Quelle valeur attribuer à cette option ?
Pour SQL Server 2005 ou SQL Server 2008, affectez la valeur 0 à l’option max worker threads;
SQL Server peut ainsi déterminer automatiquement le nombre correct de threads de travail actifs en fonction des demandes des utilisateurs.

–> Priority Boost
——————–
L’option « priority boost » permet de définir si les processus SQL Server sont prioritaires ou pas.
D’abord rappelons que chaque les processus windows s’exécute avec des niveaux de priotrité différent
4(low) – 7 (normal) – 13 (high) – 24 (real-time).
Les processus ayant un niveau de priorité élévé s’exécutent les premiers et c’est souvent le cas des processus
du système d’exploitation.

La valeur par défaut de l’option « priority boost » est 0, ce qui représente une base de priorité de 7(normal).
Si on affecte la valeur 1 à « Priority Boost » alors SQL Server bénéficie d’une base de priorité de 13(high).

Attention :
———
Une valeur trop élevée à l’option « priority boost », risque d’épuiser les ressources affectées aux fonctions
réseau et aux fonctions du système d’exploitation, ce qui peut entraîner une instabilité de l’OS.

–> Lightweight Pooling
——————–
La valeur par défaut de cette option est 0.
Pour basculer SQL Server bascule en mode de planification fibre, il suffit d’affecter la valeur 1 à l’option « Lightweight Pooling »
Le mode fibre est prévu pour réduire la charge système induite par les basculements excessifs de contexte que l’on rencontre
parfois en environnement multitraitement symétrique.
Ces situations étant rares, le mode fibre améliore rarement les performance. Garder donc la valeur par défaut 0.

–> MAX Degree Of Parallelism (MAXDOP)
——————–
La valeur par défaut est 0; cette valeur 0 indique à SQL Server de déterminer pour un système ayant plusieurs processeurs,
le degré optimal de parallélisme lors de l’exécution d’une instruction (qui correspond au nombre de processeurs utilisés pour l’exécution de l’instruction).
Les requêtes les plus lentes tirent généralement parti des plans parallèles.
Pour supprimer le parallélisme il suffit d’affecter la valeur 1 à l’option ‘Max degree of parallelism’

–> Cost threshold for parallelism
——————–
La valeur par défaut est 5.
Cette option définit le seuil au-delà duquel SQL Server crée et exécute des plans parallèles pour des requêtes.
Si l’option max degree of parallelism a la valeur 1 la valeur de l’option « cost threshold for parallelism » est ignorée.

Identifier et optimiser les requêtes les plus consommatrices de CPU
—————————————————————————————————-
Les DMVs et DMFs permettent d’identifier les requêtes les plus consommatrices de CPU.

 <br />
--======================================================================================  <br />
-- Afficher les 20 requtes les plus consommatrices de CPU  <br />
--======================================================================================  <br />
SELECT TOP 50 total_worker_time/execution_count AS [Avg CPU Time],   <br />
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)  ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text   <br />
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st   <br />
ORDER BY total_worker_time/execution_count DESC;   <br />

Tester ensuite l’Hyperthreading et si possible envisager la possibilité de mise en place du « gouverneur de ressources »
—————————————————————————————————-

——————————————-
Etienne ZINZINDOHOUE
——————————————-