Udostępnij za pośrednictwem

  • Artykuł

Plan Forcing Scenario: Create a Plan Guide to Force a Plan Obtained from a Rewritten Query

Najwygodniejszym sposobem uzyskania ulepszone plan dla kwerendy jest często ręcznie od nowa napisać kwerendę, aby wymusić kolejność łączyć, algorytmy łączyć lub użycie indeksu za pomocą kwerendy wskazówki, nie zmieniając logiczne znaczenie kwerendy.Jednak w przypadku kwerendy wewnątrz aplikacji wdrożonych tej metoda mogą być niedostępne.Korzystanie z prowadnic plan może pomóc w tej sytuacji.Planowanie pracy prowadnic przez dołączenie wskazówki kwerendy lub planów kwerend na kwerendy, gdy nie jest możliwe lub pożądane, aby zmienić tekst kwerendy bezpośrednio.Aby uzyskać więcej informacji zobaczOptimizing Queries in Deployed Applications by Using Plan Guides.

Aby ręcznie ponownie ręcznie napisać kwerendę, przechwycić plan dla niego, a następnie Zastosuj przechwyconych plan wykonania kwerendy do oryginalnej kwerendy za pomocą prowadnicy plan wykonania kwerendy, zawierający przechwycone plan wykonania kwerendy, zgodne z następującym procesem:

  1. Określić, jak zmodyfikować kwerendę (przez zmianę kolejności łączyć, za pomocą FORCE ORDER, łączyć wskazówki lub wskazówek indeksu, a inne pobieraniu), dzięki czemu Dobry plan jest produkowany, ale logiczne znaczenie kwerendy nie została zmieniona.

  2. Przechwytywanie planu kwerendy nowych, przesłane tak samo, jak oryginalna kwerenda (takie jak przy użyciu sp_executesql, sp_cursorprepexec, albo jako autonomiczny partia).

  3. Utworzenia plan wykonania kwerendy do wymuszenia dobrego plan wykonania kwerendy, który uzyskane na oryginalnej kwerendy.

  4. Za pomocą SQL Server Profiler, wybierz plan wykonania kwerendy Powiodło się and plan wykonania kwerendy Powiodło się zdarzeniaWydajność kategorii, a następnie uruchomić kwerendę oryginalnej.Badanie SQL Server Profiler Aby sprawdzić, czy kwerenda używa plan wykonania kwerendy.

Przykład

Załóżmy, że następująca kwerenda wygeneruje planu kwerend, który jest za mała.

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

Można napisać ponownie kwerendę, w następujący sposób, aby logicznie taka sama, ale z zamówieniem różnych łączyć, które będzie zmuszony.

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

Do utworzenia plan wykonania kwerendy do wymuszenia plan dla nowych kwerend na pierwotna kwerenda, przechwytywanie plan wykonania kwerendy w zmiennej i określić zmienną w instrukcja plan wykonania kwerendy wykonania kwerendy, jak pokazano w poniższym kodzie.

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

Przed uruchomieniem kwerendy oryginalnego utworzyć śledzenie przy użyciu SQL Server Profiler i wybierz zdarzenia Zaplanowanie przewodnik powiodło się and Zaplanowanie przewodnika nie powiodła się from the Wydajność kategorii.Uruchamianie kwerendy oryginalnego i sprawdź w wynikach kwerendy, w wyniku śledzenia.

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