Repères de plan

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 2014. 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é.

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.

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 @Countryparamètre _region , se trouve dans une application de base de données déployée sur la base de données AdventureWorks2012 :

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é que relativement peu de commandes client proviennent d’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 client. Étant donné que la plupart des commandes client proviennent du États-Unis, un plan de requête généré pour @Country_region = N'US' serait probablement plus performant pour toutes les valeurs possibles du @Countryparamètre _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 AdventureWorks2012 .

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)';  

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 :

• lorsque l'option de base de données PARAMETERIZATION est définie à FORCED, mais qu'il y a des requêtes que vous voulez compiler selon les règles du paramétrage simple ;

• lorsque l'option de base de données PARAMETERIZATION est définie à 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 AdventureWorks2012 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 AdventureWorks2012 peuvent être mis en correspondance avec cette requête. Supposons maintenant que AdventureWorks2012 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 que @module_or_batch a la valeur NULL, la valeur définie pour de @module_or_batch est celle de @stmt. Autrement dit, la valeur de statement_text doit être fournie dans un format identique (au caractère près) à celui utilisé lors de son envoi à 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)