• Article

Mise en mémoire cache et réutilisation du plan d'exécution

SQL Server dispose d'un pool de mémoire utilisé pour stocker les plans d'exécution et les tampons de données. Le pourcentage de ce pool alloué aux plans d'exécution ou aux tampons de données évolue de façon dynamique en fonction de l'état du système. La part du pool de mémoire utilisée pour stocker les plans d'exécution est appelée le cache de procédure.

Les plans d'exécution de SQL Server comprennent les composants principaux suivants :

  • Plan de requête

    Le corps du plan d'exécution est une structure de données réentrante et en lecture seule qui peut être utilisée par un nombre quelconque d'utilisateurs. Il constitue le plan de requête. Aucun contexte d'utilisateur n'est stocké dans le plan de requête. Il n'y a jamais plus d'une ou deux copies du plan de requête en mémoire : une copie pour toutes les exécutions en série et une autre pour toutes les exécutions en parallèle. La copie en parallèle couvre toutes les exécutions en parallèle, indépendamment de leur degré de parallélisme.

  • Contexte d'exécution

    Chaque utilisateur exécutant actuellement la requête dispose d'une structure de données qui contient les données spécifiques à son exécution, telles que la valeur des paramètres. Cette structure de données constitue le contexte d'exécution. Les structures de données du contexte d'exécution sont réutilisées. Si un utilisateur exécute une requête et qu'une des structures n'est pas en cours d'utilisation, elle est réinitialisée avec le contexte du nouvel utilisateur.

Contexte d'exécution, requête identique, littéraux différents

Lorsqu'une instruction SQL est exécutée dans SQL Server, le moteur relationnel parcourt d'abord le cache de procédures afin de voir s'il existe un plan d'exécution pour la même instruction SQL. SQL Server réutilise le plan existant qu'il trouve, évitant ainsi la recompilation de l'instruction SQL. S'il n'existe aucun plan d'exécution, SQL Server en génère un nouveau pour la requête.

SQL Server dispose d'un algorithme efficace qui permet de trouver un plan d'exécution existant pour toute instruction SQL spécifique. Dans la plupart des systèmes, les ressources minimales utilisées par cette analyse sont inférieures à celles économisées par la réutilisation de plans existants au lieu de la compilation de toutes les instructions SQL.

Les algorithmes qui permettent d'associer de nouvelles instructions SQL à des plans d'exécution inutilisés existants en mémoire cache imposent que toutes les références d'objets soient complètes. Par exemple, la première de ces instructions SELECT n'est pas associée à un plan existant, contrairement à la seconde :

SELECT * FROM Contact

SELECT * FROM Person.Contact

Suppression des plans d'exécution du cache de procédures

Les plans d'exécution demeurent dans le cache de procédures tant qu'il y a suffisamment de mémoire pour les stocker. En cas de sollicitation élevée de la mémoire, le Moteur de base de données utilise une approche basée sur les coûts pour identifier les plans d'exécution à supprimer du cache de procédures. Pour prendre une décision basée sur les coûts, le Moteur de base de données augmente et diminue une variable de coût actuel pour chaque plan d'exécution en fonction des facteurs suivants.

Lorsqu'un processus utilisateur insère un plan d'exécution dans le cache, il définit le coût actuel de sorte qu'il soit égal au coût de compilation de la requête d'origine ; pour les plans d'exécution ad hoc, le processus utilisateur définit le coût actuel à zéro. Ensuite, chaque fois qu'un processus utilisateur fait référence à un plan d'exécution, il réinitialise le coût actuel au coût de compilation d'origine ; pour les plans d'exécution ad hoc, le processus utilisateur augmente le coût actuel. Pour tous les plans, la valeur maximale du coût actuel correspond au coût de compilation d'origine.

En cas de sollicitation élevée de la mémoire, le Moteur de base de données répond en supprimant des plans d'exécution du cache de procédures. Pour identifier les plans à supprimer, le Moteur de base de données examine plusieurs fois l'état de chaque plan d'exécution et supprime des plans lorsque leur coût actuel est nul. Un plan d'exécution avec un coût nul n'est pas supprimé automatiquement en cas de sollicitation élevée de la mémoire ; il est supprimé uniquement lorsque le Moteur de base de données examine le plan et que son coût actuel est nul. Lors de l'examen d'un plan d'exécution, le Moteur de base de données pousse le coût actuel vers la valeur zéro en réduisant le coût actuel si aucune requête n'utilise actuellement le plan.

Le Moteur de base de données examine plusieurs fois les plans d'exécution jusqu'à ce que le nombre de plans supprimés soit suffisant pour répondre aux exigences mémoire. En cas de sollicitation élevée de la mémoire, un plan d'exécution peut voir son coût augmenter et diminuer à plusieurs reprises. Lorque la mémoire n'est plus sollicitée, le Moteur de base de données cesse de réduire le coût actuel des plans d'exécution inutilisés et tous les plans d'exécution demeurent dans le cache de procédures, même si leur coût est égal à zéro.

Le Moteur de base de données utilise le moniteur de ressources et des threads utilisateur pour libérer de la mémoire dans le cache de procédures en réponse à une sollicitation élevée de la mémoire. Le moniteur de ressources et les threads utilisateur peuvent examiner les plans exécutés simultanément afin de réduire le coût actuel de chaque plan d'exécution inutilisé. Le moniteur de ressources supprime des plans d'exécution du cache de procédures en cas de sollicitation élevée globale de la mémoire. Il libère de la mémoire afin d'appliquer les stratégies en matière de mémoire système, de mémoire de processus, de mémoire du pool de ressources et de taille maximale pour tous les caches.

La taille maximale de tous les caches dépend de la taille du pool de mémoires tampons et ne peut pas dépasser la quantité maximale de mémoire du serveur. Pour plus d'informations sur la configuration de la quantité maximale de mémoire du serveur, consultez le paramètre max server memory dans sp_configure (Transact-SQL).

Les threads utilisateur suppriment des plans d'exécution du cache de procédures en cas de sollicitation élevée de la mémoire d'un cache unique. Ils appliquent les stratégies de taille maximale de cache unique et de nombre maximal d'entrées de cache unique.

Les exemples suivants illustrent les plans d'exécution qui sont supprimés du cache de procédures :

  • Un plan d'exécution est fréquemment référencé de sorte que son coût n'est jamais égal à zéro. Le plan reste dans le cache de procédures et n'est pas supprimé tant qu'il n'y a pas de sollicitation de la mémoire et que le coût actuel n'est pas égal à zéro.

  • Un plan d'exécution ad hoc est inséré ; il n'est plus référencé tant que la mémoire n'est pas sollicitée de manière élevée. Dans la mesure où les plans d'exécution ad hoc sont initialisés avec un coût actuel égal à zéro, lorsque le moteur de base de données examine le plan d'exécution, il constate que le coût actuel est égal à zéro et supprime le plan du cache de procédures. Le plan d'exécution ad hoc reste dans le cache de procédures avec un coût actuel égal à zéro en l'absence de sollicitation de la mémoire.

Pour supprimer manuellement un seul plan ou l'ensemble des plans du cache, utilisez DBCC FREEPROCCACHE (Transact-SQL).

Recompilation des plans d'exécution

Certaines modifications apportées à une base de données peuvent rendre un plan d'exécution inefficace ou non valide, en fonction du nouvel état de la base de données. SQL Server détecte ce type de modifications et marque le plan comme non valide. Il faut donc recompiler un nouveau plan pour la prochaine connexion qui exécute la requête. Les conditions qui provoquent l'invalidité d'un plan sont les suivantes :

  • Les modifications apportées à une table ou à une vue référencée par la requête (ALTER TABLE et ALTER VIEW).

  • Les modifications apportées à des index utilisés par le plan d'exécution.

  • Les mises à jour de statistiques utilisées par le plan d'exécution, générées explicitement à partir d'une instruction, telle que UPDATE STATISTICS, ou automatiquement.

  • La suppression d'un index utilisé par le plan d'exécution.

  • Un appel explicite de sp_recompile.

  • Un nombre important de modifications de clés (générées par les instructions INSERT ou DELETE des autres utilisateurs qui modifient une table référencée par la requête).

  • Pour les tables contenant des déclencheurs, si le nombre de lignes des tables inserted ou deleted augmente de manière significative.

  • L'exécution d'une procédure stockée à l'aide de l'option WITH RECOMPILE.

La plupart des recompilations sont nécessaires pour que les instructions soient correctes ou pour obtenir des plans d'exécution de requête potentiellement plus rapides.

Dans SQL Server 2000, chaque fois qu'une instruction d'un traitement entraîne une recompilation, la totalité du traitement est recompilée, qu'il soit soumis par le biais d'une procédure stockée, d'un déclencheur, d'un traitement appropriées ou d'une instruction préparée. Dans SQL Server 2005 et les versions ultérieures, seule l'instruction qui déclenche la recompilation dans le lot est recompilée. En raison de cette différence, les comptes de recompilations dans SQL Server 2000 et dans les versions ultérieures ne sont pas comparables. En outre, il existe davantage de types de recompilations dans SQL Server 2005 et dans les versions ultérieures en raison de son ensemble de fonctionnalités étendu.

La recompilation de niveau instruction améliore les performances car, dans la plupart des cas, un nombre réduit d'instructions est à l'origine des recompilations et de leurs effets secondaires, en termes de temps processeur et de verrous. Par conséquent, ces effets épargnent les autres instructions du traitement qui n'ont pas besoin d'être recompilées.

L'événement de trace SP:Recompile de SQL Server Profiler signale les recompilations au niveau des instructions. Cet événement de trace signale uniquement les recompilations de traitement dans SQL Server 2000. En outre, la colonne TextData de cet événement est remplie. Par conséquent, il n'est plus nécessaire, comme cela était le cas dans SQL Server 2000, de tracer SP:StmtStarting ou SP:StmtCompleted pour obtenir le texte Transact-SQL à l'origine de la recompilation.

L'événement de trace SQL:StmtRecompile signale les recompilations au niveau des instructions. Cet événement de trace permet d'effectuer le suivi des recompilations et de les déboguer. Tandis que SP:Recompile est généré uniquement pour les procédures stockées et les déclencheurs, SQL:StmtRecompile est généré pour les procédures stockées, les déclencheurs, les traitements d'instructions appropriés, les traitements d'instructions exécutés à l'aide de sp_executesql, les requêtes préparées et le code SQL dynamique.

La colonne EventSubClass de SP:Recompile et de SQL:StmtRecompile contient un code d'entier qui indique le motif de la recompilation. Le tableau suivant décrit chaque numéro de code.

Valeur EventSubClass

Description

1

Schéma modifié.

2

Statistiques modifiées.

3

Compilation différée.

4

Option SET modifiée.

5

Table temporaire modifiée.

6

Ensemble de lignes à distance modifié.

7

Autorisation FOR BROWSE modifiée.

8

Environnement de notification de requête modifié.

9

Vue partitionnée modifiée.

10

Options de curseur modifiées.

11

OPTION (RECOMPILE) demandée.

[!REMARQUE]

Lorsque l'option de base de données AUTO_UPDATE_STATISTICS a pour valeur ON, les requêtes sont recompilées lorsqu'elles ciblent des tables ou des vues indexées dont les statistiques ont été mises à jour ou dont les cardinalités ont sensiblement évolué depuis la dernière exécution. Ce comportement s'applique aux tables temporaires, aux tables définies par l'utilisateur standard, ainsi qu'aux tables inserted et deleted créées par des déclencheurs DML. Si les performances des requêtes sont affectées par des recompilations excessives, vous pouvez attribuer à ce paramètre la valeur OFF. Lorsque l'option de base de données AUTO_UPDATE_STATISTICS a pour valeur OFF, aucune recompilation ne se produit en fonction des statistiques ou des modifications de cardinalité, à l'exception des tables inserted et deleted qui sont créées par des déclencheurs DML INSTEAD OF. Comme ces tables sont créées dans tempdb, la recompilation de requêtes qui accèdent à ces tables dépend du paramétrage de AUTO_UPDATE_STATISTICS dans tempdb. Dans SQL Server 2000, la recompilation des requêtes se poursuit en fonction des modifications de cardinalité apportées aux tables inserted et deleted créées par des déclencheurs DML, même si ce paramètre a pour valeur OFF. Pour plus d'informations sur la désactivation de l'option AUTO_UPDATE_STATISTICS, consultez Utilisation des statistiques pour améliorer les performances des requêtes.

Historique des modifications

Mise à jour du contenu

Mise à jour de la section relative à la suppression des plans d'exécution du cache de procédures.