Szenario zum Erzwingen eines Planes: Erstellen einer Planhinweisliste zum Erzwingen eines Planes, der aus einer umgeschriebenen Abfrage abgerufen wird

Artikel
12/15/2008

Häufig ist das problemloseste Verfahren zum Abrufen eines verbesserten Planes für eine Abfrage das manuelle Umschreiben der Abfrage, um die Verknüpfungsreihenfolge, die Verknüpfungsalgorithmen oder die Indexverwendung mithilfe von Abfragehinweisen zu erzwingen, ohne die logische Bedeutung der Abfrage zu ändern. Wenn die Abfrage in einer bereitgestellten Anwendung ausgeführt wird, ist diese Methode jedoch möglicherweise nicht verfügbar. Das Verwenden von Planhinweislisten zusammen mit dem USE PLAN-Abfragehinweis kann unter diesen Umständen hilfreich sein. Planhinweislisten fügen Abfragehinweise an Abfragen an, wenn es nicht möglich oder wünschenswert ist, den Text einer Abfrage direkt zu ändern. Weitere Informationen finden Sie unter Optimieren von Abfragen in bereitgestellten Anwendungen mit Planhinweislisten.

Wenn Sie die Abfrage von Hand umschreiben möchten, erfassen Sie den Plan für die Abfrage, und wenden Sie dann den erfassten Plan auf die ursprüngliche Abfrage mit einer Planhinweisliste an, der einen USE PLAN-Hinweis enthält. Verwenden Sie dabei das folgende Verfahren:

Bestimmen Sie, wie die Abfrage geändert werden soll, indem Sie die Verknüpfungsreihenfolge ändern, FORCE ORDER, Verknüpfungshinweise, Indexhinweise und andere Techniken verwenden, damit ein geeigneter Plan für die Abfrage erstellt wird, die logische Bedeutung der Abfrage jedoch nicht geändert wird.
Erfassen Sie den Plan für die umgeschrieben Abfrage, der ebenso wie die ursprüngliche Abfrage übermittelt wird (z. B. mithilfe von sp_executesql, sp_cursorprepexec oder als eigenständiger Batch).
Ändern Sie eine Kopie der ursprünglichen Abfrage, indem Sie einen OPTION (USE PLAN)-Abfragehinweis anfügen, der den erfassten Plan enthält, und testen Sie dann, ob Sie den erfassten Plan für die Abfrage erzwingen können.
Wenn der Test fehlschlägt, probieren Sie andere umgeschriebene Abfragen aus, oder debuggen Sie auf andere Weise, bis Sie einen geeigneten Plan erhalten, der für die ursprüngliche Abfrage erzwungen werden kann.
Erstellen Sie eine Planhinweisliste, um den geeigneten Plan, den Sie abgerufen haben, für die ursprüngliche Abfrage zu erzwingen.

Beispiel:

Angenommen, die folgende Abfrage generiert einen Abfrageplan, der zu langsam ist:

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

Sie können die Abfrage wie folgt umschreiben. Dabei bleibt die logische Bedeutung der Abfrage gleich, es wird jedoch eine andere Verknüpfungsreihenfolge erzwungen.

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

Nachdem Sie den STATISTICS XML-Abfrageplan für die umgeschriebene Abfrage erfasst und für die ursprüngliche Abfrage getestet haben, erstellen Sie eine Planhinweisliste, um den Plan für die ursprüngliche Abfrage zu erzwingen. Der folgende Code zeigt dieses Verfahren:

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 …'')'

Stellen Sie sicher, dass alle einfachen Anführungszeichen (') im XML-Abfrageplan mithilfe von vier einfachen Anführungszeichen ('''') geschützt werden, bevor Sie den Abfrageplan in der @hints-Zeichenfolge ersetzen. Dies liegt daran, dass der Abfrageplan in zwei Zeichenfolgenliteralen geschachtelt ist.

Siehe auch

Freigeben über

Szenario zum Erzwingen eines Planes: Erstellen einer Planhinweisliste zum Erzwingen eines Planes, der aus einer umgeschriebenen Abfrage abgerufen wird

Beispiel:

Siehe auch

Aufgaben

Konzepte

Andere Ressourcen

Hilfe und Informationen

Zusätzliche Ressourcen