Usare SQL Server Profiler per creare e sottoporre a test le guide di piano

  • Articolo

Si applica a:SQL ServerDatabase SQL di Azure

Quando si crea una guida di piano è possibile usare SQL Server Profiler per acquisire il testo esatto della query da usare nell'argomento statement_text della stored procedure sp_create_plan_guide. Questo garantisce che in fase di compilazione la guida di piano corrisponderà alla query. Dopo la creazione della guida di piano è possibile utilizzare ancora SQL Server Profiler per verificare che la guida di piano corrisponda effettivamente alla query. È in genere consigliabile testare le guide di piano tramite SQL Server Profiler per verificarne la corrispondenza con la query.

Acquisizione del testo di una query tramite SQL Server Profiler

Se si esegue una query e se ne acquisisce il testo esattamente come è stato inviato a SQL Server tramite SQL Server Profiler, sarà possibile creare una guida di piano di tipo SQL o TEMPLATE che corrisponderà esattamente al testo della query. Questo garantisce che la guida di piano verrà utilizzata da Query Optimizer.

Si consideri la query seguente, inviata da un'applicazione come batch autonomo:

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

Si supponga di voler eseguire la query tramite un'operazione di merge join, ma che SHOWPLAN indichi che la query non sta utilizzando un merge join. Non è possibile modificare la query direttamente nell'applicazione, pertanto è necessario creare una guida di piano per specificare che l'hint per la query MERGE JOIN venga accodato alla query in fase di compilazione.

Per acquisire il testo della query esattamente come viene ricevuto da SQL Server, eseguire la procedura seguente:

  1. Avviare una traccia di SQL Server Profiler verificando che sia selezionato il tipo di evento SQL:BatchStarting.

  2. Eseguire la query dall'applicazione.

  3. Sospendere una traccia SQL Server Profiler.

  4. Fare clic sull'evento SQL:BatchStarting corrispondente alla query.

  5. Fare clic con il pulsante destro del mouse e scegliere Estrai dati eventi.

    Importante

    Non tentare di copiare il testo del batch selezionandolo dal riquadro inferiore della finestra di traccia di SQL Profiler. In caso contrario, la guida di piano creata potrebbe non corrispondere al batch originale.

  6. Salvare i dati degli eventi in un file. Si ottiene in tal modo il testo del batch.

  7. Aprire il file del testo del batch nel Blocco note e copiare il testo.

  8. Creare la guida di piano e incollare il testo copiato tra le virgolette (") specificate per l'argomento @stmt . È necessario usare caratteri di escape per ogni virgoletta singola nell'argomento @stmt facendola precedere da un'altra virgoletta singola. Fare attenzione a non aggiungere o rimuovere altri caratteri quando si inseriscono queste virgolette. Ad esempio, il valore letterale di data '20000101' deve essere delimitato come ''20000101''.

La guida di piano ottenuta è la seguente:

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 delle guide di piano tramite SQL Server Profiler

Per verificare la corrispondenza tra una guida di piano e una query, eseguire la procedura seguente:

  1. Avviare una traccia di SQL Server Profiler verificando che sia selezionato il tipo di evento Showplan XML, disponibile nel nodo Performance.

  2. Eseguire la query dall'applicazione.

  3. Sospendere una traccia SQL Server Profiler.

  4. Trovare l'evento Showplan XML relativo alla query interessata.

    Nota

    L'evento Showplan XML for Query Compile non può essere usato. PlanGuideDB non esiste nell'evento in questione.

  5. Se la guida di piano è di tipo OBJECT o SQL, verificare che nell'evento Showplan XML siano contenuti gli attributi PlanGuideDB e PlanGuideName per la guida di piano che si prevedeva corrispondesse alla query. In alternativa, se la guida di piano è di tipo TEMPLATE, verificare che l'evento Showplan XML contenga gli attributi TemplatePlanGuideDB e TemplatePlanGuideName per la guida di piano prevista. Se tali attributi sono presenti, la guida di piano funziona regolarmente. Questi attributi sono contenuti nell'elemento <StmtSimple>> del piano.

Vedi anche

sp_create_plan_guide (Transact-SQL)