Il y a quelques jours, j’ai eu à faire face à un problème d’occupation CPU élevé  sur un des serveurs SQL du client chez lequel je me trouve. Après quelques investigations, je me suis posé la question suivante : Est ce que le serveur utilise le parallélisme et comment savoir quels lots de requêtes a recours à un traitement en parallèle ?

En regardant de plus prêt je me suis aperçu que le serveur avait recours de façon intensive au parallélisme. L’utilisation de la requête suivante m’a permis de visualiser les lots de requêtes susceptibles d’utiliser un traitement parallèle. Les DMV concernant les plans compilés en cache permettent d’extraire les informations intéressantes :

SELECT
    t.text,
    st.execution_count,       
    st.total_worker_time / st.execution_count / 1000 AS avg_worker_time_in_milliseconds,
    st.total_elapsed_time / st.execution_count / 1000 AS avg_elapsed_time_in_milliseconds,
    st.total_physical_reads,
    st.total_logical_reads,
    st.total_logical_writes,
    ep.query_plan
FROM sys.dm_exec_cached_plans cp   
INNER JOIN sys.dm_exec_query_stats st
ON cp.plan_handle = st.plan_handle
CROSS APPLY sys.dm_exec_query_plan(st.plan_handle) ep
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) t
WHERE cp.cacheobjtype = ‘Compiled Plan’
AND ep.query_plan.value(‘declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan »;max(//p:RelOp/@Parallel)’, ‘float’) > 0
ORDER BY st.execution_count DESC

A la 1ère utilisation, j’avais ordonné les résultats par utilisation CPU (total_worker_time) pensant pouvoir faire un classement des requêtes ayant recours à un traitement parallèle et les plus consommatrices de temps CPU. Cependant les valeurs d’utilisation CPU fournis par la DMV sys.dm_exec_query_stats est incorrecte pour les requêtes utilisant un plan d’exécution parallélisé.

Pour quelle raison ? L’exécution sérialisée d’une requête dans la DMV sys.dm_exec_query_stats reporte le temps passé par le thread qui exécute cette requête (temps kernel et temps utilisateur). A contrario l’exécution parallèle d’une requête ne reporte pas le temps passé par tous les threads concernés dans cette même DMV. (Pour une démonstration voir le site de www.sqlworkshop.com)

Mon choix s’est donc porté sur un classement par importance d’utilisation de ces plans de requête (execution_count)  pour pouvoir faire un 1er diagnostic mais il serait intéressant de trouver comment mesurer de façon précise l’utilisation CPU pour une requête concernée par un traitement parallèle. La question reste donc ouverte !!!

++

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon