Utiliser SQL Server Profiler pour créer et tester des repères de plan

S’applique à :SQL ServerAzure SQL Database

Lorsque vous créez un repère de plan, vous pouvez utiliser SQL Server Profiler 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éé, SQL Server Profiler peut également être utilisé pour tester que le guide de plan est, en fait, mis en correspondance avec la requête. En règle générale, vous devez tester les repères de plan à l’aide de SQL Server Profiler pour vérifier que votre requête est mise en correspondance avec votre guide 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 exactement tel qu’il a été soumis à SQL Server à l’aide de SQL Server Profiler, vous pouvez créer un repère de plan de type SQL ou TEMPLATE qui correspond exactement au texte de la requête. 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 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 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 exactement comme SQL Server le reçoit, procédez comme suit :

1. Démarrez une trace SQL Server Profiler, 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 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. Le repère de plan que vous créez risque alors de ne pas correspondre au traitement d'origine.

6. Enregistrez les données d'événement dans un fichier. Il s'agit du texte de traitement.

7. Ouvrez le fichier du texte de traitement 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 de repères 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. Suspendez la trace sql Server Profiler.

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

   Note

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

sp_create_plan_guide (Transact-SQL)