Plans d’exécution en cache

Dans la série des actions à mener de temps en temps (difficile de dire si c’est de l’hebdomadaire ou du mensuel …), il convient de jeter un œil sur les DMV concernant le cache d’exécution de SQL Server. Quels sont les plans en cache ? Existe t-il des requêtes faisant l’objet de plusieurs plans d’exécution (si vous avez un compteur Recompilations/sec qui s’affole, il y a fort à parier que vous en trouverez …) ? Bref comment fonctionne ce cache (je vais être bref, ce n’est pas l’objet de ce post) et comment l’exploiter.

Lorsque vous soumettez une requête pour la première fois à SQL Server, celle-ci est d’abord analysée syntaxiquement afin de vérifier que vous avez correctement écrit les mots du langage T-SQL, que le mot FROM est bien présent dans le cadre d’une requête SELECT, bref, des actions très basiques. Passé cette première étape, SQL Server va ensuite entamer une phase de résolution de noms. Et oui, avez-vous bien épelé le nom de vos tables, la colonne “Nom du grand père” fait-elle bien partie de la table “Mes ancêtres les singes”. En somme, une vérification sur l’ensemble des objets va être effectuée. La phase suivante concerne l’optimisation proprement dite de la requête : quel index vais-je utiliser pour trouver l’âge du capitaine si son sabre mesure plus de 88 cm ? SQL Server va rechercher les différents index pouvant répondre à son besoin (vous répondre le plus rapidement possible) et surtout en fonction des statistiques d’index évaluer ou non  la pertinence de celui-ci. Soyons clair, plus votre requête est complexe, plus SQL Server va prendre de temps pour chercher la meilleure solution, et plus le nombre de jointures augmente, plus la difficulté de choisir le bon ordre dans lequel il va faire ces jointures augmente (factorielle … factorielle). A partir de ce constat on sait que SQL Server va trouver un bon plan mais pas forcément le meilleur, reste qu’en général il se débrouille plutôt bien.

Revenons à nos moutons et à nos plans en cache. Une fois la phase d’optimisation terminée, il en résulte un plan d’exécution, que SQL Server va conserver en cache. Il va ensuite utiliser ce plan en cache pour répondre à votre première sollicitation.

Pour toute les sollicitations futures pour la même requête (ou procédure stockée), il va réutiliser ce plan en cache (attention cependant aux statistiques d’index si vos données ont fortement fluctuées). D’où l’idée de maintenir au mieux ce cache. Et là, le principe du cache est assez bien fait. Lors de la phase de compilation/optimisation, un poids a été donné à cette requête : compliquée à compiler, un poids élevé est affecté. Requête toute simple, un poids faible est affecté. Et en fait SQL Server va au départ doubler ce poids. Ensuite, à chaque exécution depuis le cache va augmenter la valeur obtenue de 1 et à chaque passage du lazy writer, toutes les valeurs sont toutes décrémentées de 1. les valeurs égales à 0 sortent du cache. Ainsi, au final, les requêtes couteuses à compiler et celles plus simple mais fréquemment appelées se trouvent dans le cache.  OK, c’était le but du jeu on est pas trop mal.

Maintenant, même si SQL Server est assez intelligent pour interpréter certaines valeurs comme des paramètres de requête, il reste néanmoins sensible à la casse et le moindre écart dans l’écriture de la requête va générer un nouveau plan (je passe sous silence l’option apparue avec SQL Server 2008 : optimize for ad hoc workloads). Une requêtes SELECT * FROM Table1 est différente de SELeCT * FROM Table1 !!!

D’où l’idée de jeter un œil de temps en temps sur ce fameux cache …

Je vous propose :

select cp.cacheobjtype,cp.objtype , cp.usecounts,
       convert(int,paexe.value) as ‘Nb exec’,
          db_name(convert(int,padb.value)) as ‘database’,
          qp.query_plan, est.text
from sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) padb
CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) paexe
CROSS APPLY sys.dm_exec_query_plan ( plan_handle ) qp
CROSS APPLY sys.dm_exec_sql_text (plan_handle) est 
WHERE padb.attribute = ‘dbid_execute’
AND   paexe.attribute = ‘hits_exec_context’
AND convert(int,padb.value) > 4 — BD utilisateur

pour obtenir les plans en cache avec le nombre de fois où ils sont utilisés …

A partir de là, prenez les plans les plus utilisés pour voir s’ils sont vraiment optimums (oups, je m’égare, ce n’est pas d’optimisation dont on traite aujourd’hui).

Intéressez-vous donc aux requêtes ayant un texte similaire. Si vous rencontrez plusieurs fois la même, cela veut dire qu’il y a plusieurs plans pour votre requête.

Vous pouvez aussi utiliser

SELECT sql.text as ‘requete’,COUNT(*) as ‘Nbre plans’
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text (plan_handle) sql
where cp.usecounts <=1
GROUP BY sql.text

éventuellement avec un order by de votre choix pour voir plus facilement les requêtes les plus problématiques d’un point de vue cache.

Si vous vous posez des questions sur une requête bien spécifique :

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N’%Cette satanée requête%’;

Maintenant, lorsque vous avez détecté un problème rien ne vous empêche de vider ce cache, en totalité, partiellement pour une base de données ou même supprimer un plan…

NB : si vous attendrez des réponses aux commentaires que vous laissez ou aux questions que vous posez : pensez à exposer vos coordonnées email, sinon, je n’ai aucun moyen de vous joindre …

Des problèmes de performance ? Vous souhaitez un audit de votre server et de vos bases ?
 

Christophe LAPORTE Consultant – Formateur certifié Microsoft / MVP SQL Server Conseil IT           | Web : http://www.conseil-it.fr  MVP SQL Server | Blog: http://christophelaporteatwork.spaces.live.com