Repères de plan

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Les repères de plan vous permettent d’optimiser les performances des requêtes lorsque vous ne pouvez pas ou ne souhaitez pas modifier directement le texte de la requête réelle dans SQL Server. Les repères de plan influencent l'optimisation des requêtes en attachant des indicateurs de requête ou un plan fixe de requête à celles-ci. Les repères de plan s'avèrent utiles lorsqu'un petit sous-ensemble de requêtes d'une application de base de données fournie par un tiers ne fonctionne pas comme prévu. Dans le repère de plan, vous spécifiez l'instruction Transact-SQL que vous voulez optimiser et une clause OPTION contenant les indicateurs de requête ou un plan de requête spécifique à utiliser pour optimiser la requête. Lorsque la requête s’exécute, SQL Server correspond à l’instruction Transact-SQL au repère de plan et attache la clause OPTION à la requête au moment de l’exécution ou utilise le plan de requête spécifié. Étant donné que l’optimiseur de requête SQL Server sélectionne généralement le meilleur plan d’exécution pour une requête, nous vous recommandons uniquement d’utiliser des guides de plan comme dernier recours pour les développeurs expérimentés et les administrateurs de base de données.

Note

Les indicateurs du Magasin des requêtes fournissent une méthode plus facile à utiliser pour mettre en forme des plans de requête sans modifier le code de l’application. Les indicateurs du Magasin des requêtes sont plus simples que les repères de plan. Les indicateurs du Magasin des requêtes sont disponibles dans Azure SQL Database et Azure SQL Managed Instance, et dans SQL Server 2022 (16.x) et versions ultérieures.

Le nombre total de repères de plan que vous pouvez créer est uniquement tributaire des ressources système disponibles. Toutefois, les repères de plan doivent se limiter au traitement des requêtes critiques ciblées à des fins d'amélioration ou de stabilisation des performances. Les repères de plan ne doivent pas influencer la majeure partie de la charge de requête d'une application déployée.

Le plan d’exécution obtenu, qui est forcé par cette fonctionnalité, est le même que le plan forcé ou est similaire à celui-ci. Étant donné que le plan obtenu ne peut pas être identique au plan spécifié par le repère de plan, les performances des plans peuvent varier. Dans de rares cas, la perte de performances peut être importante. Dans ce cas, l’administrateur doit supprimer le plan forcé.

Les repères de plan ne peuvent pas être utilisés dans chaque édition de Microsoft SQL Server. Pour obtenir la liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Fonctionnalités prise en charge par les éditions de SQL Server 2016. Les repères de plan sont visibles dans n'importe quelle édition. En outre, vous pouvez attacher une base de données qui contient des repères de plan à n'importe quelle édition. Les repères de plan restent intacts lorsque vous restaurez ou attachez une base de données à une version mise à niveau de SQL Server.

Types de repères de plan

Il est possible de créer les types de repères de plan suivants.

OBJECT, repère de plan

Un repère de plan OBJECT correspond à des requêtes qui s’exécutent dans le contexte de procédures stockées Transact-SQL, de fonctions scalaires définies par l’utilisateur, de fonctions table à instructions multiples définies par l’utilisateur et de déclencheurs DML.

Supposons que la procédure stockée suivante, qui accepte le paramètre @Country_region, figure dans une application de base de données déployée dans la base de données AdventureWorks2022 :

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c,   
        Sales.SalesTerritory AS t  
    WHERE h.CustomerID = c.CustomerID  
        AND c.TerritoryID = t.TerritoryID  
        AND CountryRegionCode = @Country_region  
END;

Supposons que cette procédure stockée a été compilée et optimisée pour @Country_region = N'AU' (Australie). Toutefois, étant donné qu’il existe relativement peu de commandes commerciales provenant de l’Australie, les performances diminuent lorsque la requête s’exécute à l’aide de valeurs de paramètres de pays/régions avec plus de commandes commerciales. La plupart des commandes provenant des États-Unis, un plan de requête généré pour @Country_region = N'US' obtiendrait de meilleures performances pour toutes les valeurs possibles du paramètre @Country_region.

Vous pouvez résoudre ce problème en modifiant la procédure stockée pour ajouter l'indicateur de requête OPTIMIZE FOR à la requête. Toutefois, étant donné que la procédure stockée se trouve dans une application déployée, vous ne pouvez pas modifier directement le code de cette dernière. Vous pouvez en revanche créer le repère de plan suivant dans la base de données AdventureWorks2022 .

sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,  
        Sales.Customer AS c,  
        Sales.SalesTerritory AS t  
        WHERE h.CustomerID = c.CustomerID   
            AND c.TerritoryID = t.TerritoryID  
            AND CountryRegionCode = @Country_region',  
@type = N'OBJECT',  
@module_or_batch = N'Sales.GetSalesOrderByCountry',  
@params = NULL,  
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

Lorsque la requête spécifiée dans l'instruction sp_create_plan_guide s'exécute, elle est modifiée avant l'optimisation de façon à inclure la clause OPTIMIZE FOR (@Country = N''US'') .

Repère de plan SQL

Un repère de plan SQL correspond à des requêtes qui s’exécutent dans le contexte de lots et d’instructions Transact-SQL autonomes ne faisant pas partie d’un objet de base de données. Les repères de plan SQL peuvent également être employés pour les requêtes paramétrables au format spécifié. Les repères de plan SQL s’appliquent aux instructions et aux lots Transact-SQL autonomes. Le plus souvent, ces instructions sont envoyées par une application à l’aide de la procédure stockée système sp_executesql . Imaginons par exemple le traitement autonome suivant :

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Pour empêcher la génération d'un plan d'exécution parallèle sur cette requête, créez le repère de plan suivant et affectez à l'indicateur de requête MAXDOP la valeur 1 dans le paramètre @hints .

sp_create_plan_guide   
@name = N'Guide2',   
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',  
@type = N'SQL',  
@module_or_batch = NULL,   
@params = NULL,   
@hints = N'OPTION (MAXDOP 1)';

Imaginons un autre exemple, où l’instruction SQL suivante est envoyée à l’aide de sp_executesql.

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;

Pour créer un plan unique pour chaque exécution de cette requête, créez le repère de plan suivant et utilisez l’indicateur de requête OPTION (RECOMPILE) dans le paramètre @hints.

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

Important

Les valeurs fournies pour les arguments @module_or_batch et @params de l'instruction sp_create_plan guide doivent correspondre au texte correspondant soumis dans la requête réelle. Pour plus d’informations, consultez sp_create_plan_guide (Transact-SQL) et Utiliser SQL Server Profiler pour créer et tester des repères de plan.

Les repères de plan SQL peuvent aussi être créés sur des requêtes paramétrables au même format lorsque l'option de base de données PARAMETERIZATION a la valeur FORCED ou lorsqu'un repère de plan TEMPLATE est créé pour spécifier qu'une classe de requête est paramétrable.

TEMPLATE, repère de plan

Un repère de plan TEMPLATE correspond à des requêtes autonomes paramétrables au format spécifié. Il s'emploie pour remplacer l'option de base de données PARAMETERIZATION par une classe de requêtes.

Vous pouvez créer un repère de plan TEMPLATE dans l'une des circonstances suivantes :

  • Quand l’option de base de données PARAMETERIZATION a la valeur FORCED, mais qu’il y a des requêtes que vous voulez compiler selon les règles du paramétrage simple

  • Quand l’option de base de données PARAMETERIZATION a la valeur SIMPLE (l’option par défaut), mais que vous voulez appliquer une tentative de paramétrage forcé à une classe de requêtes

Paramétrage de la mise en correspondance du repère de plan

Les repères de plan sont limités à la base de données dans laquelle ils sont créés. Par conséquent, seuls les repères de plan existant dans la base de données qui est active lors de l'exécution d'une requête peuvent être mis en correspondance avec cette requête. Supposons que AdventureWorks2022 est la base de données active et que la requête suivante est exécutée :

SELECT FirstName, LastName FROM Person.Person;

Seuls les repères de plan de la base de données AdventureWorks2022 peuvent être mis en correspondance avec cette requête. Supposons maintenant que AdventureWorks2022 est la base de données active et que les instructions suivantes sont exécutées :

USE DB1; 
SELECT FirstName, LastName FROM Person.Person;

Seuls les repères de plan de DB1 peuvent être mis en correspondance avec la requête, car celle-ci s'exécute dans le contexte de DB1.

Pour les repères de plan BASÉS sur SQL ou TEMPLATE, SQL Server correspond aux valeurs pour les arguments et @params les @module_or_batch valeurs d’une requête en comparant les deux valeurs par caractère. Cela signifie que vous devez fournir le texte exactement comme SQL Server le reçoit dans le lot réel.

Quand @type = 'SQL' et @module_or_batch est défini sur NULL, la valeur est @module_or_batch définie sur la valeur de @stmt. Cela signifie que la valeur de statement_text doit être fournie dans le format identique, caractère pour caractère, tel qu’il est soumis à SQL Server. Aucune conversion interne n'est effectuée pour faciliter cette correspondance.

Lorsqu'un repère de plan normal (SQL ou OBJECT) et un repère de plan TEMPLATE peuvent s'appliquer tous les deux à une instruction, seul le repère de plan normal est utilisé.

Note

Le traitement qui contient l’instruction sur laquelle vous souhaitez créer un repère de plan ne peut pas contenir une instruction de base de données USE.

Effet du repère de plan sur le cache du plan

La création d'un repère de plan sur un module supprime le plan de requête pour ce module du cache du plan. La création d'un repère de plan de type OBJET ou SQL sur un lot supprime le plan de requête pour un lot qui a la même valeur de hachage. La création d'un repère de plan de type TEMPLATE supprime tous les lots comprenant une instruction unique du cache du plan dans cette base de données.

Task Rubrique
Explique comment créer un repère de plan. Créer un repère de plan
Explique comment créer un repère de plan pour les requêtes paramétrables. Créer un repère de plan pour les requêtes paramétrables
Explique comment contrôler le comportement du paramétrage de requête à l'aide de repères de plan. Spécifier le comportement du paramétrage de requêtes grâce aux repères de plan
Explique comment inclure un plan de requête fixe dans un repère de plan. Appliquer un plan de requête fixe à un repère de plan
Explique comment spécifier des indicateurs de requête dans un repère de plan. Attacher des indicateurs de requête à un repère de plan
Explique comment afficher les propriétés d'un repère de plan. Afficher les propriétés du repère de plan
Explique comment utiliser SQL Server Profiler pour créer et tester des repères de plan. Utiliser SQL Server Profiler pour créer et tester des repères de plan
Explique comment valider des repères de plan. Valider des repères de plan après la mise à niveau

Voir aussi

sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide (Transact-SQL)