Articles

Recompiler en Procédure Stockée

by admin
  • 28/10/2019
  • 4 minutes à lire
    • s
    • M
    • m
    • d
    • m
    • +2

s’applique à: Serveur SQL (toutes les versions prises en charge) Base de données SQL Azure Instance gérée SQL Azure Azure Synapse Analytics div>Entrepôt de données parallèle

Cette rubrique décrit comment recompiler une procédure stockée dans SQL Server à l’aide de Transact-SQL. Il y a trois façons de le faire: AVEC l’option RECOMPILER dans la définition de la procédure ou lorsque la procédure est appelée, l’indice de requête RECOMPILER sur des instructions individuelles, ou en utilisant la procédure stockée du système sp_recompile. Cette rubrique décrit l’utilisation de l’option AVEC RECOMPILER 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 du 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, en utilisant:

    Transact-SQL

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, la 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 y a des moments où la recompilation de la procédure doit être forcée et d’autres moments où elle se produit automatiquement. La recompilation automatique se produit chaque fois que SQL Server est redémarré. Cela 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 est de contrecarrer le comportement de « reniflage de paramètres » de la compilation de procédures. Lorsque SQL Server exécute des procédures, toutes les valeurs de paramètres utilisées par la procédure lors de sa compilation sont incluses dans 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 bénéficie du plan de requête chaque fois qu’elle se compile et s’exécute. Si les valeurs de paramètres de la procédure sont souvent 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 des procédures au niveau des instructions. Lorsque SQL Server recompile des procédures stockées, seule l’instruction à l’origine de 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 de la procédure peuvent être améliorées en utilisant l’indice de RECOMPILATION des requêtes dans ces requêtes. Étant donné que seules les requêtes utilisant l’indice de requête seront recompilées au lieu de la procédure complète, le comportement de recompilation au niveau des instructions de SQL Server est imité. Mais en plus d’utiliser les valeurs de paramètres actuelles de la procédure, l’indice de requête de RECOMPILATION utilise également les valeurs de toutes les variables locales dans la procédure stockée lorsque vous compilez l’instruction. Pour plus d’informations, consultez Indice de requête (Transact-SQL).

Sécurité

Permissions

AVEC l’option RECOMPILER
Si cette option est utilisée lors de la création de la définition de procédure, elle nécessite l’autorisation de CRÉER une PROCÉDURE dans la base de données et l’autorisation de MODIFIER 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 d’EXÉCUTION sur la procédure. Les autorisations ne sont pas requises sur l’instruction EXECUTE elle-même, mais les autorisations EXECUTE sont requises sur la procédure référencée dans l’instruction EXECUTE. Pour plus d’informations, consultez EXECUTE (Transact-SQL).

RECOMPILER l’indice de requête
Cette fonctionnalité est utilisée lorsque la procédure est créée et que l’indice est inclus dans les instructions Transact-SQL de la procédure. Par conséquent, il nécessite une autorisation de CRÉATION de PROCÉDURE dans la base de données et une autorisation de MODIFICATION sur le schéma dans lequel la procédure est créée.

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

En utilisant Transact-SQL

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

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

  3. Copiez et collez l’exemple suivant dans la fenêtre de requête et 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. Cela exécute la procédure et recompile le plan de requête de la procédure.

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 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'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
SUPPRIMER LA PROCÉDURE (Transact-SQL)