Szenario zum Erzwingen eines Planes: Erstellen einer Planhinweisliste zum Erzwingen eines Planes, der aus einer umgeschriebenen Abfrage abgerufen wird
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. In dieser Situation können Planhinweislisten helfen. Planhinweislisten fügen Abfragehinweise oder Abfragepläne 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, die den erfassten Plan enthält. Verwenden Sie dabei das folgende Verfahren:
Bestimmen Sie, wie die Abfrage geändert werden soll (indem Sie die Verknüpfungsreihenfolge ändern oder FORCE ORDER, Verknüpfungshinweise, Indexhinweise oder 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).
Erstellen Sie eine Planhinweisliste, um den geeigneten Plan, den Sie abgerufen haben, für die ursprüngliche Abfrage zu erzwingen.
Verwenden Sie den SQL Server Profiler, wählen Sie die Ereignisse Plan Guide Successful und Plan Guide Unsuccessful unter dem Knoten Leistung aus, und führen Sie dann die ursprüngliche Abfrage aus. Überprüfen Sie SQL Server Profiler, um sich zu vergewissern, dass die Abfrage die Planhinweisliste verwendet.
Beispiel:
Angenommen, die folgende Abfrage generiert einen Abfrageplan, der zu langsam ist.
USE AdventureWorks;
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
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
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
OPTION (FORCE ORDER)',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
Um den Plan für die umgeschriebene Abfrage der ursprünglichen Abfrage zu erzwingen, erstellen Sie eine Planhinweisliste, indem Sie den Plan in einer Variablen speichern und diese Variable in der Anweisung der Planhinweisliste angeben. Der folgende Code zeigt dieses Verfahren:
DBCC FREEPROCCACHE;
GO
USE AdventureWorks;
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
OPTION (FORCE ORDER)',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
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'%Sales.SalesPerson%' AND st.text LIKE N'%OPTION (FORCE ORDER)%');
EXEC sp_create_plan_guide
@name = N'ForceOrderGuide1',
@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 = @xml_showplan;
GO
SELECT * FROM sys.plan_guides;
GO
Bevor Sie die ursprüngliche Abfrage ausführen, erstellen Sie mit SQL Server Profiler eine Ablaufverfolgung und wählen die Ereignisse Plan Guide Successful und Plan Guide Unsuccessful unter dem Knoten Leistung aus. Führen Sie die ursprüngliche Abfrage aus und überprüfen Sie die Ergebnisse der Abfrage anhand der Ausgabe der Ablaufverfolgung.
USE AdventureWorks;
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
Siehe auch