• Artikel

Szenario zum Erzwingen eines Plans: Erstellen einer Planhinweisliste, die einen Abfrageplan angibt

Beim Erstellen einer Planhinweisliste können Sie einen Abfrageplan erzwingen, indem Sie die gespeicherte Systemprozedur sp_create_plan_guide verwenden und im @hints-Parameter einen Abfrageplan im XML-Showplanformat für die Abfrage angeben. Planhinweislisten werden verwendet, um Abfragehinweise oder Abfragepläne auf Abfragen in bereitgestellten Anwendungen anzuwenden, wenn Sie die Anwendung nicht direkt ändern können oder möchten. Weitere Informationen zu Planhinweislisten finden Sie unter Optimieren von Abfragen in bereitgestellten Anwendungen mit Planhinweislisten. In diesem Szenario fügen Sie der Planhinweisliste einen bestimmten Abfrageplan an.

Angenommen, Ihre Anwendung enthält die folgende gespeicherte Prozedur:

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion;
END;
GO

Angenommen, die meisten Abfragen, die diese Prozedur ausführen, weisen eine unzureichende Leistung auf, weil der Abfrageplan weder für einen repräsentativen, noch für einen Worst-Case-Wert des Parameters @CountryRegion optimiert ist. Sie möchten erzwingen, dass diese gespeicherte Prozedur einen bestimmten Abfrageplan verwendet, der für ein spezielles Land oder eine spezielle Region optimiert wurde. Sie können die gespeicherte Prozedur der Anwendung jedoch nicht direkt ändern, weil Sie die Anwendung bei einem unabhängigen Softwareanbieter erworben haben. Stattdessen können Sie jedoch eine Planhinweisliste für die Abfrage erstellen und dabei in der Planhinweisliste einen Abfrageplan angeben, der für den repräsentativen Wert optimiert wurde.

Um einer Planhinweisliste einen Abfrageplan anzufügen, müssen Sie zunächst einen optimierten Abfrageplan für die Abfrage in der gespeicherten Prozedur abrufen. Dazu führen Sie die in der gespeicherten Prozedur definierte Abfrage aus, wobei Sie den @CountryRegion-Parameter durch einen konstanten repräsentativen oder Worst-Case-Wert ersetzen. Dann fragen Sie die dynamische Verwaltungssicht sys.dm_exec_query_stats ab, um den Abfrageplan aus dem Plancache abzurufen. Es wird empfohlen, den XML-Showplan einer Variable zuzuweisen. Andernfalls müssen Sie alle einfachen Anführungszeichen im XML-Showplan abgrenzen, indem Sie ihnen ein weiteres einfaches Anführungszeichen voranstellen. Schließlich erstellen Sie eine Planhinweisliste, die den XML-Showplan im @hints-Parameter angibt.

Beispiel

Im folgenden Codebeispiel werden die Schritte gezeigt, die notwendig sind, um einen optimierten Abfrageplan für die gespeicherte Prozedur Sales.GetSalesOrderByCountryRegion abzurufen und ihn einer Planhinweisliste anzufügen. Wenn die gespeicherte Prozedur ausgeführt wird, wird die in der Prozedur definierte Abfrage mit der Planhinweisliste verglichen, und der Abfrageoptimierer verwendet den in der Planhinweisliste angegebenen Abfrageplan.

CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion;
END;
GO
-- Execute the query based on a representative or "worst-case" scenario.
GO
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = N'US';
GO
-- Retrieve the query plan for the previous query. Assign the query plan to a variable and attach the query plan to a plan guide.
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'SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = N''US'';%');

EXEC sp_create_plan_guide 
    @name = N'Guide_for_GetSalesByCountryRegion',
    @stmt = N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountryRegion',
    @params = NULL,
    @hints = @xml_showplan;
GO
SELECT * FROM sys.plan_guides
WHERE scope_object_id = OBJECT_ID(N'Sales.GetSalesOrderByCountryRegion');