Nécessité du cache de plansLa génération des plans consomme d'importantes ressources CPU et mémoire. Ce processus comporte trois étapes principales :

• Analyse du texte SQL et construction d'un arbre syntaxique
• Simplification et transformations (réécritures de sous-requêtes, optimisation de jointures)
• Évaluation des coûts basée sur les statistiques

Voici un exemple de requête présentant plusieurs possibilités d'exécution :

SELECT *
FROM X INNER JOIN Y ON x.id = y.ref
INNER JOIN Z ON z.ref = x.id

L'ordre des jointures n'affecte pas le résultat final, mais impacte les performances. Le cache évite de recalculer ces plans pour des requêtes identiques.

Composition du cacheQuatre types d'objets sont conservés :

• Plans compilés : instructions exécutables
• Contextes d'exécution : variables et états spécifiques à chaque session
• Curseurs : état de navigation dans les résultats
• Arbres algébriques : représentations textuelles pour les objets réutilisables

L'illustration suivante présente les types d'objets en mémoire :

Important : Le cache utilise le texte exact comme clé. Préférez toujours la notation avec schéma (Schéma.Table) pour uniformiser les requêtes similaires.

Optimisation via le cacheLes statistiques des plans cachés fournissent des indicateurs précieux :

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
  CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28,2)) AS [Durée totale(s)],
  CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU],
  CAST((qs.total_elapsed_time - qs.total_worker_time) * 100.0 / 
       qs.total_elapsed_time AS DECIMAL(28,2)) AS [% Attente],
  qs.execution_count,
  CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL(28,2)) AS [Durée moyenne(s)],
  SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
    ((CASE WHEN qs.statement_end_offset = -1 
      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
      ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS Requête,
  DB_NAME(qt.dbid) AS Base,
  qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY qs.total_elapsed_time DESC

Limitations de cette méthode :

• Absence des instructions de maintenance (index, statistiques)
• Plan éventuellement éjecté du cache
• Non-prise en compte du coût de compilation

Tensions entre cache et optimisationLe choix du plan varie selon les paramètres utilisés. Examinez cette situation :

-- Paramètre de faible sélectivité
SELECT * FROM Orders WHERE CustomerID = @Param1
-- Paramètre de haute sélectivité
SELECT * FROM Orders WHERE CustomerID = @Param2

Images comparant les différents chemins d'exécution.

Dans les procédures stockées, le cache peut générer des conflits :

• Un premier appel avec paramètre peu sélectif génère un plan adapté aux grandes volumétries
• Un second appel avec paramètre très sélectif réutilise le même plan inadapté

Des situations inverses peuvent également se produire. Cet antagonisme sera approfondi ultérieurement.