Freigeben über


Entwerfen von Planhinweislisten für parametrisierte Abfragen

Sie können einen Planhinweis für eine Abfrage erstellen, die parametrisiert ist. Eine Abfrage kann aus einem der folgenden Gründe parametrisiert sein:

  • Die Abfrage wird mithilfe von sp_executesql übermittelt.

  • Erzwungene Parametrisierung ist in der Datenbank aktiviert. Dies bedeutet, dass alle geeigneten Abfragen parametrisiert werden.

  • Ein separater Planhinweis wurde für eine Klasse von Abfragen erstellt, zu denen diese Abfrage gehört; dabei wurde angegeben, dass sie parametrisiert sind.

Wenn Sie einen Planhinweis für eine parametrisierte Abfrage erstellen, erstellen Sie im Wesentlichen einen Planhinweis für alle Abfragen, die in die gleiche Form parametrisieren und sich nur hinsichtlich ihrer konstanten Literalwerte unterscheiden. In einer Datenbank, für die erzwungene Parametrisierung aktiviert ist, parametrisieren die folgenden beiden Abfragen z. B. in die gleiche Form:

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 50;

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 100;

Um einen Planhinweis für parametrisierte Abfragen zu erstellen, erstellen Sie einen Planhinweis vom Typ SQL und geben dann die parametrisierte Form der Abfrage in der gespeicherten Prozedur sp_create_plan_guide an.

Wenn Sie z. B. die parametrisierte Form einer der Abfragen aus dem vorherigen Beispiel abrufen und einen Planhinweis für diese erstellen möchten, um den Optimierer zur Verwendung einer Hashverknüpfung zu zwingen, führen Sie die folgenden Schritte aus:

  1. Rufen Sie die parametrisierte Form der Abfrage ab, indem Sie sp_get_query_template ausführen.

  2. Wenn die Abfrage noch nicht von SQL Server mithilfe von sp_executesql oder der SET-Option PARAMETERIZATION FORCED der Datenbank parametrisiert wurde, erstellen Sie einen Planhinweis vom Typ TEMPLATE, um die Parametrisierung zu erzwingen.

  3. Erstellen Sie einen Planhinweis vom Typ SQL für die parametrisierte Abfrage.

Der folgende Batch führt diese drei Schritte aus:

--Obtain the paramaterized form of the query:
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
    FROM Production.ProductModel pm 
    INNER JOIN Production.ProductInventory pi 
        ON pm.ProductModelID = pi.ProductID 
    WHERE pi.ProductID = 101 
    GROUP BY pi.ProductID, pi.Quantity 
    HAVING SUM(pi.Quantity) > 50',
@stmt OUTPUT, 
@params OUTPUT;
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)';
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1', 
@stmt, 
N'SQL', 
NULL, 
@params, 
N'OPTION(HASH JOIN)';

Der Planhinweis gilt nun für alle Abfragen, die in die angegebene Form parametrisiert werden, jedoch unterschiedliche konstante Literalwerte enthalten.