Recompiler une procédure stockée

Article
07/15/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)

Cet article 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.

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 tout 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).

Remarque

Dans les pools dédiés et serverless d’Azure Synapse Analytics, les procédures stockées ne sont pas du code précompilé et ne peuvent donc pas être recompilées. Pour plus d’informations, consultez Utilisation de procédures stockées pour les pools SQL dédiés dans Azure Synapse Analytics

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

RECOMPILE, indicateur de requête

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édures stockées système sp_recompile

Nécessite l'autorisation ALTER pour la procédure spécifiée.

Utilisation de Transact-SQL

Connectez-vous au Moteur de base de données.
Dans la barre d’outils Standard, sélectionnez Nouvelle requête.

Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter. Cet exemple crée la définition de la procédure.

USE AdventureWorks2022;  
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 sélectionnez Exécuter. La procédure est alors exécutée et son plan de requête recompilé.

USE AdventureWorks2022;  
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 sélectionnez 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 AdventureWorks2022;  
GO  
EXEC sp_recompile N'dbo.uspProductByVendor';   
GO

Étapes suivantes

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)

Partage via