Aracılığıyla paylaş

  • Makale

Plan zorlama senaryosu: Bir sorgu planı belirtir bir Plan kılavuzu oluşturma

Bir plan kılavuzu kullanarak oluştururken, bir sorgu planı zorla sp_create_plan_guide sistem saklı yordamı ve sorgu Showplan xml biçiminde bir sorgu planı belirterek @hints parametresi.Plan kılavuzları edemez veya uygulamayı doğrudan değiştirmek istiyor musunuz Sorgu ipuçları veya sorgu planlarını sorgular dağıtılan uygulamalar içinde uygulamak için kullanılır.Plan kılavuzları hakkında daha fazla bilgi için bkz: Plan kılavuzları kullanarak sorgular dağıtılan uygulamalar içinde en iyi duruma getirme.Bu senaryoda, belirli sorgu planı plan Kılavuzu iliştiriyorsanız.

Aşağıdaki uygulama içerdiğini varsayalım saklı yordam:

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    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 = @CountryRegion;
END;
GO

Sorgu planı için bir temsilcisi ya da getirilmemiştir çünkü bu yordamı yürütmek sorguların çoğu kötü gerçekleştirdiğiniz varsayılmaktadır ya da "kötü -durum" için değer @CountryRegion parametresi.Belirli bir ülke veya bölge için optimize bir özel bir sorgu planı kullanmak için bu saklı yordam zorlamak istiyor.Ancak, bağımsız yazılım satıcısı uygulamadan satın çünkü saklı yordam uygulamasında doğrudan değiştiremezsiniz.Bunun yerine, bir sorgu planı temsilcisi değeri optimize edilmiş plan Kılavuzu belirterek sorgu planı kılavuzu oluşturabilirsiniz.

Bir sorgu planı için bir plan kılavuz eklemek için önce bir sorgu en iyi duruma getirilmiş sorgu planını almanız saklı yordam.Saklı yordam içinde tanımlanan sorgu yürütülerek bir temsilcisi yerine bunu ya da "kötü -durum," sabit değer yerine @CountryRegion parametresi.Sorgulamak, daha sonra sys.dm_exec_query_stats Dinamik yönetim görünümü elde etme sorgusu planından plan önbelleği.xml Showplan bir değişkene atamanız önerilir; Aksi halde, tek bir tırnak işareti koyarak herhangi tek tırnak içine xml Showplan çıkış gerekir.xml Showplan de belirterek bir plan Kılavuzu oluşturun son olarak, @hints parametresi.

Örnek

Aşağıdaki kod örneği için bir sorgu en iyi duruma getirilmiş planı elde etmek için gerekli adımları göstermektedir Sales.GetSalesOrderByCountryRegion saklı yordam ve eklemek için bir plan Kılavuzu.Saklı yordam çalıştırıldığında, yordamda tanımlanan sorgu planı Kılavuzu eşleştirilir ve sorgu iyileştiricisi plan Kılavuzu'nda belirtilen sorgu planını kullanır.

CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    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 = @CountryRegion;
END;
GO
-- Execute the query based on a representative or "worst-case" scenario.
GO
SELECT h.SalesOrderID, h.OrderDate, h.Comment
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 = N'US';
GO
-- Retrieve the query plan for the previous query. Assign the query plan to a variable and attach the query plan to a plan guide.
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'SELECT h.SalesOrderID, h.OrderDate, h.Comment
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 = N''US'';%');

EXEC sp_create_plan_guide 
    @name = N'Guide_for_GetSalesByCountryRegion',
    @stmt = N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
    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 = @CountryRegion',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountryRegion',
    @params = NULL,
    @hints = @xml_showplan;
GO
SELECT * FROM sys.plan_guides
WHERE scope_object_id = OBJECT_ID(N'Sales.GetSalesOrderByCountryRegion');