Utiliser SQL Server Profiler pour créer et tester des guides de plan

S’applique à :SQL ServerAzure SQL Database

Lorsque vous créez un repère de plan, vous pouvez recourir au Générateur de profils SQL pour capturer le texte de requête exact à utiliser dans l’argument statement_text de la procédure stockée sp_create_plan_guide. Cela permet de s’assurer que le guide de plan sera mis en correspondance avec la requête lors de la compilation. Une fois le repère de plan créé, vous pouvez également utiliser le Générateur de profils SQL 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 du Générateur de profils SQL pour vérifier que la requête correspond au repère de plan.

Capture de texte de requête à l’aide de SQL Server Profiler

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, vous pouvez créer un repère de plan de type SQL ou TEMPLATE qui corresponde exactement à ce texte. Cela garantit que le guide de plan est utilisé par l’optimiseur de requêtes.

Imaginons la requête suivante soumise par une application en tant que traitement 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 que cette requête s'exécute à l'aide d'une jointure de fusion, mais que SHOWPLAN indique que la requête n'utilise pas de jointure de fusion. Vous ne pouvez pas modifier la requête directement dans l’application. Vous créez plutôt un guide de plan pour spécifier que l’indicateur MERGE de requête JOIN doit être 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, 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. Mettre en pause la trace SQL Server Profiler.

  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.

    Important

    N'essayez pas de copier le texte du traitement en le sélectionnant dans le volet inférieur de la fenêtre de trace du Générateur de profils. Cela peut faire en sorte que le guide de plan que vous créez ne corresponde pas au batch d’origine.

  6. Enregistrez les données d'événement dans un fichier. Ceci est le texte du lot.

  7. Ouvrez le fichier texte batch dans le Bloc-notes et 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 guide du 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 guides de plan à l’aide de SQL Server Profiler

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

  1. Démarrez une trace SQL Server Profiler, en veillant à ce que le type d’événement Showplan XML soit sélectionné (situé sous le nœud Performances ).

  2. Faites exécuter la requête par l'application.

  3. Mettre en pause la trace SQL Server Profiler.

  4. Recherchez l'événement Showplan XML de la requête affectée.

    Remarque

    L'événement Showplan XML For Query Compile ne peut pas être utilisé. PlanGuideDB n'existe pas dans cet événement.

  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. Cela vérifie que le guide de plan fonctionne. Ces attributs sont contenus sous l’élément <StmtSimple>> du plan.

Voir aussi

sp_create_plan_guide (Transact-SQL)

  • Last updated on