Recompiler une procédure stockée

Article
04/02/2014

Cette rubrique explique comment recompiler une procédure stockée dans SQL Server 2012 à l'aide de Transact-SQL. Il existe trois manières d'effectuer cette opération : via l'option WITH RECOMPILE dans la définition de la procédure ou lorsque la procédure est appelée, via l'indicateur de requête RECOMPILE sur des instructions ou en utilisant la procédure stockée système sp_recompile. Cette rubrique décrit l'utilisation de l'option WITH RECOMPILE lors de la création d'une définition de procédure et de l'exécution d'une procédure existante. Elle décrit également l'utilisation de la procédure stockée système sp_recompile pour recompiler une procédure existante.

Dans cette rubrique

Avant de commencer :

Recommandations

Sécurité
Pour recompiler une procédure stockée à l'aide de :

Transact-SQL

Avant de commencer

Recommandations

Lorsqu'une procédure est compilée pour la première fois ou recompilée, son plan de requête est optimisé pour l'état actuel de la base de données et de ses objets. Si une base de données subit des modifications significatives au niveau de ses données ou de sa structure, le fait de recompiler la procédure met à jour et optimise son plan de requête en fonction ces modifications. Cela peut améliorer les performances de traitement de la procédure.
Parfois, la recompilation de procédure doit être forcée, et parfois elle se produit automatiquement. La recompilation automatique se produit chaque fois que SQL Server redémarre. Elle se produit également si une table sous-jacente référencée par la procédure a été modifiée au niveau de sa conception physique.
Une autre raison pour forcer la recompilation d'une procédure est de contrer le comportement de détection des paramètres de la compilation de la procédure. Lorsque SQL Server exécute des procédures, 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 utilisées pour appeler ultérieurement la procédure, celle-ci bénéficie du plan de requête à chaque compilation et exécution. Si les valeurs des paramètres de la procédure sont atypiques, l'application forcée d'une recompilation et un nouveau plan défini en fonction des valeurs des paramètres peuvent améliorer les performances.
SQL Server permet la recompilation des procédures au niveau de l'instruction. Lorsque SQL Server recompile des procédures, seule l'instruction ayant provoqué la recompilation est compilée, et non la procédure toute entière.
Si certaines requêtes d'une procédure utilisent régulièrement des valeurs atypiques ou temporaires, les performances des procédures peuvent être améliorées en utilisant l'indicateur de requête RECOMPILE à l'intérieur de ces requêtes. Étant donné que seules les requêtes utilisant l'indicateur de requête seront recompilées au lieu de la procédure complète, le comportement de recompilation de l'instruction de SQL Server est reproduit. Cependant, en plus d'utiliser les valeurs des paramètres actuels de la procédure, l'indicateur de requête RECOMPILE utilise également les valeurs des variables locales à l'intérieur de la procédure stockée lorsque vous compilez l'instruction. Pour plus d'informations, consultez Indicateur de requête (Transact-SQL).

Sécurité

Autorisations

Option WITH RECOMPILE
Si cette option est utilisée lorsque la définition de la procédure est créée, elle nécessite l'autorisation CREATE PROCEDURE dans la base de données et l'autorisation ALTER sur le schéma dans lequel la procédure est créée.

Si cette option est utilisée dans une instruction EXECUTE, elle nécessite des autorisations EXECUTE sur la procédure. Aucune autorisation n'est requise sur l'instruction EXECUTE elle-même, mais une autorisation est requise sur la procédure référencée dans l'instruction EXECUTE. Pour plus d'informations, consultez EXECUTE (Transact-SQL).
Indicateur de requête RECOMPILE
Cette fonctionnalité est utilisée lorsque la procédure est créée et l'indicateur est inclus dans les instructions Transact-SQL de la procédure. Par conséquent, elle nécessite l'autorisation CREATE PROCEDURE dans la base de données et l'autorisation ALTER sur le schéma dans lequel la procédure est créée.
Procédure stockée système sp_recompile
Nécessite l'autorisation ALTER pour la procédure spécifiée.

Icône de flèche utilisée avec le lien Retour en haut [Haut de la page]

Utilisation de Transact-SQL

Pour recompiler une procédure stockée à l'aide de l'option WITH RECOMPILE

Connectez-vous au Moteur de base de données.
Dans la barre d'outils standard, cliquez sur Nouvelle requête.
Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. Cet exemple crée la définition de la procédure.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v 
    JOIN Purchasing.ProductVendor AS pv 
      ON v.BusinessEntityID = pv.BusinessEntityID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

Pour recompiler une procédure stockée à l'aide de l'option WITH RECOMPILE

Connectez-vous au Moteur de base de données.
Dans la barre d'outils standard, cliquez sur Nouvelle requête.
Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. Cet exemple crée une procédure simple qui retourne tous les employés (prénom et nom), leur titre et le nom de leur service à partir d'une vue.

Puis, copiez et collez le second exemple de code dans la fenêtre de requête et cliquez sur Exécuter. Cela exécute la procédure et recompile son plan de requête.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

USE AdventureWorks2012;
GO
EXECUTE HumanResources.uspGetAllEmployees WITH RECOMPILE;
GO

Pour recompiler une procédure stockée à l'aide de sp_recompile

Connectez-vous au Moteur de base de données.
Dans la barre d'outils standard, cliquez sur Nouvelle requête.
Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. Cet exemple crée une procédure simple qui retourne tous les employés (prénom et nom), leur titre et le nom de leur service à partir d'une vue.

Ensuite, copiez et collez l'exemple suivant dans la fenêtre de requête et cliquez sur Exécuter. Cela n'exécute pas la procédure mais la marque pour la recompilation de sorte que son plan de requête sera mis à jour à la prochaine exécution.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

USE AdventureWorks2012;
GO
EXEC sp_recompile N'HumanResources.uspGetAllEmployees';
GO

Icône de flèche utilisée avec le lien Retour en haut [Haut de la page]