Scenario di utilizzo forzato del piano: Creazione di una guida di piano per forzare l'utilizzo di un piano ottenuto da una query riformulata

Articolo
12/15/2008

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 tal caso, è consigliabile utilizzare guide di piano insieme all'hint per la query USE PLAN. Le guide di piano associano gli hint alle 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 un hint USE PLAN, seguire la procedura seguente:

Determinare le modifiche da apportare alla query cambiando l'ordine di join, utilizzando FORCE ORDER e hint di join, hint per l'indice e 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).
Modificare una copia della query originale associando la relativa clausola di hint OPTION (USE PLAN) contenente il piano acquisito ed eseguire un test per verificare se è possibile forzare l'utilizzo di tale piano sulla query.
Se il test non viene superato, provare a riformulare la query o altrimenti eseguire il debug fino ad ottenere un piano appropriato di cui sia possibile forzare l'utilizzo sulla query originale.
Creare una guida di piano per forzare l'utilizzo del piano appropriato ottenuto sulla query originale.

Esempio

Si supponga che la query seguente generi un piano di query troppo lento:

USE AdventureWorks;
GO
SET STATISTICS XML ON;
GO
EXEC sp_executesql 
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,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.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [Sales].[SalesTerritory] st 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
SET STATISTICS XML OFF;
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 AdventureWorks;
GO
SET STATISTICS XML ON;
GO
EXEC sp_executesql 
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,st.[Name] AS [SalesTerritory]
    ,soh.[SubTotal]
    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
FROM [Sales].[SalesPerson] sp 
    INNER JOIN [Sales].[SalesTerritory] st -- Moved this join earlier 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1
OPTION (FORCE ORDER)',  -- force join order to be as specified in FROM list
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
SET STATISTICS XML OFF
GO

Dopo aver acquisito il piano STATISTICS XML della query riformulata e averlo testato sulla query originale, creare una guida di piano per forzare l'utilizzo del piano sulla query originale, come illustrato nel codice seguente:

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide',
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,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.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [Sales].[SalesTerritory] st 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1', 
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = N'OPTION (USE PLAN 
N''… put XML showplan for modified query here …'')'

Verificare che per ogni virgoletta singola (') all'interno del piano di query XML vengano utilizzate quattro virgolette singole ('''') come caratteri di escape prima di sostituire il piano di query nella stringa @hints. Questa operazione è necessaria in quanto il piano di query è nidificato tra due valori letterali stringa.

Vedere anche

Condividi tramite

Scenario di utilizzo forzato del piano: Creazione di una guida di piano per forzare l'utilizzo di un piano ottenuto da una query riformulata

Esempio

Vedere anche

Attività

Concetti

Altre risorse

Guida in linea e informazioni

Risorse aggiuntive