Utilisation du Générateur de profils SQL Server pour créer et tester des repères de plan

  • Article

Mis à jour : 5 décembre 2005

Lorsque vous créez un repère de plan, vous pouvez recourir à Générateur de profils SQL Server pour capturer le texte de requête exact à utiliser dans l'argument statement_text de la procédure stockée sp_create_plan_guide. Ainsi, au moment de la compilation, le repère de plan correspondra à la requête. Une fois le repère de plan créé, vous pouvez également utiliser Générateur de profils SQL Server pour tester la correspondance effective du repère de plan à la requête. En règle générale, vous devez tester les repères de plan à l'aide de Générateur de profils SQL Server pour vérifier que la requête correspond au repère de plan.

Capture du texte de requête à l'aide du Générateur de profils SQL Server

Si vous exécutez une requête et capturez le texte tel qu'il a été soumis à SQL Server à l'aide du Générateur de profils SQL Server, vous pouvez créer un repère de plan de type SQL ou TEMPLATE qui corresponde exactement à ce texte. Cela permet de faire en sorte que le repère de plan soit utilisé par l'optimiseur de requête.

Imaginons la requête suivante soumise par une application en tant que lot autonome :

SELECT COUNT(*) AS c
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.OrderDate BETWEEN '20000101' and '20050101'

Supposons que vous souhaitiez exécuter cette requête à l'aide d'une opération de jointure de fusion, mais que SHOWPLAN indique qu'elle n'est pas en train d'utiliser une jointure de fusion. Étant donné que vous ne pouvez pas modifier la requête directement dans l'application, vous créez un repère de plan pour spécifier que l'indicateur de requête MERGE JOIN soit ajouté à la requête au moment de la compilation.

Pour capturer le texte de la requête tel que SQL Server le reçoit, procédez comme suit :

  1. Démarrez une trace du Générateur de profils SQL Server, en veillant à ce que le type d'événement SQL:BatchStarting soit sélectionné.
  2. Faites exécuter la requête par l'application.
  3. Suspendez la trace du Générateur de profils SQL Server.
  4. Cliquez sur l'événement SQL:BatchStarting qui correspond à la requête.
  5. Cliquez avec le bouton droit et sélectionnez Extraire les données d'événement.
    ms188255.note(fr-fr,SQL.90).gifImportant :
    N'essayez pas de copier le texte du lot en le sélectionnant dans le volet inférieur de la fenêtre de trace du Générateur de profils. Le repère de plan que vous créez risque alors de ne pas correspondre au lot d'origine.
  6. Enregistrez les données d'événement dans un fichier. Il s'agit du texte de lot.
  7. Ouvrez le fichier du texte de lot dans l'application Bloc-notes puis copiez le texte dans le Presse-papiers.
  8. Créez le repère de plan puis collez le texte copié entre les guillemets ('') de l'argument @stmt. Vous devez isoler tout guillemet simple contenu dans l'argument @stmt en le faisant précéder par un autre guillemet simple. Faites attention de ne pas ajouter ou supprimer d'autres caractères lorsque vous insérez ces guillemets simples. Par exemple, le littéral de date '20000101' doit être délimité de la façon suivante : ''20000101''.

Voici le repère de plan :

EXEC sp_create_plan_guide 
    @name = N'MyGuide1',
    @stmt = N'<paste the text copied from the batch text file here>',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (MERGE JOIN)';

Test des repères de plan à l'aide du Générateur de profils SQL Server

Pour vérifier qu'un repère de plan correspond à une requête, procédez comme suit :

  1. Démarrez une trace du Générateur de profils SQL Server, en veillant à ce que le type d'événement Showplan XML, situé sous le nœud Performances, soit sélectionné.
  2. Faites exécuter la requête par l'application.
  3. Suspendez la trace du Générateur de profils SQL Server.
  4. Recherchez l'événement Showplan XML de la requête affectée.
  5. Si le repère de plan est de type OBJECT ou SQL, vérifiez que l'événement Showplan XML contient les attributs PlanGuideDB et PlanGuideName du repère de plan qui doit correspondre à la requête. Ou, dans le cas d'un repère de plan TEMPLATE, vérifiez que l'événement Showplan XML contient les attributs TemplatePlanGuideDB et TemplatePlanGuideName du repère de plan prévu. Cette opération vérifie que le repère de plan fonctionne. Ces attributs sont contenus sous l'élément <StmtSimple> du plan.

Voir aussi

Concepts

Optimisation des requêtes dans les applications déployées à l'aide des repères de plan

Autres ressources

Performance des requêtes
sp_create_plan_guide (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version Historique

5 décembre 2005
Nouveau contenu :
  • Des informations ont été ajoutées à l'étape 8, sous « Capture du texte de requête à l'aide du Générateur de profils SQL Server », concernant la délimitation des guillemets simples dans l'argument @stmt.
  • Des informations ont été ajoutées à l'étape 5, sous « Test des repères de plan à l'aide du Générateur de profils SQL Server », concernant la vérification des Showplans XML pour les repères de plan TEMPLATE.