Scenario di utilizzo forzato del piano: Creazione di una guida di piano per forzare l'utilizzo di un piano ottenuto da una query riformulata
In genere, il modo più conveniente per ottenere un piano di query migliorato consiste nel riformulare manualmente la query per forzare l'ordine di join, gli algoritmi JOIN o l'utilizzo degli indici tramite gli hint per la query, senza modificare il significato logico della query. Se tuttavia la query viene eseguita all'interno di un'applicazione distribuita, tale metodo potrebbe non essere disponibile. In questa situazione è consigliabile utilizzare guide di piano. Le guide di piano associano alle query gli hint di query o i piani di query quando non è possibile o consigliabile modificare direttamente il testo di una query. Per ulteriori informazioni, vedere Ottimizzazione delle query nelle applicazioni distribuite tramite le guide di piano.
Per riformulare manualmente una query, acquisire il relativo piano e quindi applicarlo alla query originale con una guida di piano contenente il piano acquisito, seguire la procedura seguente:
Determinare le modifiche da apportare alla query (cambiando l'ordine di join, utilizzando FORCE ORDER e hint di join o hint per l'indice e utilizzando altre tecniche) in modo da generare un piano ottimale lasciando tuttavia invariato il significato logico della query.
Acquisire il piano della query riformulata, inviata esattamente come la query originale (tramite sp_executesql, sp_cursorprepexec o come batch autonomo).
Creare una guida di piano per forzare l'utilizzo del piano appropriato ottenuto sulla query originale.
In SQL Server Profiler, selezionare gli eventi Plan Guide Successful e Plan Guide Unsuccessful dalla categoria Performance e quindi eseguire la query originale. Esaminare SQL Server Profiler per verificare che la query stia utilizzando la guida di piano.
Esempio
Si supponga che la query seguente generi un piano di query troppo lento.
USE AdventureWorks2008R2;
GO
EXEC sp_executesql
@stmt = N'SELECT soh.SalesPersonID
,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
,e.JobTitle
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson AS sp
INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee AS e ON soh.SalesPersonID = e.BusinessEntityID
INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
WHERE st.[Group] = @p1',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
È possibile riformulare la query come illustrato di seguito, in modo che il significato logico rimanga lo stesso ma venga forzato l'utilizzo di un diverso ordine di join.
USE AdventureWorks2008R2;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
,e.JobTitle
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader soh ON sp.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.BusinessEntityID
INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
WHERE st.[Group] = @p1
OPTION (FORCE ORDER)',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
Per creare una guida di piano e forzare l'utilizzo del piano sulla query originale, acquisire il piano in una variabile e specificare la variabile nell'istruzione della guida di piano, come illustrato nel codice seguente.
DBCC FREEPROCCACHE;
GO
USE AdventureWorks2008R2;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
,e.JobTitle
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee AS e ON soh.SalesPersonID = e.BusinessEntityID
INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
WHERE st.[Group] = @p1
OPTION (FORCE ORDER)',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
WHERE st.text LIKE N'%Sales.SalesPerson%' AND st.text LIKE N'%OPTION (FORCE ORDER)%');
EXEC sp_create_plan_guide
@name = N'ForceOrderGuide1',
@stmt = N'SELECT
soh.SalesPersonID
,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
,e.JobTitle
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson AS sp
INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID
INNER JOIN Person.Person ASp ON e.BusinessEntityID = p.BusinessEntityID
WHERE st.[Group] = @p1',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = @xml_showplan;
GO
SELECT * FROM sys.plan_guides;
GO
Prima di eseguire la query originale, creare una traccia con SQL Server Profiler e selezionare gli eventi Plan Guide Successful e Plan Guide Unsuccessful dalla categoria Performance. Eseguire la query originale e controllarne i risultati nell'output della traccia.
USE AdventureWorks2008R2;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
,e.JobTitle
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson AS sp
INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID
INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
WHERE st.[Group] = @p1',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
Vedere anche