INF : Dépannage des problèmes recompilation de procédures stockées

Traductions disponibles Traductions disponibles
Numéro d'article: 243586 - Voir les produits auxquels s'applique cet article
Ancien nº de publication de cet article : F243586
Agrandir tout | Réduire tout

Sommaire

Résumé

Cet article traite d'un type spécifique de problèmes de performances des applications utilisées avec SQL Server : la recompilation des procédures stockées. Si vous devez résoudre un problème de performances sans en avoir encore identifié la cause exacte, consultez en premier lieu l'article suivant de la Base de connaissances Microsoft :

224587 INF : Résolution des problèmes de performances des applications avec SQL Server
Cet article part du principe que vous avez suivi les instructions contenues dans l'article mentionné ci dessus pour cibler l'origine du problème et que vous avez capturé une trace du Générateur de profils SQL Server comportant les détails des événements et des colonnes de données spécifiques.

Plus d'informations

Dans SQL Server 7.0, lorsque vous créez une procédure stockée, le texte de la procédure est stocké dans la table système syscomments. Lorsqu'un utilisateur exécute la procédure, si celle-ci n'est pas déjà disponible en cache, SQL Server la charge à partir de syscomments (sysprocedures dans les versions précédentes de SQL Server) et compile un plan de requête. Le plan compilé est mis en cache et réutilisé par les appelants suivants de la procédure stockée jusqu'à l'exécution d'une action invalidant le plan et nécessitant une recompilation. Les opérations suivantes peuvent provoquer la recompilation d'un plan de procédure stockée :
  • Utilisation d'une clause WITH RECOMPILE dans l'instruction CREATE PROCEDURE ou EXECUTE.
  • Modification de schéma vers les objets référencés, y compris ajout ou suppression de contraintes, de valeurs par défaut ou de règles.
  • Exécution de sp_recompile pour une table référencée dans la procédure.
  • Restauration de la base de données contenant la procédure ou l'un des objets référencés dans la procédure (si vous effectuez des opérations de bases de données croisées).
  • Activité importante du serveur provoquant l'expiration du plan et sa sortie cache.
Toutes ces raisons de recompiler une procédure stockée existaient dans les versions précédentes de SQL Server et entraînaient la recompilation du plan avant le commencement de l'exécution de la procédure. Dans SQL Server 7.0, un comportement nouveau apparaît puisqu'une procédure stockée peut se recompiler pendant son exécution. Cela permet de garantir que l'optimiseur dispose toujours du meilleur plan pour chaque instruction à l'intérieur d'une procédure. Les événements suivants peuvent provoquer la recompilation des procédures stockées :
  • Un pourcentage important de données modifiées au sein d'une table référencée dans la procédure stockée.
  • La procédure entrelace les opérations DDL (Data Definition Language) et DML (Data Manipulation Language).
  • La procédure effectue certaines opérations sur les tables temporaires.
Chacune de ces causes est traitée en détail dans cet article.

Dans certains cas, l'inconvénient de la recompilation d'une procédure stockée dépasse les avantages, en particulièrement pour les procédures étendues. Il est essentiel de ne pas oublier que lorsqu'une recompilation est déclenchée, l'ensemble du lot ou de la procédure est recompilé. Cela signifie que la baisse de performance est directement proportionnelle à la taille de la procédure ou du lot. Pour plus d'informations à ce sujet, consultez la rubrique " Conseils Transact-SQL " dans la documentation SQL Server 7.0 en ligne.


Les informations de cet article se concentrent sur l'identification de la cause de l'exécution des recompilations de procédures stockées et traitent des méthodes possibles pour les éviter.

Meilleure méthode

Il est préférable d'indiquer complètement et exactement les noms des procédures stockées lorsque vous exécutez une procédure en tant que non-propriétaire. Cela garantit une meilleure clarté et une réutilisation plus simple du plan d'exécution existant pour l'utilisateur actuel. Par exemple, si un utilisateur autre que le propriétaire de base de données exécute une procédure stockée (appelée maProc dans cet exemple) appartenant à un propriétaire de base de données dans la base de données pubs, utilisez l'instruction suivante : exec pubs.dbo.maProc au lieu de :
exec maProc
Cette technique évite la confusion avec d'autres versions possibles de la procédure par différents propriétaires à partir d'un point de codage et de maintenance et permet également à SQL Server d'accéder plus rapidement au plan d'exécution pour la procédure donnée.

Identification et résolution des problèmes

Si vous ne l'avez pas encore fait, consultez l'article suivant de la Base de connaissances Microsoft ; vous y trouverez des informations concernant la capture de données Optimiseur qui vous aideront dans l'analyse des performances système :
224587 INF : Résolution des problèmes de performances des applications avec SQL Server

Affichage des données Optimiseur

Le Générateur de profils SQL Server comprend un événement SP:Recompile que vous pouvez utiliser pour contrôler le nombre de recompilations. L'événement SP:Recompile a lieu chaque fois qu'une procédure stockée se recompile pendant son exécution.
  • Groupement de la trace Générateur de profils par Classe d'événements

    1. Dans le menu Fichier, cliquez sur Propriétés.
    2. Dans l'onglet Colonnes de données, utilisez le bouton HAUT pour déplacer la colonne Classe d'événements et la colonne Texte sous l'en-tête Groupes, en positionnant Classe d'événements en premier. Utilisez le bouton BAS pour supprimer toutes les autres colonnes sous l'en-tête Groupes.
    3. Cliquez sur OK.
    Vérifiez le nombre d'événements SP:Recompile.

    Vous pouvez développer le groupe SP:Recompile pour voir les détails de chaque occurrence. La colonne Texte de l'événement indique le nom de la procédure stockée qui s'est recompilée. Si plusieurs procédures provoquent des recompilations, elles sont triées par nombre d'occurrences. Si les événements SP:Recompile sont nombreux et que l'utilisation de l'UC soit élevée, vous devez d'abord dépanner les procédures présentant le plus de recompilations. Notez l'ID de processus système (SPID) et l'Heure de démarrage de l'événement SP:Recompile pour une instance de la procédure stockée, puis procédez comme suit. Si aucun événement SP:Recompile n'est affiché mais que vous rencontrez encore des problèmes de performances, consultez l'article suivant de la Base de connaissances Microsoft :
    224587 INF : Résolution des problèmes de performances des applications avec SQL Server
  • Identification de l'instruction ayant déclenché l'événement de recompilation

    1. Dans le menu Fichier, cliquez sur Propriétés.
    2. Dans l'onglet Colonnes de données, utilisez le bouton BAS pour supprimer toutes les autres colonnes sous l'en-tête Groupes.
    3. Dans l'onglet Événements, supprimez tous les événements sauf SP:Starting, SP:StmtStarting, SP:Recompile et SP:Completed. Si vous n'avez pas capturé l'événement SP:StmtStarting, vous pouvez le remplacer par SP:StmtCompleted, mais n'indiquez pas les deux car cela doublerait la quantité des informations que vous devrez examiner.
    4. Si vous avez découvert une instance d'une recompilation de procédure stockée à examiner, vous pouvez limiter les données affichées au SPID et à la période spécifiques à cette occurrence grâce à l'onglet Filtres.
    5. Cliquez sur OK.

    L'événement SP:Recompile sera élevé directement au rang suivant l'événement SP:StmtStarted de l'instruction de la procédure stockée ayant provoqué la recompilation. À la fin de l'événement de recompilation, vous voyez une répétition de l'événement SP:StmtStarted, indiquant que l'instruction s'exécute avec un nouveau plan.

    Considérez cet exemple :
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    Si vous exécutez ce code dans l'Analyseur de requête et que vous affichiez les événements ci-dessus dans une trace de Générateur de profils, la séquence suivante s'affiche :

    Réduire ce tableauAgrandir ce tableau
    SP:Starting RecompProc
    SP:StmtStarting create table #t (a int)
    SP:StmtStarting select * from #t
    SP:Recompile RecompProc
    SP:StmtStarting select * from #t
    SP:Completed RecompProc


    Il est évident que l'instruction qui a causé la recompilation était :
    select * from #t
    En effet, elle apparaît à la fois avant et après l'événement SP:Recompile.

    Si vous n'avez capturé que l'événement SP:StmtCompleted, mais pas l'événement SP:StmtStarting, l'événement SP:Recompile apparaîtra immédiatement devant l'instruction qui l'a provoqué, comme suit :

    Réduire ce tableauAgrandir ce tableau
    SP:Starting RecompProc
    SP:Recompile RecompProc
    SP:StmtCompleted select * from #t
    SP:Completed RecompProc


    Comme vous pouvez le voir, l'événement SP:Recompile est généré avant l'événement SP:StmtCompleted pour l'instruction " select * from #t ", qui a provoqué la recompilation. Cela paraît logique, puisque l'instruction ne peut pas être terminée tant qu'un nouveau plan de requête n'a pas été généré pour la recompilation. Tous les autres exemples de cet article utilisent l'événement SP:StmtStarting. Si vous n'avez capturé que l'événement SP:StmtCompleted, n'oubliez pas d'afficher l'instruction après SP:Recompile, comme indiqué précédemment.

    N'oubliez pas que si vous exécutez plusieurs fois cette procédure stockée, SQL Server réutilise le plan existant pour cette procédure. Vous ne verrez que l'événement de recompilation pour la première exécution de la procédure, ou si vous abandonnez puis recréez la procédure chaque fois que vous exécutez le script. La raison de la recompilation dans ce cas particulier est étudiée dans la section " Recompilations dues à l'entrelacement d'opérations DDL (Data Definition Language) et DML (Data Manipulation Language) " de cet article ; il s'agit simplement d'un exemple visant à illustrer la facilité de la détermination de l'instruction provoquant la recompilation.

Recompilations dues à des modifications de ligne


À partir d'un certain pourcentage de données modifiées au sein d'une table référencée dans une procédure stockée depuis la génération du plan de requête d'origine, SQL Server recompile la procédure stockée pour garantir qu'il dispose d'un plan basé sur les données statistiques les plus récentes. Prenons comme exemple la procédure stockée suivante :
drop procedure RowModifications 
go
create procedure RowModifications as
-- suppose que SomeTable existe, avec la même définition que #t 
-- et qu'elle contient plus de 1000 lignes
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
select count(*) from #t  where a = 37
go
exec RowModifications
exec RowModifications
Pour la seconde exécution de la procédure RowModifications, vous verrez s'afficher les événements suivants dans le Générateur de profils :

Réduire ce tableauAgrandir ce tableau
SP:Starting RowModifications
SP:StmtStarting create table #t (a int, b char(10))
SP:StmtStarting select * from #t
SP:StmtStarting insert #t select * from SomeTable
SP:StmtStarting select count(*) from #t where a = 37
SP:Recompile RowModifications
Auto-UpdateStats a
SP:StmtStarting select count(*) from #t where a = 37
SP:Completed RowModifications

REMARQUE : la première exécution affichera également un événement SP:Recompile pour l'instruction " select * from #t ". La raison de la recompilation dans ce cas particulier est traitée dans la section " Recompilations dues à l'entrelacement d'opérations DDL (Data Definition Language) et DML (Data Manipulation Language) " de cet article. Pour cet exemple, observez bien l'événement SP:Recompile indiqué ci-dessus car il se produit à chaque exécution de la procédure.

Dans cet exemple, " select count(*) from #t where a = 37 " provoque une recompilation de la procédure en raison du changement du nombre de lignes depuis la création de la table. La présence de l'événement Auto-UpdateStats confirme le fait que la recompilation a été causée par des modifications de lignes. La colonne Texte indique la colonne pour laquelle les statistiques ont été modifiées.

Lors de la création de la table #t, le nombre de lignes est zéro. Le plan de l'instruction " select * from #t " d'origine est développé avec ce compte de lignes, ainsi que le plan de la requête " select count (*) ". Cependant, avant l'exécution de " select count(*) ", 1000 lignes nouvelles sont insérées dans la table #t. Parce qu'un nombre important de données a été modifié, l'optimiseur recompile la procédure afin de garantir qu'il choisit le plan le plus efficace pour l'instruction. Cette recompilation aura lieu à chaque exécution de la procédure stockée car l'insertion de 1000 lignes sera toujours interprétée comme suffisamment importante pour justifier la recompilation.

Pour déterminer si un plan doit être recompilé, SQL Server emploie le même algorithme que celui qui permet de mettre à jour automatiquement les statistiques, comme décrit dans l'article suivant de la Base de connaissances Microsoft :
195565 INF : Fonctionnement des statistiques automatiques SQL Server 7.0
Dans l'exemple précédent, la procédure stockée est suffisamment petite pour que la recompilation n'ait pas d'effet notable sur les performances. Cependant, dans le cas d'une procédure stockée importante qui exécute des opérations similaires entraînant plusieurs recompilations, vous pouvez constater une baisse des performances.

Les méthodes suivantes permettent de contrer les recompilations dues à des modifications de lignes :
  • Exécutez l'instruction à l'aide de sp_executesql.
    Il s'agit de la méthode la plus souvent utilisée. Les instructions exécutées avec la procédure stockée sp_executesql ne sont pas compilées en tant que parties du plan de la procédure stockée. Ainsi, lorsque vous exécuterez l'instruction, SQL Server pourra soit utiliser un plan existant en cache pour l'instruction, soit créer un nouveau plan lors de l'exécution. Dans chacun des cas, le plan pour la procédure stockée appelante n'est pas affecté et il n'est pas nécessaire de le recompiler.

    L'instruction EXECUTE aura le même effet. Elle n'est cependant pas recommandée. L'instruction EXECUTE n'est pas aussi efficace que sp_executesql car elle ne permet pas de paramétrer la requête.

    La procédure RowModifications indiquée précédemment peut être déclarée de telle sorte qu'elle utilise sp_executesql de la manière suivante :

    drop procedure RowModifications2 
    go
    create procedure RowModifications2 as
    set nocount on
    -- suppose que SomeTable existe, avec la même définition que #t 
    -- et qu'elle contient plus de 1000 lignes
    create table #t (a int, b char(10))
    select * from #t
    insert #t select * from SomeTable
    exec sp_executesql N'select count(*) from #t where a = @a', 
                       N'@a int', @a =  37
    go
    exec RowModifications2
    exec RowModifications2

    Pour la seconde exécution de la procédure RowModifications2, vous verrez s'afficher les événements suivants dans le Générateur de profils :

    Réduire ce tableauAgrandir ce tableau
    SP:Starting RowModifications2
    SP:StmtStarting create table #t (a int, b char(10))
    SP:StmtStarting select * from #t
    SP:StmtStarting insert #t select * from SomeTable
    SP:StmtStarting exec sp_executesql N'select count(*) from #t where a = @a', N'@a int', @a = 37
    SP:Starting
    SP:StmtStarting select count(*) from #t where a = @a
    Auto-UpdateStats a
    SP:StmtStarting select count(*) from #t where a = @a
    SP:Completed
    SP:Completed RowModifications2


    Notez bien qu'il n'existe aucun événement SP:Recompile pour la procédure RowModifications2. Les événements complets SP:Starting à SP:Completed sont présents pour le contexte d'appel sp_executesql et un événement Auto-UpdateStats est présent pour la colonne a. Cependant, parce que cet appel se trouve en dehors du contexte de la procédure stockée, il n'est pas nécessaire de recompiler la procédure RowModifications2 dans ce cas.

    Pour plus d'informations concernant l'utilisation de la procédure stockée sp_executesql, consultez les rubriques " sp_executesql (T-SQL) " et " Utilisation de sp_executesql " dans la documentation SQL Server 7.0 en ligne.
  • Utilisation de sous-procédures pour exécuter les instructions provoquant les recompilations.
    Dans ce cas, l'instruction peut encore provoquer une recompilation, mais au lieu de recompiler une importante procédure stockée d'appel, elle ne recompile que la petite sous-procédure.
  • Utilisation de l'option KEEP PLAN.
    Les tables temporaires possèdent des règles spéciales concernant les recompilations qui, dans certains cas, peuvent s'avérer plus strictes que l'algorithme de recompilation par défaut. L'option KEEP PLAN peut vous permettre de retourner le seuil de table temporaire à l'algorithme par défaut. Pour plus d'informations, voir la section " Blocage de la recompilation grâce à l'option KEEP PLAN " de cet article.
REMARQUE : la procédure RowModifications est un exemple très simplifié d'une procédure recompilée en raison de modifications de ligne. Veuillez relire les avertissements concernant cet exemple :

  • Bien que l'exemple utilise une table temporaire, cette situation s'applique aux procédures stockées se rapportant également à des tables permanentes. Si une quantité importante de données d'une table référencée a été modifiée depuis la génération du plan de requête, la procédure stockée est recompilée. Les différences d'interprétation des tables temporaires à des fins de recompilation sont décrites dans la section " Blocage de la recompilation grâce à l'option KEEP PLAN " de cet article.
  • Les premières exécutions des deux procédures mentionnées ci-avant provoquent également une recompilation sur la première sélection à partir de la table temporaire #t. Les raisons de cette recompilation sont traitées dans la section " Recompilations dues à l'entrelacement d'opérations DDL (Data Definition Language) et DML (Data Manipulation Language) " de cet article.
  • Une instruction " select count(*) from #t " était utilisée dans cet exemple au lieu d'une simple instruction " select * from #t ". Pour éviter que trop de recompilations n'aient lieu, SQL Server ne prend pas en considération les " plans normaux " de recompilation (tels que la sélection * à partir d'une table) causés par des modifications de lignes.

Recompilations dues à l'entrelacement d'opérations DDL (Data Definition Language) et DML (Data Manipulation Language)

Si vous effectuez des opérations DDL à l'intérieur d'une procédure ou d'un lot, la procédure ou le lot est recompilé lorsqu'il se trouve face à la première opération DLL suivante affectant la table concernée par le DDL.

Considérez cet exemple de procédure stockée :
drop procedure Interleave 
go
create procedure Interleave as
-- DDL
create table #t1 (a int)
-- DML
select * from #t1
-- DDL
create index idx_#t1 on #t1(a)
-- DML
select * from #t1
-- DDL
create table #t2 (a int)
-- DML
select * from #t2
go
exec Interleave
Si vous exécutez ce code dans l'Analyseur de requête et que vous affichez les événements ci-dessus dans une trace de Générateur de profils, la séquence suivante s'affiche :

Réduire ce tableauAgrandir ce tableau
SP:Starting Interleave
SP:StmtStarting create table #t1 (a int)
SP:StmtStarting select * from #t1
SP:Recompile Interleave
SP:StmtStarting select * from #t1
SP:StmtStarting create index idx_#t1 on #t1(a)
SP:StmtStarting select * from #t1
SP:Recompile Interleave
SP:StmtStarting select * from #t1
SP:StmtStarting create table #t2 (a int)
SP:StmtStarting select * from #t2
SP:Recompile Interleave
SP:StmtStarting select * from #t2
SP:Completed Interleave


Dans ce cas, la procédure stockée est recompilée trois fois pendant son exécution. Pour comprendre la raison de la recompilation, analysez de quelle façon l'optimiseur développe un plan pour cette seconde procédure stockée :
  1. Pendant la compilation initiale de la procédure, les tables temporaires #t1 et #t2 n'existent pas. Il est donc impossible de créer de plans pour les requêtes se rapportant à ces tables. Ils doivent être générés au moment de l'exécution.
  2. Lorsque la procédure s'exécute pour la première fois, la première étape consiste à créer la table #t1. L'opération suivante est une sélection à partir de la table #t1 -- pour laquelle il n'existe aucun plan. Ainsi, la procédure est recompilée à ce moment afin de développer un plan pour l'instruction SELECT. Un plan est généré pour la sélection actuelle de #t1, ainsi que pour la sélection à partir de #t1 après la création d'index. Aucun plan ne peut être généré pour la sélection de #t2 car #t2 n'existe pas encore.
  3. L'étape suivante consiste à créer un index sur #t1. Ensuite, une autre sélection a lieu sur #t1, qui possède maintenant un plan à partir de la première recompilation. Cependant, étant donné que le schéma de #t1 a été modifié depuis la génération du plan, la procédure doit être de nouveau recompilée pour générer un nouveau plan pour la sélection à partir de #t1. Et parce que #t2 n'existe toujours pas, aucun plan ne peut être généré pour la sélection à partir de #t.
  4. La table #t2 est ensuite créée et la sélection à partir de #t2 exécutée. Parce qu'aucun plan n'existe pour l'instruction, la procédure est recompilée une dernière fois.
Ces recompilations ont lieu à chaque exécution de la procédure stockée. Pour réduire le nombre de recompilations, modifiez la procédure de manière à effectuer d'abord les opérations DDL, puis les opérations DML, comme indiqué ci-après :
drop procedure NoInterleave 
go
create procedure NoInterleave as
-- Tous les opérations DDL d'abord
create table #t1 (a int)
create index idx_#t1 on #t1(a)
create table #t2 (a int)
-- puis les opérations DML 
select * from #t1
select * from #t1
select * from #t2
go
exec NoInterleave 
exec NoInterleave
La première exécution de la procédure NoInterleave affichera les événements suivants dans le Générateur de profils :

Réduire ce tableauAgrandir ce tableau
SP:Starting NoInterleave
SP:StmtStarting create table #t1 (a int)
SP:StmtStarting create index idx_#t1 on #t1(a)
SP:StmtStarting create table #t2 (a int)
SP:StmtStarting select * from #t1
SP:Recompile NoInterleave
SP:StmtStarting select * from #t1
SP:StmtStarting select * from #t1
SP:StmtStarting select * from #t2
SP:Completed NoInterleave


Dans ce cas, toutes les instructions DDL sont effectuées d'avance. L'optimiseur compile cette procédure de la manière suivante :
  1. Pendant la compilation initiale de la procédure, les tables temporaires #t1 et #t2 n'existent pas. Il est donc impossible de créer un plan pour les requêtes se rapportant à ces tables. Ils doivent être générés au moment de l'exécution.
  2. Les premières opérations effectuées par la procédure sont les opérations DDL, qui créent les tables #t1 et #t2, ainsi que l'index sur #t1.
  3. L'étape suivante est la première sélection à partir de #t1. Étant donné qu'aucun plan n'est disponible pour cette instruction SELECT, la procédure est recompilée. Puisque tous les objets existent, les plans sont générés pour toutes les instructions SELECT de la procédure à ce moment.
  4. Le reste de la procédure s'exécute à l'aide des plans générés. Étant donné que les objets référencés n'ont pas été modifiés, il n'est pas nécessaire de recompiler de nouveau la procédure.
REMARQUE : la seconde exécution et les suivantes utilisent le plan de requête et le cache existants et elles n'entraînent aucune recompilation. Les procédures qui utilisent fréquemment des tables temporaires doivent être modifiées pour garantir que toutes les instructions DDL se trouvent au début de la procédure.

Recompilations dues à certaines opérations de tables temporaires

L'utilisation de tables temporaires dans une procédure stockée peut provoquer la recompilation de la procédure stockée à chaque exécution de la procédure.

Pour éviter cela, modifiez la procédure stockée de manière qu'elle réponde aux exigences suivantes :
  • Toutes les instructions contenant le nom d'une table temporaire font référence à une table temporaire créée dans la même procédure stockée, et non dans une procédure stockée appelante ou appelée, ou dans une chaîne exécutée à l'aide de l'instruction EXECUTE ou de la procédure stockée sp_executesql.
  • Toutes les instructions contenant le nom d'une table temporaire apparaissent de manière syntaxique après la table temporaire dans la procédure stockée ou le déclencheur.
  • Il n'existe aucune instruction DECLARE CURSOR dont les instructions SELECT fassent référence à une table temporaire.
  • Toutes les instructions contenant le nom d'une table temporaire précèdent toute instruction DROP TABLE faisant référence à une table temporaire.

    Les instructions DROP TABLE ne sont pas requises pour les tables temporaires créées dans une procédure stockée. Les tables sont automatiquement abandonnées lorsque la procédure se termine.
  • Aucune instruction créant une table temporaire (telle que CREATE TABLE ou SELECT... INTO) n'apparaît dans une instruction de contrôle de flux telle que IF... ELSE ou WHILE.

Blocage de la recompilation grâce à l'option KEEP PLAN

L'utilisation d'une table temporaire dans des procédures stockées introduit certaines difficultés pour l'optimiseur de requête. Le compte de lignes et les informations statistiques des tables peuvent varier de manière importante sur toute la durée de l'exécution de la procédure stockée. Pour garantir que l'optimiseur utilise le meilleur plan dans tous les cas relatifs aux tables temporaires, un algorithme spécial a été développé de manière qu'il soit plus agressif envers en matière de recompilation. Cet algorithme établit que si une table temporaire créée avec une procédure stockée a été modifiée plus de six fois, la procédure est recompilée lorsque la nouvelle instruction fait référence à la table temporaire.

Considérez cet exemple :
drop procedure useKeepPlan 
go
create procedure useKeepPlan as
create table #t (a int, b char(3))
select * from #t
-- Appliquer plus de6 modifications à #t
insert #t values (1, 'abc')
insert #t values (2, 'abc')
insert #t values (3, 'abc')
insert #t values (4, 'abc')
insert #t values (5, 'abc')
insert #t values (6, 'abc')
insert #t values (7, 'abc')
-- Référencer #t
select count(*) from #t 
-- option (KEEP PLAN)
go
exec useKeepPlan
exec useKeepPlan
Dans ce cas, vous verrez s'afficher les événements suivants dans le Générateur de profils pour la seconde exécution :

Réduire ce tableauAgrandir ce tableau
SP:Starting useKeepPlan
SP:StmtStarting create table #t (a int)
SP:StmtStarting - Les sept instructions d'insertion -
SP:StmtStarting select count(*) from #t1
SP:Recompile useKeepPlan
SP:StmtStarting select count(*) from #t1
SP:Completed useKeepPlan

La procédure est recompilée sur la sélection suivant les sept modifications de la table temporaire #t.

Cette recompilation agressive trouve son utilité dès lors que les modifications de la distribution de données de la table temporaire peuvent grandement affecter le plan de requête optimal pour l'instruction qui s'y rapporte. Cependant, dans le cas de procédures étendues qui modifient souvent les tables temporaires, mais pas de manière importante, les recompilations peuvent entraîner une baisse des performances générales. L'option KEEP PLAN de l'instruction SELECT a été créée pour ce type de situation.

KEEP PLAN élimine les recompilations de procédures stockées provoquées par plus de six modifications des tables temporaires à l'intérieur de la procédure et revient à l'algorithme standard de recompilation suite aux modifications de lignes dont il a précédemment été question dans la section " Recompilations dues à des modifications de ligne " de cet article. L'option KEEP PLAN n'empêche pas les recompilations en général, elle évite celles qui sont provoquées par plus de six modifications de tables temporaires référencées dans la procédure. Dans l'exemple ci-dessus, si vous supprimez le commentaire de la ligne " option (KEEP PLAN) " dans la procédure stockée, l'événement SP:Recompile ne sera pas généré.

Si vous supprimez le commentaire de la ligne " option (KEEP PLAN) " dans le code précédent et que vous l'exécutez, vous verrez les événements suivants dans le Générateur de profils :

Réduire ce tableauAgrandir ce tableau
SP:Starting useKeepPlan
SP:StmtStarting create table #t (a int)
SP:StmtStarting - Les sept instructions d'insertion -
SP:StmtStarting select count(*) from #t1 option (KEEP PLAN)
SP:Completed useKeepPlan


Notez que l'événement SP:Recompile n'est pas présent.

Références

Pour plus d'informations sur les problèmes de performance de requêtes dans SQL Server 7.0, consultez le Dépanneur de performances de requêtes SQL Server 7.0 à l'adresse http://support.microsoft.com/?scid=ph;en-us;2862.

Pour des informations concernant le Générateur de profils SQL Server, voir la documentation SQL Server 7.0 en ligne.

Propriétés

Numéro d'article: 243586 - Dernière mise à jour: jeudi 6 juillet 2006 - Version: 3.0
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 7.0 Standard
Mots-clés : 
kbinfo kbsqlserv700 KB243586
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com