Recompilation de procédures stockées
Comme une base de données est modifiée par des opérations telles que l'ajout d'index ou la modification de données dans les colonnes indexées, les plans de requête d'origine servant à accéder à ses tables doivent être à nouveau optimisés par leur recompilation. Cette optimisation se produit automatiquement lors de la première exécution d'une procédure stockée après le redémarrage de SQL Server. Elle se produit également lorsqu'une table sous-jacente utilisée par la procédure stockée est modifiée. Toutefois, si vous ajoutez un nouvel index dont la procédure stockée pourrait bénéficier, l'optimisation ne se produit pas jusqu'à l'exécution suivante de la procédure stockée après le redémarrage de SQL Server. Dans cette situation, il peut être utile de forcer la recompilation de la procédure stockée pendant son exécution suivante.
Une autre raison pour forcer la recompilation d'une procédure stockée est de contrer, lorsque nécessaire, le comportement de détection des paramètres de la compilation des procédures stockées. Lorsque SQL Server exécute des procédures stockées, les valeurs des paramètres utilisés par la procédure lors de sa compilation sont incluses dans le cadre de la génération du plan de requête. Si ces valeurs représentent les valeurs standard avec lesquelles la procédure est appelée plus tard, la procédure stockée bénéficie du plan de requête à chaque compilation et exécution. Dans le cas contraire, les performances peuvent être dégradées.
SQL Server 2008 R2 permet la recompilation des procédures stockées au niveau de l'instruction. Lorsque SQL Server 2008 R2 recompile des procédures stockées, seule l'instruction ayant provoqué la recompilation est compilée, et non la procédure toute entière. Par conséquent, SQL Server utilise les valeurs de paramètres telles qu'elles existent dans l'instruction recompilée lors de la régénération du plan de requête. Ces valeurs peuvent être différentes de celles qui ont été intégrées dans la procédure à l'origine.
Recompilation forcée d'une procédure stockée
SQL Server propose trois moyens de forcer la recompilation d'une procédure stockée :
La procédure stockée système sp_recompile force la recompilation d'une procédure stockée lors de son exécution suivante. Pour cela, il supprime le plan existant du cache de procédures, ce qui force la création d'un nouveau plan lors de la prochaine exécution de la procédure.
La création d'une procédure stockée incluant l'option WITH RECOMPILE dans sa définition indique que SQL Server ne met pas de plan en cache pour cette procédure et que celle-ci est recompilée à chaque fois qu'elle est exécutée. Utilisez l'option WITH RECOMPILE lorsque les procédures stockées utilisent des paramètres dont les valeurs diffèrent beaucoup d'une exécution à l'autre, ce qui entraîne la création de plusieurs plans d'exécution à chaque fois. L'utilisation de cette option n'est pas courante car elle ralentit l'exécution de la procédure stockée ; cette dernière doit en effet être recompilée à chaque exécution.
Si vous voulez recompiler uniquement des requêtes individuelles dans la procédure stockée plutôt que la totalité de cette dernière, spécifiez l'indicateur de requête RECOMPILE pour chaque requête que vous voulez recompiler. Ce comportement reproduit la recompilation au niveau de l'instruction de SQL Server mentionnée plus haut, mais en plus d'utiliser les valeurs de paramètres actuelles de la procédure stockée, l'indicateur de requête RECOMPILE se sert également des valeurs des éventuelles variables locales dans la procédure stockée pendant la compilation de l'instruction. Utilisez cette option lorsque des valeurs non courantes ou provisoires sont utilisées uniquement dans un sous-ensemble de requêtes appartenant à la procédure stockée. Pour plus d'informations, consultez Indicateur de requête (Transact-SQL).
Vous pouvez forcer la recompilation d'une procédure stockée en spécifiant l'option WITH RECOMPILE lorsque vous l'exécutez. Utilisez cette option uniquement lorsque le paramètre fourni n'est pas courant ou si les données ont changé de façon significative depuis la création de la procédure.
Notes
Si un objet référencé par une procédure stockée est supprimé ou renommé, l'exécution de la procédure produit une erreur. Cependant, si un objet référencé dans une procédure stockée est remplacé par un objet du même nom, la procédure s'exécute sans qu'il soit nécessaire de la recréer.
Pour recompiler une procédure stockée à son exécution suivante