Repères de plan

S’applique à : SQL Server base de données Azure SQL Azure 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 fait correspondre l’instruction Transact-SQL au repère de plan et attache la clause OPTION à la requête au moment de l’exécution ou fait appel au plan de requête spécifié. Comme l’optimiseur de requête SQL Server sélectionne généralement le meilleur plan d’exécution pour une requête, nous recommandons de ne recourir aux repères de plan qu’en dernier ressort, et à condition d’être un développeur ou un administrateur de base de données expérimenté.

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 toutes les éditions 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, comme il y a relativement peu de commandes client qui proviennent d’Australie, les performances diminuent lorsque la requête s’exécute à l’aide de valeurs de paramètre de pays/régions contenant plus de commandes client. 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 guide de plan SQL correspond aux requêtes qui s’exécutent dans le contexte d’instructions Transact-SQL autonomes et de lots qui ne font 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)';

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 fait correspondre les valeurs des arguments @module_or_batch et @params avec une requête en comparant les deux valeurs caractère par caractère. Cela signifie que vous devez fournir le texte exactement tel que SQL Server le reçoit dans le traitement réel.

Quand @type = ’SQL’ et @module_or_batch est défini sur NULL, la valeur de @module_or_batch est 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é.

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.

Tâches associées

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)