DOC

Batch Compilation, Recompilation, and Plan Caching Issues in SQL

By Patrick Walker,2014-04-18 00:55
6 views 0
Batch Compilation, Recompilation, and Plan Caching Issues in SQL

Compilation par lots, recompilation et mise en cache des plans dans

    SQL Server 2005

    Par Arun Marathe Publication : juillet 2004

    Résumé : Ce document explique comment mettre en cache et réutiliser les lots dans SQL Server 2005 et propose des pratiques

    recommandées pour réutiliser au mieux les plans mis en cache. Il décrit également certains scénarios impliquant une recompilation

    des lots et aborde les pratiques recommandées pour réduire ou éliminer les recompilations inutiles.

    Sur cette page

    Objectif de ce document

    Recompilations : définition

     Comparaison de la recompilation dans SQL Server 2000 et dans SQL Server 2005

     Mise en cache du plan

     Guide pour la suite du document

     Réutilisation du plan de requête

     Causes des recompilations

     Compilations, recompilations et concurrence d'accès

     Compilations, recompilations et détection des paramètres

     Identification des recompilations

     Outils et commandes

     Conclusion

     Annexe A : Dans quels cas SQL Server 2005 n'applique pas l'autoparamétrage des requêtes ?

    Objectifs de ce document technique

    Ce document technique a plusieurs objectifs. Il explique comment mettre en cache et réutiliser les lots dans SQL Server 2005 et

    présente les pratiques recommandées pour réutiliser au mieux les plans mis en cache. Il décrit également les scénarios dans lesquels

    les lots sont recompilés et propose des pratiques recommandées pour réduire ou éliminer les recompilations inutiles. Il décrit la

    fonction ? recompilation des instructions ? de SQL Server 2005. Nous aborderons également de nombreux outils et utilitaires

    d’observation des processus pour la compilation et la recompilation des requêtes, la mise en cache et la réutilisation des plans. La

    comparaison entre les comportements de SQL Server 2000 et SQL Server 2005 sur laquelle repose la totalité de ce document permet

    au lecteur de mieux comprendre le sujet. Les instructions mentionnées dans le présent document s’appliquent à SQL Server 2000 et

    à SQL Server 2005. Les différences de comportement entre ces deux versions de SQL Server sont mentionnées de manière explicite.

    Ce document s’adresse à trois types de lecteurs :

    Utilisateurs : Personnes chargées d’utiliser, de gérer et de développer des applications pour SQL Server. Les nouveaux utilisateurs avec SQL Server 2005 et ceux qui migrent de SQL Server 2000 y trouveront des informations utiles.

Développeurs : Les développeurs de SQL Server y trouveront des informations de base intéressantes.

    Testeurs et gestionnaires de programme : Ce document sert de cahier des charges de la fonction ? Compilations et recompilations dans SQL Server 2005 ?.

     Haut de page

    Recompilations : définition

    Avant de commencer l'exécution d'une requête, d'un lot, d'une procédure stockée, d'un déclencheur, d'une instruction préparée ou

    d'une instruction SQL dynamique sur un serveur SQL, le lot est compilé dans un plan. Le plan est ensuite exécuté pour produire un

    effet ou des résultats.

    Un lot peut contenir une ou plusieurs instructions SELECT, INSERT, UPDATE et DELETE et des appels de procédures stockées, éventuellement entrelacés par des instructions ? colle ? T-SQL ou des structures de contrôle comme SET, IF, WHILE, DECLARE, des instructions DDL comme CREATE, DROP et des instructions d'autorisation comme GRANT, DENY et REVOKE. Un lot peut contenir une définition et utiliser des constructions CLR comme des types, des fonctions, des procédures et des agrégations personnalisées.

    Les plans compilés sont enregistrés dans une portion de la mémoire du serveur SQL nommée cache de plan. Le cache de plan est

    exploré en vue de réutilisations possibles. Si on réutilise un plan pour un lot, on peut éviter les coûts de compilation. À noter que,

    dans la littérature consacrée au serveur SQL, on retrouve le terme de ? cache de procédure ? en lieu et place du terme ? cache de

    plan ? utilisé ici. ? Cache de plan ? est une expression plus précise, car le cache de plan ne stocke pas uniquement les plans de

    requêtes des procédures stockées.

    En langage SQL Server, la compilation mentionnée dans le paragraphe précédent est parfois improprement appelée ? recompilation ?, bien que la procédure soit une simple ? compilation ?.

    Définition de la recompilation : supposons qu’un lot a été compilé en une série d’un ou plusieurs plans de requêtes. Avant toute

    exécution d’un des plans de requêtes sur le serveur SQL, le serveur contrôle la validité (exactitude) et l'efficacité du plan concerné.

    Si l’un des contrôles échoue, l’instruction correspondant au plan de requête ou le lot complet est compilé à nouveau et un nouveau

    plan est créé, qui peut être différent. Ces compilations portent le nom de ? recompilations ?. Notez qu’il n’est pas nécessaire de mettre en cache les plans de requêtes du lot. En fait, certains types de lots ne sont jamais mis en

    cache, mais peuvent quand même provoquer une recompilation. Prenons l’exemple d’un lot contenant un littéral supérieur à 8 Ko.

    Supposons que ce lot crée une table temporaire et insère ensuite 20 lignes dans cette table. L’insertion de la septième ligne va

    entraîner une recompilation, mais compte tenu de la taille du littéral, le lot n’est pas mis en cache.

    La plupart des recompilations effectuées dans SQL ont une raison d’être. Certaines sont nécessaires pour garantir l’exactitude de

    l’instruction, d’autres pour obtenir des plans d’exécution de requêtes potentiellement plus performants en cas de modifications dans

    la base de données SQL. Cependant, les recompilations ralentissent parfois considérablement les exécutions de lots et il devient alors

    nécessaire d’en réduire la fréquence.

    Haut de page

    Comparatif de la recompilation dans SQL Server 2000 et SQL Server 2005 Lorsqu’un lot est recompilé dans SQL Server 2000, toutes ses instructions sont prises en compte et pas uniquement celle qui a

    déclenché la recompilation. SQL Server 2005 propose une amélioration en ne compilant que l’instruction à l’origine de la recompilation et non le lot complet. Cette fonction de ? recompilation des instructions ? améliore la procédure de recompilation de

    SQL Server 2005 par rapport à SQL Server 2000. En particulier, SQL Server 2005 consomme moins de temps processeur et de mémoire durant les recompilations par lots et exige moins de verrous de compilation.

    Un des avantages de la recompilation des instructions est évident : il n’est plus nécessaire de segmenter une procédure stockée

    longue en plusieurs petites afin de ne pas être pénalisé par la recompilation d’une telle procédure.

    Haut de page

    Mise en cache du plan

    Avant d'aborder les recompilations, ce document consacre une place importante à un sujet connexe et important : la mise en cache du

    plan de requête. Les plans sont mis en cache dans l'éventualité de leurs réutilisations. Si un plan n’est pas mis en cache, la probabilité

    qu’il soit réutilisé est nulle. Il sera compilé à chacune de ses exécutions, ce qui réduit considérablement les performances. Dans certains cas rares, il est souhaitable de ne pas mettre un plan en cache, mais nous y reviendrons ultérieurement. SQL Server peut mettre en cache des plans de requêtes pour de nombreux types de lots. Vous trouverez ci-dessous une liste des différents types, ainsi que les conditions nécessaires à la réutilisation du plan. Notez que ces conditions ne sont pas obligatoirement suffisantes. Vous comprendrez pourquoi un peu plus loin dans le document.

    Requêtes spécifiques. Une requête spécifique est un lot contenant une instruction SELECT, INSERT, UPDATE ou DELETE. 1.

SQL Server exige une correspondance de texte exacte pour deux requêtes spécifiques. Cette correspondance tient compte dans

    les deux cas de la casse et des espacements. Par exemple, les deux requêtes suivantes ne partagent pas le même plan de requête. (Tous les extraits de code T-SQL reproduits ici s’appuient sur la base de données AdventureWorks de SQL Server 2005). SELECT ProductID

    FROM Sales.SalesOrderDetail

    GROUP BY ProductID

    HAVING AVG(OrderQty) > 5

    ORDER BY ProductID

    SELECT ProductID

FROM Sales.SalesOrderDetail

    GROUP BY ProductID

    HAVING AVG(OrderQty) > 5

     ORDER BY ProductId

    Requêtes autoparamétrées Pour certaines requêtes, SQL Server 2005 remplace les valeurs littérales constantes par des 2.

    variables et compile les plans de requêtes. Si une requête se distingue uniquement par les valeurs des constantes, elle

    correspondra à la requête autoparamétrée. En général, SQL Server 2005 applique l'autoparamétrage aux requêtes dont les plans

    sont indépendants des valeurs des littéraux constants.

    L’annexe A contient la liste des types d’instructions qui ne sont pas autoparamétrées par SQL Server 2005. À titre d’exemple d’un autoparamétrage dans SQL Server 2005, les deux requêtes suivantes peuvent réutiliser le plan de

    requête :

    SELECT ProductID, SalesOrderID, LineNumber

     FROM Sales.SalesOrderDetail

    WHERE ProductID > 1000

    ORDER BY ProductID

SELECT ProductID, SalesOrderID, LineNumber

     FROM Sales.SalesOrderDetail

    WHERE ProductID > 2000

    ORDER BY ProductID

    La forme autoparamétrée des requêtes est la suivante :

    SELECT ProductID, SalesOrderID, LineNumber

     FROM Sales.SalesOrderDetail

    WHERE ProductID > @p1

ORDER BY ProductID

    Lorsque les valeurs des littéraux constants qui apparaissent dans une requête peuvent influencer un plan de requête, la requête

    n’est pas autoparamétrée. Les plans de ces requêtes sont mis en cache, mais avec intégration des constantes et sans espace

    réservé de type @p1.

    La fonction ? showplan ? de SQL Server peut servir à déterminer si une requête a été autoparamétrée. Par exemple, la requête peut être soumise en mode ? set showplan_xml on ?. Si le showplan qui en résulte contient des espaces réservés de type @p1 et

    @p2, la requête a été autoparamétrée. Les showplans SQL Server au format XML contiennent également des informations sur

    les valeurs des paramètres au moment de la compilation (modes ? showplan_xml ? et ? statistics xml ?) et au moment de l’exécution (mode ? statistics xml ? seulement).

    Procédure sp_executesql. C’est une des méthodes qui favorise la réutilisation du plan de requête. Avec sp_executesql, un 3.

    utilisateur ou une application peut identifier explicitement les paramètres. Par exemple :

    EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber

    -{}-FROM Production.Product p

    INNER JOIN Production.ProductDescription pd

    ON p.ProductID = pd.ProductDescriptionID

    WHERE p.ProductID = @a', N'@a int', 170

EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber

    FROM Production.Product p INNER JOIN Production.ProductDescription pd

    ON p.ProductID = pd.ProductDescriptionID

    WHERE p.ProductID = @a', N'@a int', 1201

    Vous pouvez spécifier les paramètres multiples en les listant à la suite. Les valeurs actuelles des paramètres suivent les

    définitions. Le système prévoit les possibilités de réutilisation de plan si le texte de la requête est identique (le premier argument après sp_executesql) et en fonction des paramètres suivant le texte de la requête (N'@a int' dans l’exemple ci-dessus). Les valeurs de paramètres (170 et 1201) ne sont pas prises en compte pour les correspondances de texte. De ce fait, dans l’exemple précédent, le plan peut être réutilisé pour les deux instructions sp_executesql.

    Requêtes préparées. Cette méthode, comparable à la méthode sp_executesql, favorise également la réutilisation de plans de 4.

    requêtes. Le texte de lot est envoyé une fois au moment de la préparation. SQL Server 2005 répond en retournant un descripteur

    (handle) qui peut être utilisé pour appeler le lot au moment de l’exécution. Au moment de l’exécution, un descripteur et les valeurs du paramètre sont envoyés au serveur. ODBC et OLE DB prennent en charge cette fonctionnalité via

    SQLPrepare/SQLExecute et ICommandPrepare. Voici un exemple d’extrait de code utilisant ODBC :

    SQLPrepare(hstmt, "SELECT SalesOrderID, SUM(LineTotal) AS SubTotal

    FROM Sales.SalesOrderDetail sod

    WHERE SalesOrderID < ?

    GROUP BY SalesOrderID

    ORDER BY SalesOrderID", SQL_NTS)

    SQLExecute(hstmt)

    Procédures stockées (y compris les déclencheurs). Les procédures stockées sont conçues pour favoriser la réutilisation des 5.

    plans. La réutilisation de plans s’appuie sur le nom de la procédure stockée ou du déclencheur. (Cependant, il n’est pas possible

    d’appeler directement un déclencheur). En interne, SQL Server convertit le nom de la procédure stockée en un code

    d’identification (ID) et la réutilisation qui suit s’effectue sur la base de la valeur de cet ID. La mise en cache du plan et la

    recompilation des déclencheurs diffère légèrement de celles des procédures stockées. Nous reviendrons sur ces différences en

    temps voulu.

    Lorsqu’une procédure est stockée pour la première fois, les valeurs des paramètres fournis avec l’appel d’exécution sont

    utilisées pour optimiser les instructions. Cette procédure porte le nom de ? détection des paramètres ? (parameter sniffing). Si

    ces valeurs sont typiques, la majorité des appels vers cette procédure stockée bénéficient d’un plan de requête efficace. Ce

    document aborde ensuite les techniques à utiliser pour éviter de mettre en cache des plans de requêtes contenant des valeurs de

    paramètres de procédures stockées atypiques.

    Lots. Il est possible de réutiliser des plans de requêtes pour les lots si le texte du lot est identique. Cette correspondance tient 6.

    également compte de la casse et des espacements.

    Exécution de la requête via EXEC ( ...). SQL Server 2005 peut mettre en cache des chaînes exécutées dans le cadre d'une 7.

    instruction EXEC. Elles portent le nom de ? SQL dynamique ?. Par exemple :

    EXEC ( 'SELECT *' + ' FROM Production.Product pr

    INNER JOIN Production.ProductPhoto ph' + '

    ON pr.ProductID = ph.ProductPhotoID' +

    ' WHERE pr.MakeFlag = ' + @mkflag )

    La réutilisation de plans est basée sur une chaîne concaténée. Cette dernière est obtenue en remplaçant des variables comme

    @mkflag dans l’exemple ci-dessus par leurs valeurs réelles au moment de l'exécution de l’instruction.

    Niveaux multiples de la mise en cache

    Il est important de comprendre que les correspondances de caches à plusieurs ? niveaux ? sont indépendantes les unes des autres. Voici un exemple : Supposons que le lot 1 (qui n’est pas une procédure stockée) contient l’instruction suivante (parmi d’autres) :

    EXEC dbo.procA

    Le texte du lot 2 (qui n'est pas non plus une procédure stockée) ne correspond pas à celui du lot 1, mais le lot 2 contient exactement

    la même instruction ? EXEC dbo.procA ? se référant à la même procédure stockée. Dans ce cas, les plans de requêtes des lots 1 et 2

    ne correspondent pas. Cependant, quand l'instruction ? EXEC dbo.procA ? est exécutée dans un des deux lots, il existe une

    possibilité de réutilisation du plan de requête (et de réutilisation du contexte d’exécution, comme décrit plus loin) pour procA si

    l’autre lot l’a exécuté avant le lot concerné et si le plan de requête pour procA existe encore dans le cache du plan. Chaque exécution de procA s’effectue cependant dans son propre contexte. Ce contexte d’exécution est soit nouvellement généré (si tous les contextes existants sont en cours d’utilisation) soit réutilisé (si un contexte inutilisé est disponible). On peut observer le même type de réutilisation même si du code SQL dynamique est exécuté via EXEC ou si une instruction autoparamétrée est exécutée dans les lots

    1 et 2. En résumé, les trois types de lots suivants démarrent leurs propres ? niveaux ?, dans lesquels des correspondances de caches

    peuvent apparaître, qu’il y ait ou non correspondance de cache à l'un des niveaux conteneurs.

    Exécution de procédures stockées de type ? EXEC dbo.stored_proc_name ?

    Exécutions de SQL dynamique de type ? EXEC query_string ?

    Requêtes autoparamétrées

    Les procédures stockées constituent une exception à la règle mentionnée ci-dessus. À titre d’exemple, il n’y a pas réutilisation du

    plan de requête et du contexte d’exécution de procA si deux procédures stockées distinctes contiennent l’instruction ? EXEC

    procA ?.

    Plans de requêtes et contextes d’exécution

    Quand un lot pouvant être mis en cache est soumis à SQL Server 2005 pour exécution, il est compilé et un plan de requête est placé

    dans le cache de plan. Un plan de requête est une structure réentrante en lecture seule partagée par de multiples utilisateurs. Il y a au

    plus deux instances d’un plan de requête à un moment donné dans le cache de plan : un pour toutes les exécutions en série et un pour toutes les exécutions en parallèle. La copie pour les exécutions parallèles est commune à tous les degrés du parallélisme. (À

proprement parler, si deux requêtes identiques posées par le même utilisateur en utilisant deux sessions différentes mais avec les

    mêmes options arrivent simultanément sur un SQL Server 2005, deux plans de requêtes cohabitent pendant l’exécution. Mais à la fin

    de l’exécution, un seul plan est gardé en cache pour une des deux requêtes).

    Le contexte d’exécution est obtenu à partir d’un plan de requête. Un contexte d’exécution est l’ensemble de ce qui est ? exécuté ?

    afin de produire les résultats de la requête. Les contextes d’exécution sont également mis en cache et réutilisés. Chaque utilisateur

    exécutant un lot se verra attribuer un contexte d’exécution contenant les données (les valeurs des paramètres par exemple) qui sont spécifiques à son exécution. Bien qu’ils soient réutilisés, les contextes d’exécution ne sont pas réentrants (ils sont à une entrée). Cela

    signifie qu’à tout moment, un contexte d’exécution ne peut exécuter qu’un seul lot soumis par une session et pendant l’exécution, le contexte n’est attribué à aucune autre session ni à un autre utilisateur.

    Les relations entre un plan de requête et les contextes d’exécution qui en sont dérivés sont répertoriées dans le schéma suivant.

    Celui-ci présente un plan de requête et les trois contextes d’exécution qui en découlent. Les contextes d’exécution contiennent les

    valeurs des paramètres et les informations spécifiques à l’utilisateur. Le plan de requête est indépendant des valeurs des paramètres et des informations spécifiques à l’utilisateur.

Dans le cache de plan, un plan de requête peut coexister avec plusieurs contextes d’exécution qui lui sont associés. Par contre le

    cache de plan ne peut contenir un contexte d’exécution seul (sans un plan de requête associé). En cas de suppression d’un plan de

    requête, tous les contextes d’exécution sont également supprimés du cache de plan.

    Lorsqu'il recherche un plan à réutiliser dans le cache, le système compare les plans de requêtes et non les contextes d’exécution. Une

    fois qu’un plan de requête réutilisable est identifié, le système trouve un contexte d’exécution (réutilisation du contexte) ou en

    génère un nouveau. De ce fait, la réutilisation d’un plan de requête n’implique pas nécessairement la réutilisation d’un contexte

    d’exécution.

    Les contextes d’exécution sont produits ? à la volée ? sur la base d’un squelette généré au début de l’exécution du lot. Au cours de

    l’exécution, les éléments de contexte nécessaires sont générés et placés dans le squelette. Cela veut dire que deux contextes

    d’exécution ne sont pas obligatoirement identiques, même après suppression des informations spécifiques à l’utilisateur et des

    paramètres de la requête. Comme les structures des contextes d’exécution qui dérivent du même plan de requête peuvent être différentes les unes des autres, le contexte d’exécution utilisé pour une exécution donnée a un léger impact sur les performances. L’impact de ces différences de performance diminue avec le temps lorsque le cache de plan a atteint son seuil de ? chauffe ? et que

    son état devient stable.

    Exemple : Supposons qu’un lot B contienne une instruction ? if ? (si). Au début de l’exécution de B, un contexte d’exécution est généré pour ce lot. Supposons que durant cette première exécution, le programme exécute le branchement ? true ? (vrai) de

    l'instruction ? if ?. Supposons en outre que B soit soumis à nouveau par une autre connexion pendant la première exécution. Comme

    le seul contexte d'exécution existant à cet instant est en cours d’utilisation, un second contexte est généré et attribué à la seconde

    connexion. Supposons que cette fois le programme exécute le branchement ? false ? (faux) de l'instruction ? if ?. Une fois que les

    deux exécutions sont terminées, B est soumis par une troisième connexion. En supposant que la troisième exécution de B choisisse le

    choix ? true ?, l’exécution sera terminée un peu plus rapidement si SQL Server 2005 choisit pour cette connexion le premier contexte d’exécution de B plutôt que le second.

Les contextes d’exécution d’un lot S peuvent être réutilisés même si la séquence d’appel de S est différente. Par exemple, une

    séquence d’appel peut être ? stored proc 1 --> stored proc 2 --> S ?, alors qu’une seconde séquence d’appel peut être ? stored proc 3

    --> S ?. Le contexte de la première exécution de S peut être réutilisé pour la seconde utilisation de S.

    Si une exécution de lot génère une erreur de niveau de gravité 11 ou plus, le contexte d’exécution est détruit. Si une exécution de lot génère un avertissement (erreur de niveau de gravité 10), le contexte d’exécution n’est pas détruit. Ainsi, même en l’absence de

    contrainte sur la mémoire (qui peut entraîner une réduction de cache de plan) le nombre de contextes d’exécution (pour un plan de

    requête donné) placés dans le cache de plan peut augmenter ou diminuer. Les contextes d’exécution pour les plans parallèles ne sont pas mis en cache. Pour que SQL Server puisse compiler un plan de requête parallèle, la condition suivante doit être vérifiée : le plus petit nombre de processeurs qui ont survécu au masque d’affinité du processeur et la valeur de l’option côté-serveur ? max degree of parallelism ? (degré max de parallélisme), définie le cas échéant

    avec la procédure stockée ? sp_configure ?, sont supérieurs à 1. Même si un plan de requête parallèle est compilé, le composant de

    SQL Server ? Query Execution ? (exécution de la requête) peut générer un contexte d’exécution série à partir de ce dernier. Les

    contextes d’exécution série ou parallèle dérivés d’un plan parallèle ne sont pas mis en cache. Par contre, un plan de requête parallèle est mis en cache.

    Mise en cache du plan de requête et différentes options SET (associées au showplan et autres) Différentes options SET, généralement associées au showplan, ont une incidence complexe sur la compilation, la mise en cache et la

    réutilisation des plans de requêtes et des contextes d’exécution. Le tableau suivant résume ces différents cas. Le tableau doit être lu comme suit. Un lot est soumis au SQL Server sous un mode donné, indiqué dans la première colonne. Il existe

    ou non un plan de requête dans le cache de plan pour le lot soumis. Les colonnes 2 et 3 s’appliquent en cas d’existence d’un plan de requête mis en cache, les colonnes 4 et 5 en cas d’absence de plan de requête mis en cache. Dans chaque catégorie, les cas s’appliquant aux plans de requêtes et aux contextes d’exécution sont séparés. Le texte explique ce qui se passe au niveau de la structure (plan de requête ou contexte d’exécution) : s'il y a mise en cache, réutilisation et utilisation. Nom du mode Si un plan de Si un plan de Si un plan de requête Si un plan de requête requête mis en requête mis en mis en cache n’existe mis en cache n’existe cache existe cache existe pas pas

    Plan de requête Contexte Plan de requête Contexte d’exécution

    d’exécution

    showplan_text, showplan_all, Réutilisé (pas de Réutilisé Mis en cache Génération d’un

    showplan_xml compilation) (compilation) contexte d’exécution,

    non utilisé et mis en

    cache

    statistics profile, statistics Réutilisé (pas de Non réutilisé. Mis en cache Génération d’un

    xml, statistics io, statistics compilation) Génération d’un (compilation) contexte d’exécution,

    time nouveau contexte utilisé et non mis en

    d’exécution, utilisé et cache

    non mis en cache

    noexec Réutilisé (pas de Réutilisé Mis en cache Le contexte d’exécution

    compilation) (compilation) n’est pas généré (du fait

    du mode ? noexec ?).

    Parseonly (en appuyant sur le n.s. n.s. n.s. n.s.

    bouton ? Analyser ? de

    l’analyseur de requêtes ou

    dans Management Studio)

    Coûts des différents plans de requêtes et contextes d’exécution Un coût est enregistré avec chaque plan de requête et contexte d’exécution. Le coût contrôle partiellement la durée de vie du plan ou du contexte dans le cache de plan. Dans SQL Server 2000 et SQL Server 2005, les coûts sont manipulés de manière différente. En

    voici le détail.

SQL Server 2000 : pour un plan de requête, le coût est une mesure des ressources du serveur (temps processeur et E/S) utilisées par

    l’optimiseur de requêtes pour optimiser le lot. Pour les requêtes spécifiques, le coût est égal à zéro. Pour un contexte d’exécution, le

    coût est une mesure des ressources du serveur (temps processeur et E/S) utilisées par le serveur pour initialiser le contexte afin que

    les instructions soient prêtes à être exécutées. Notez que les coûts du contexte d’exécution ne comprennent pas les coûts (temps de

    processeur et E/S) occasionnés pendant les exécutions du lot. Généralement, les coûts du contexte d’exécution sont plus bas que les

    coûts du plan de requête.

    Voici comment sont calculés les coûts d’un plan de requête pour un lot dans SQL Server 2000. Les quatre facteurs affectant les coûts sont les suivants : temps de processeur utilisé pour générer le plan (cputime) ; nombre de pages lues à partir du disque (ioread) ;

    nombre de pages écrites sur le disque (iowrite) ; et nombre de pages mémoire occupées par le plan de requête du lot (pagecount).

    Les coûts du plan de requête peuvent être exprimés sous la forme suivante (f est une fonction mathématique).

    Query plan cost c = f(cputime, ioread, iowrite) / pagecount

    Voici comment sont calculés les coûts d’un contexte d’exécution pour un lot dans SQL Server 2005. Les coûts individuels c donnés

    par l’équation ci-dessus sont calculés pour chaque instruction dans le lot et sont ensuite cumulés. À noter, cependant, que les coûts

    individuels sont maintenant des coûts de lancement et non des coûts de compilation ou d’exécution d’instructions.

    Parfois, la procédure d’écriture différée balaye le cache de plan et décrémente les coûts. Les coûts sont divisés par quatre et arrondis par défaut si nécessaire. (Exemple, 25 --> 6 --> 1 --> 0). En cas de contrainte de mémoire, les plans de requêtes et les contextes

    d’exécution avec des coûts nuls sont supprimés du cache de plan. En cas de réutilisation d’un plan de requête ou d’un contexte d’exécution, les coûts sont rétabli au niveau des coûts de compilation (ou de génération de contexte d’exécution). Les coûts du plan

    de requête et de la requête spécifique sont toujours incrémentés de 1. De ce fait, les plans de requêtes des lots exécutés fréquemment

    vivent plus longtemps dans le cache de plan que les lots exécutés rarement SQL Server 2005 : le coût d’une requête spécifique est égal à zéro. Sinon, le coût d’un plan de requête mesure les ressources

    nécessaires pour le produire. Plus particulièrement, le coût est calculé en ? nombre de mesures ? avec une valeur maximum de 31

    et il est constitué de trois parties :

    Coûts = coûts E/S + coûts commutateurs de contexte (mesure des coûts de processeur) + coûts mémoire Les parties individuelles des coûts sont calculées comme suit.

    Deux E/S coûtent 1 mesure, avec un maximum de 19 mesures

    Deux commutateurs de contexte coûtent 1 mesure, avec un maximum de 8 mesures

    16 pages de mémoire (128 Ko) coûtent 1 mesure, avec un maximum de 4 mesures

    Dans SQL Server 2005, le cache de plan est différent du cache de données. Il existe en outre d’autres caches spécifiques à certaines

    fonctions. La procédure d’écriture différée ne décrémente pas les coûts dans SQL Server 2005. Au contraire, dès que la taille du

    cache de plan atteint 50 % de celle du groupe de mémoires tampon, l’accès suivant au cache de plan décrémente les mesures de chaque plan de 1. La décrémentation étant superposée à une entrée qui accède au cache de plan à des fins de recherche, elle peut être

    considérée comme s’effectuant en différé. Si la taille totale de tous les caches dans SQL Server 2005 atteint ou dépasse 75 % de la

    taille du groupe de mémoires tampons, une entrée d’analyse de ressources est activée et elle décrémente le compteur de mesures de

    chaque objet dans tous les caches. (De fait, le comportement de l’entrée correspond approximativement à celui de l’entrée d’écriture

    en différé dans SQL Server 2005). Une réutilisation de plan de requête remet les coûts de plan à leur valeur initiale.

    Haut de page

    Guide pour la suite du document

    Le lecteur doit bien comprendre que pour obtenir une bonne performance de l’exécution du lot SQL Server, les deux points suivants

    doivent être vérifiés :

    Les plans de requêtes doivent être réutilisés autant que possible. Ceci évite les coûts de compilation de requêtes inutiles. La réutilisation des requêtes entraîne également une meilleure utilisation du cache de plan, ce qui implique de meilleures

    performances sur le serveur.

    Il faut éviter les manipulations risquant d’augmenter le nombre de recompilations de requêtes. La réduction des comptes de recompilation économise les ressources du serveur (processeur et mémoire) et augmente le nombre d’exécutions de lots avec une

    performance prédictible.

    La section suivante décrit en détail la réutilisation du plan de requête. Le cas échéant, nous indiquons des pratiques recommandées

    pour une meilleure réutilisation du plan. Dans la section suivante, nous décrivons certains scénarios courants qui peuvent augmenter

    le nombre de recompilations et nous exposons des pratiques recommandées pour les éviter.

Haut de page

    Réutilisation du plan de requête

    Le cache de plan contient les plans de requêtes et les contextes d’exécution. Un plan de requête est lié de par sa conception aux

    contextes d’exécution qui lui sont associés. La réutilisation du plan de requête pour un lot S dépend du lot lui-même (p.ex. texte de requête ou nom de la procédure stockée) et de certains facteurs externes (p.ex. le nom de l’utilisateur ayant généré S, l’application

    ayant généré S, les options SET de la connexion associée à S, etc.). Certains facteurs externes ont une influence sur la réutilisation

    car lorsque deux requêtes diffèrent uniquement d’un facteur, il n’est pas possible d’utiliser un plan commun. D’autres facteurs n’ont

    pas d’effet sur la réutilisation du plan.

    La majorité des facteurs qui influent sur la réutilisation du plan sont indiqués dans les colonnes de la table virtuelle

    sys.syscacheobjects. La liste suivante décrit les facteurs des scénarios les plus ? classiques ?. Dans certains cas, les entrées indiquent

    simplement lorsque les plans ne sont pas mis en cache (et donc jamais réutilisés) quelle qu’en soit la raison.

    En général, un plan de requête peut être réutilisé si le serveur, la base de données et les paramètres de la connexion qui sont à

    l’origine de sa mise en cache sont identiques aux paramètres de la connexion en cours. Ensuite, les objets référencés par le lot n’exigent pas de résolution de nom. Par exemple, Sales.SalesOrderDetail n’a pas besoin d'être résolu. Par contre SalesOrderDetail

    requiert une résolution car on peut trouver ce nom de table dans plusieurs bases de données. En général, l’emploi de noms d’objets complets permet de mieux réutiliser les plans.

    Facteurs qui influent sur la réutilisation du plan

    Notez que si un plan n’est pas mis en cache, il ne peut pas être réutilisé. C’est pourquoi nous ne signalerons explicitement que l’absence de mise en cache, la non-réutilisation étant alors implicite.

    Si une procédure stockée est exécutée dans la base de données D1, le plan de requête n’est pas réutilisé à l’exécution de la 1.

    même procédure stockée dans une base de données D2 différente. À noter que cela s’applique uniquement aux procédures

    stockées et non aux requêtes spécifiques, préparées et au SQL dynamique.

    Pour l'exécution d'un déclencheur, le nombre de lignes affectées par cette exécution (l contre n) correspondant au nombre de 2.

    lignes dans l'une ou l'autre des tables inserted ou deleted, est un facteur caractéristique d'une recherche dans un cache de plan.

    Notez que ce comportement est spécifique aux déclencheurs et ne s’applique pas aux procédures stockées.

    Dans le cas des déclencheurs INSTEAD OF de SQL Server 2005, le ? 1-plan ? est partagé par les exécutions concernant 0 et

    1 ligne alors que pour les autres déclencheurs (? after ?), ? 1-plan ? est utilisé uniquement dans les exécutions qui concernent

    1 ligne et ? n-plan ? pour les exécutions concernant 0 et n lignes (n étant supérieur à 1).

    Les instructions d’insertion en bloc ne sont jamais mises en cache, mais les déclencheurs associés aux insertions sont mis en 3.

    cache.

    Un lot contenant au moins un littéral plus grand que 8 Ko n’est pas mis en cache. De ce fait, les plans de requêtes relatifs à ces 4.

    lots ne peuvent pas être réutilisés. (La longueur d’un littéral se mesure après définition de la constante).

    Les lots marqués d’un indicateur ? réplication ? (associé à un utilisateur de réplication) ne sont pas comparés aux lots non 5.

    marqués.

    Un lot appelé à partir du common-language runtime (CLR) de SQL Server 2005 n’est pas comparé au même lot soumis hors 6.

    CLR. Cependant, deux lots soumis par CRL peuvent réutiliser le même plan. Il en va de même pour :

     les déclencheurs CLR et non CLR,

     les requêtes de notification.

    Les plans pour des requêtes soumises via sp_resyncquery ne sont pas mis en cache. Si la requête est soumise une nouvelle fois 7.

    (via sp_resyncquery ou autrement), elle doit être recompilée.

    SQL Server 2005 permet la définition de curseurs en tête d’un lot T-SQL. Si le lot est soumis sous forme d’instruction 8.

    séparée, il ne réutilise pas le plan pour ce curseur.

    9. Les options SET suivantes Nom de l’option SET

    ont un effet sur la

    réutilisation du plan.

    Nombre

    ANSI_NULL_DFLT_OFF 1

    ANSI_NULL_DFLT_ON 2

    ANSI_NULLS 3

    ANSI_PADDING 4

    ANSI_WARNINGS 5

    ARITHABORT 6

    CONCAT_NULL_YIELDS_NULL 7

    DATEFIRST 8

    DATEFORMAT 9

    FORCEPLAN 10

    LANGUAGE 11

    NO_BROWSETABLE 12

    NUMERIC_ROUNDABORT 13

    QUOTED_IDENTIFIER 14

    De plus, ANSI_DEFAULTS affecte la réutilisation de plan car l’option peut être utilisée pour modifier les options SET suivantes (certaines affectant la réutilisation du plan) : ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING,

    ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS, QUOTED_IDENTIFIER.

    Les options SET ci-dessus affectent la réutilisation du plan parce que SQL Server 2000 et SQL Server 2005 effectuent la

    ? définition de constante ? (évaluation d’une expression constante au moment de la compilation à des fins d’optimisation) et parce que les paramètres de ces options affectent les résultats de ces expressions.

    Les paramètres de certaines de ces options SET sont indiqués dans les colonnes de la table virtuelle sys.syscacheobjects, par

    exemple ? langid ? et ? dateformat ?.

    À noter que les valeurs de certaines de ces options SET peuvent être modifiées à l’aide de plusieurs méthodes :

     la procédure stockée sp_configure (pour une modification sur l’ensemble du serveur)

     la procédure stockée sp_dboption (pour une modification sur l’ensemble de la base de données)

     la clause SET de l’instruction ALTER DATABASE

    En cas de conflit entre les valeurs, les options SET au niveau utilisateur et au niveau de la connexion sont prioritaires sur celles qui sont au niveau de la base de données et du serveur. De plus, si une option SET au niveau de la base de données est

    effective, alors, pour un lot qui référencie plusieurs bases de données (pouvant potentiellement avoir des valeurs d’option SET différentes), les options SET de la base de données dans le contexte de laquelle le lot est exécuté prennent le pas sur les options SET des autres bases de données.

    Pratique recommandée : pour éviter une recompilation liée aux options SET, établissez les options SET au moment de la connexion et assurez-vous qu'elles ne changent pas pendant la durée de cette dernière.

    Lorsque les lots portent des noms d’objets non qualifiés, les plans de requêtes ne peuvent pas être réutilisés. Par exemple, 10.

    dans ? SELECT * FROM MyTable ?, MyTable peut être légitimement traduit par Alice.MyTable si Alice lance cette requête et si elle est propriétaire de la table portant ce nom. De même, MyTable peut se traduire par Bob.MyTable. Dans ce cas, SQL Server ne réutilise pas les plans de requêtes. Par contre, si Alice lance ? SELECT * FROM dbo.MyTable ?, il n’y a pas

    d’ambiguïté car l’objet est identifié de manière univoque et il est possible de réutiliser le plan de requête. (Voir la colonne uid

    dans sys.syscacheobjects. Elle indique l’ID d’utilisateur pour la connexion dans laquelle a été créé le plan. Seuls les plans de

Report this document

For any questions or suggestions please email
cust-service@docsford.com