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

[!REMARQUE]

Les repères de plan ne peuvent pas être utilisés dans toutes les éditions de Microsoft SQL Server. Pour obtenir une liste des fonctionnalités prises en charge par les éditions de SQL Server, consultez Fonctionnalités prises en charge par les éditions de SQL Server 2012. 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 demeurent 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 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, 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 contenant plus de commandes client. Dans la mesure où les États-Unis constituent le pays qui arrive en première position en termes de commandes remportées, 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 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 soumises 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)';
  

  Important

  Les valeurs fournies pour les arguments @params et @module_or_batch 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 le Générateur de profils SQL Server 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 :

  • 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 pour les 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.

Lorsque @type = 'SQL' et @module\_or\_batch ont la valeur NULL, la valeur de @module\_or\_batch a la valeur 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 sa soumission à 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é.

[!REMARQUE]

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

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

Référence

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)