Grundlegendes zu Planhinweislisten

Angenommen, die folgende gespeicherte Prozedur, die den @Country\_region-Parameter annimmt, existiert in einer Datenbankanwendung, die in der AdventureWorks-Datenbank bereitgestellt wird:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    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 = @Country_region
END;

Gehen Sie weiterhin davon aus, dass diese gespeicherte Prozedur kompiliert und für @Country\_region = N'AU' (Australien) optimiert wurde. Aus Australien kommen jedoch nur relativ wenige Bestellungen. Wenn die Abfrage mit Parameterwerten für Länder oder Regionen mit mehr Bestellungen ausgeführt wird, beeinträchtigt dies die Leistung. Da die meisten Bestellungen aus den USA kommen, würde ein für @Country\_region = N'US' generierter Abfrageplan wahrscheinlich eine bessere Leistung für alle möglichen Werte des @Country\_region-Parameters erbringen.

Sie könnten dieses Problem lösen, indem Sie die gespeicherte Prozedur so ändern, dass der Abfrage der OPTIMIZE FOR-Abfragehinweis hinzugefügt wird. Da sich die gespeicherte Prozedur jedoch in einer bereitgestellten Anwendung befindet, können Sie den Code der Anwendung nicht direkt ändern. Stattdessen können Sie in der AdventureWorks-Datenbank die folgende Planhinweisliste erstellen.

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT *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 = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))'

Wenn die in der sp_create_plan_guide-Anweisung angegebene Abfrage ausgeführt wird, wird sie vor der Optimierung so geändert, dass sie die OPTIMIZE FOR (@Country = N''US'')-Klausel enthält.

SQL-Planhinweislisten werden für eigenständige Transact-SQL-Anweisungen und -Batches verwendet. Diese Anweisungen werden häufig von einer Anwendung mithilfe der gespeicherten sp_executesql-Systemprozedur übermittelt. Betrachten Sie beispielsweise den folgenden eigenständigen Batch:

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Um zu verhindern, dass ein paralleler Ausführungsplan für diese Abfrage generiert wird, erstellen Sie die folgende Planhinweisliste und legen den MAXDOP-Abfragehinweis im @hints-Parameter auf 1 fest.

sp_create_plan_guide 
@name = N'Guide2', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)';

Hinweis
Der Batch, der die Anweisung enthält, für die Sie eine Planhinweisliste erstellen wollen, darf keine USE database-Anweisung enthalten.

Wichtig
Die für das @module_or_batch-Argument und das @params-Argument der sp_create_plan_guide-Anweisung angegebenen Werte müssen mit dem Text übereinstimmen, der in der Abfrage übermittelt wird. Weitere Informationen finden Sie unter sp_create_plan_guide (Transact-SQL) und Verwenden von SQL Server Profiler zum Erstellen und Testen von Planhinweislisten.

SQL-Planhinweislisten können auch für Abfragen erstellt werden, die in derselben Form parametrisiert werden, wenn die PARAMETERIZATION-Datenbankoption mithilfe von SET auf FORCED festgelegt wird oder wenn eine TEMPLATE-Planhinweisliste erstellt wird, die eine parametrisierte Abfrageklasse angibt. Weitere Informationen finden Sie unter Entwerfen von Planhinweislisten für parametrisierte Abfragen.

TEMPLATE-Planhinweislisten werden verwendet, um das Parametrisierungsverhalten für bestimmte Abfrageformen außer Kraft zu setzen. Sie können eine TEMPLATE-Planhinweisliste in folgenden Situationen erstellen:

• Die Datenbankoption PARAMETERIZATION ist auf FORCED festgelegt, es gibt aber Abfragen, die nach den Regeln der einfachen Parametrisierung kompiliert werden sollen.

• Die Datenbankoption PARAMETERIZATION ist auf SIMPLE festgelegt (die Standardeinstellung), für eine Klasse von Abfragen soll aber eine erzwungene Parametrisierung versucht werden.

Weitere Informationen finden Sie unter Angeben des Abfrageparametrisierungsverhaltens mithilfe von Planhinweislisten.

Im folgenden Beispiel wird eine Planhinweisliste erstellt, die mit jeder Abfrage übereinstimmt, die zu einer bestimmten Form parametrisiert wird. Außerdem wird SQL Server angewiesen, die Parametrisierung der Abfrage zu erzwingen. Die folgenden beiden Abfragen sind syntaktisch gleich, unterscheiden sich jedoch in ihren konstanten Literalwerten.

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

Dies ist die Planhinweisliste für die parametrisierte Form der Abfrage:

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

Im vorhergehenden Beispiel entspricht der Wert des @stmt-Parameters der parametrisierten Form der Abfrage. Die einzige zuverlässige Möglichkeit, diesen Wert für die Verwendung in sp_create_plan_guide abzurufen, ist die gespeicherte sp_get_query_template-Systemprozedur. Mithilfe des folgenden Skripts können Sie die parametrisierte Abfrage abrufen und anschließend eine Planhinweisliste für die Abfrage erstellen.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

Wichtig
Der Wert der konstanten Literale in dem an sp_get_query_template übergebenen @stmt-Parameter kann sich auf den Datentyp auswirken, der für den Parameter, der das Literal ersetzt, gewählt wird. Dies wiederum beeinflusst den Planhinweislistenabgleich. Möglicherweise müssen mehrere Planhinweislisten für verschiedene Parameterwertbereiche erstellt werden.

Sie haben auch die Möglichkeit, TEMPLATE-Planhinweislisten zusammen mit SQL-Planhinweislisten zu verwenden. Beispielsweise können Sie eine TEMPLATE-Planhinweisliste erstellen, um sicherzustellen, dass eine bestimmte Abfrageklasse parametrisiert wird. Anschließend können Sie eine SQL-Planhinweisliste für die parametrisierte Form dieser Abfragen erstellen.