Szenario zum Erzwingen eines Plans: Erstellen einer Planhinweisliste zum Erzwingen eines Plans, der aus einer umgeschriebenen Abfrage abgerufen wird
Häufig ist das problemloseste Verfahren zum Abrufen eines verbesserten Plans für eine Abfrage das manuelle Umschreiben der Abfrage, um die Joinreihenfolge, die Joinalgorithmen 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 kann die Verwendung von Planhinweislisten helfen. Durch Planhinweislisten werden Abfragehinweise oder Abfragepläne an Abfragen angefügt, 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 Joinreihenfolge ändern, FORCE ORDER, Joinhinweise oder Indexhinweise verwenden sowie andere Techniken anwenden), damit ein geeigneter Plan für die Abfrage erstellt, die logische Bedeutung der Abfrage jedoch nicht geändert wird.
Erfassen Sie den Plan für die umgeschriebene Abfrage, der genauso 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.
Wählen Sie mit SQL Server Profiler das Plan Guide Successful-Ereignis und das Plan Guide Unsuccessful-Ereignis in der Kategorie Leistung aus, und führen Sie dann die ursprüngliche Abfrage aus. Überprüfen Sie in SQL Server Profiler, ob die Planhinweisliste von der Abfrage verwendet wird.
Beispiel
Angenommen, die folgende Abfrage generiert einen Abfrageplan, der zu langsam ist.
USE AdventureWorks2008R2;
GO
EXEC sp_executesql
@stmt = N'SELECT soh.SalesPersonID
,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
,e.JobTitle
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson AS sp
INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee AS e ON soh.SalesPersonID = e.BusinessEntityID
INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
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 Joinreihenfolge erzwungen.
USE AdventureWorks2008R2;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
,e.JobTitle
,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.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.BusinessEntityID
INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
WHERE st.[Group] = @p1
OPTION (FORCE ORDER)',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
Wenn Sie eine Planhinweisliste erstellen möchten, um den Plan für die umgeschriebene Abfrage für die ursprüngliche Abfrage zu erzwingen, erfassen Sie den Plan in einer Variablen und geben die Variable in der Anweisung der Planhinweisliste an (siehe dazu den folgenden Code).
DBCC FREEPROCCACHE;
GO
USE AdventureWorks2008R2;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
,e.JobTitle
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee AS e ON soh.SalesPersonID = e.BusinessEntityID
INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
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
,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
,e.JobTitle
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson AS sp
INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID
INNER JOIN Person.Person ASp ON e.BusinessEntityID = p.BusinessEntityID
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 das Plan Guide Successful-Ereignis und das Plan Guide Unsuccessful-Ereignis in der Kategorie Leistung aus. Führen Sie die ursprüngliche Abfrage aus, und überprüfen Sie die Ergebnisse der Abfrage in die Ausgabe der Ablaufverfolgung.
USE AdventureWorks2008R2;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
,e.JobTitle
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson AS sp
INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID
INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
WHERE st.[Group] = @p1',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
Siehe auch