Partager via

Recompiler une procédure stockée

Cette rubrique explique comment recompiler une procédure stockée dans SQL Server 2014 à l’aide de Transact-SQL. Il existe trois façons de procéder : utiliser l’option spécifiée dans la définition de procédure ou à l’appel de la procédure, ajouter l'indicateur de requête RECOMPILE sur des instructions individuelles, ou utiliser 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. Il 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

  • Lorsqu’une procédure est compilée pour la première fois ou recompilée, le plan de requête de la procédure 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 importantes de ses données ou de sa structure, recompilation d’une procédure met à jour et optimise le plan de requête de la procédure pour ces modifications. Cela peut améliorer les performances de traitement de la procédure.

  • Il existe des moments où la recompilation de procédure doit être forcée et d’autres fois lorsqu’elle se produit automatiquement. La recompilation automatique se produit chaque fois que SQL Server est redémarré. Elle se produit également si une table sous-jacente référencée par la procédure a subi des modifications de conception physique.

  • Une autre raison de forcer une procédure à recompiler consiste à contrecarrer le comportement de compilation des procédures « sniffing de paramètre ». Lorsque SQL Server exécute des procédures, toutes les valeurs de paramètre utilisées par la procédure lorsqu’elle compile sont incluses dans le cadre de la génération du plan de requête. Si ces valeurs représentent les valeurs typiques avec lesquelles la procédure est ensuite appelée, la procédure tire parti du plan de requête chaque fois qu’elle compile et s’exécute. Si les valeurs de paramètre sur la procédure sont fréquemment atypiques, forcer une recompilation de la procédure et un nouveau plan basé sur différentes valeurs de paramètres peut améliorer les performances.

  • SQL Server propose une recompilation au niveau de l’instruction des procédures. Lorsque SQL Server recompile des procédures stockées, seule l’instruction qui a provoqué la recompilation est compilée, au lieu de la procédure complète.

  • 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 à l’aide de 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 sont recompilées au lieu de la procédure complète, le comportement de recompilation au niveau de l’instruction de SQL Server est imité. En plus d’utiliser les valeurs de paramètre actuelles 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 l’indicateur de requête (Transact-SQL).

Sécurité

Autorisations

WITH RECOMPILE Option
Si cette option est utilisée lorsque la définition de 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. Les autorisations ne sont pas requises sur l’instruction EXECUTE elle-même, mais les autorisations d’exécution sont requises sur la procédure référencée dans l’instruction EXECUTE. Pour plus d’informations, consultez EXECUTE (Transact-SQL).

RECOMPILE Indicateur de requête
Cette fonctionnalité est utilisée lors de la création de la procédure, et l'indice 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.

sp_recompile Procédure stockée système
Nécessite l’autorisation ALTER sur la procédure spécifiée.

Utilisation de Transact-SQL

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

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

  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 une procédure simple qui retourne tous les employés (prénoms et noms de famille fournis), leurs intitulés de postes et leurs noms de département à partir d'une vue.

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

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

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

  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 une procédure simple qui retourne tous les employés (prénoms et noms fournis), leurs titres de poste et les noms des départements à partir d’une vue.

    Ensuite, copiez et collez l’exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. Cela n’exécute pas la procédure, mais elle marque la procédure à recompiler afin que son plan de requête soit mis à jour la prochaine fois que la procédure est exécutée.

USE AdventureWorks2012;  
GO  
EXEC sp_recompile N'HumanResources.uspGetAllEmployees';  
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)

  • Last updated on