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

Mis à jour : 17 juillet 2006

SQL Server 2005 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 2005 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.

Lorsqu'une instruction SQL est exécutée dans SQL Server 2005, le moteur relationnel parcourt d'abord le cache de procédure afin de voir s'il existe un plan d'exécution pour la même instruction SQL. SQL Server 2005 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 2005 en génère un nouveau pour la requête.

SQL Server 2005 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

Il y a une plus forte probabilité que les plans d'exécution individuels soient réutilisés dans une instance de SQL Server 2000 et SQL Server 2005 que dans SQL Server version 6.5 ou antérieure.

Expiration des plans d'exécution

Lorsqu'un plan d'exécution est généré, il reste dans le cache de procédure. SQL Server 2005 fait expirer les anciens plans inutilisés et les supprime du cache lorsqu'il manque d'espace. Chaque plan de requête et contexte d'exécution est associé à un facteur de coût qui indique le coût de compilation de la structure. Les structures de données disposent également d'un champ d'expiration. Chaque fois que l'objet est référencé par une connexion, le champ d'expiration est incrémenté par le facteur coût de la compilation. Par exemple, si un plan de requête a un facteur coût de 8 et qu'il est référencé deux fois, son champ d'expiration prend la valeur 16. Le processus d'écriture différée analyse périodiquement la liste des objets contenus dans le cache de procédure. L'écriture différée décrémente ensuite le champ d'expiration de chaque objet de 1 à chaque analyse. L'âge de notre exemple de plan de requête est décrémenté à 0 après 16 analyses du cache de procédure, jusqu'à ce qu'un autre utilisateur fasse référence au plan. Le processus d'écriture différée supprime l'affectation d'un objet si les conditions suivantes sont remplies :

• Le gestionnaire de mémoire a besoin de mémoire et toute la mémoire disponible est actuellement utilisée.
• Le champ d'expiration de l'objet a pour valeur 0.
• L'objet n'est actuellement référencé par aucune connexion.

Étant donné que le champ d'expiration est incrémenté chaque fois qu'un objet est référencé, les objets auxquels il est régulièrement fait référence n'ont jamais une valeur d'expiration égale à 0 et ne sont donc pas supprimés du cache. Les objets auxquels il n'est pas fait référence fréquemment pourront bientôt être désaffectés, mais ils ne le seront réellement qu'en cas de besoin de mémoire pour d'autres objets.

Recompilation des plans d'exécution

Certaines modifications apportées dans une base de données peuvent entraîner l'inefficacité ou l'invalidité d'un plan d'exécution, en fonction du nouvel état de la base de données. SQL Server détecte les 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 lot entraîne une recompilation, la totalité du lot est recompilée, qu'il soit soumis par le biais d'une procédure stockée, d'un déclencheur, d'un lot d'instructions appropriées ou d'une instruction préparée. Dans SQL Server 2005, seule est recompilée l'instruction qui, dans le lot, déclenche la recompilation. En raison de cette différence, les nombres de recompilations dans SQL Server 2000 et SQL Server 2005 ne sont pas comparables. En outre, il existe davantage de types de recompilations dans SQL Server 2005 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 lot qui n'ont pas besoin d'être recompilées.

L'événement de trace SP:Recompile de Générateur de profils SQL Server signale les recompilations de niveau instruction dans SQL Server 2005. Cet événement de trace signale uniquement les recompilations de lot d'instructions dans SQL Server 2000. En outre, dans SQL Server 2005, 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.

En outre, SQL Server 2005 introduit un nouvel événement de trace appelé SQL:StmtRecompile qui signale les recompilations de niveau instruction. 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 lots d'instructions appropriés, les lots 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.

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 Statistiques d'index.

Voir aussi

Référence

Objet SQLServer:SQL Statistics

Concepts

Gestion des tampons

Autres ressources

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 (en anglais)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications