Recompiler une procédure stockée

S’applique à : ouiSQL Server (toutes les versions prises en charge) OuiAzure SQL Database OuiAzure SQL Managed Instance ouiAzure Synapse Analytics OuiParallel Data Warehouse

Cette rubrique explique comment recompiler une procédure stockée dans SQL Server à 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

  • Quand 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, la recompilation de la procédure a pour effet de mettre à jour et d’optimiser son plan de requête en fonction de ces modifications. Les performances de traitement de la procédure peuvent s’en trouver améliorées.

  • 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 Serverest 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

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.

Utilisation de Transact-SQL

  1. Connectez-vous au Moteur de base de données.

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

  3. 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;  
    

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

Sélectionnez Nouvelle requête, puis copiez et collez l’exemple de code suivant dans la fenêtre de requête et cliquez sur Exécuter. La procédure est alors exécutée et son plan de requête recompilé.

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

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

Sélectionnez Nouvelle requête, puis 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  
EXEC sp_recompile N'dbo.uspProductByVendor';   
GO

Voir aussi

Créer une procédure stockée
Modifier une procédure stockée
Renommer une procédure stockée
Afficher la définition d'une procédure stockée
Afficher les dépendances d’une procédure stockée
DROP PROCEDURE (Transact-SQL)