sp_create_plan_guide (Transact-SQL)

Belirttiğiniz planı kılavuzları için @ türü = 'SQL' veya @ türü = 'şablon' başarılı bir sorgu değerlerini eşleştirmek için batch_text ve @parameter_name data_type ,...n uygulama tarafından gönderilen karşılıkları tam olarak aynı biçimde sağlanmalıdır.Yani toplu iş metni vermesi gereken tam olarak SQL Server derleyici it. alırGerçek toplu iş ve parametre metni yakalamak için kullanabileceğiniz SQL Server Profiler.Daha fazla bilgi için, bkz. Oluşturmak ve sınamak için SQL Server Profiler'I kullanarak Plan kılavuzları.

Zaman @ türü = 'SQL' ve @ module_or_batch küme NULL, değeri @ module_or_batch değerine küme @ stmt.Bu değeri anlamına gelir statement_text tam olarak aynı biçimde, sağlanan karakter-için-karakteri için göndermiş gibi SQL Server.Bu KAÇINCI kolaylaştırmak için iç dönüştürme gerçekleştirilir.

Zaman SQL Server eşleşen değeri statement_text için batch_text ve @parameter_name data_type ,...n , ya da Eğer @ türü = **'**nesne', metne karşılık gelen bir sorgu içinde object_name, aşağıdaki dize öğeleri yok sayılır:

• dizeiçinde boşluk karakterleri (sekme, boşluk, başları veya satır beslemeleri).

• Comments (-- or /* */).

• Sondaki noktalı

Örneğin, SQL Server eşleşebilir statement_text dize N'SELECT * FROM T WHERE a = 10' aşağıdaki batch_text:

N'SELECT *

FROM T

WHERE a=10'

Ancak, aynı dize için eşleştirilecektir değil batch_text:

N'SELECT * FROM T WHERE b = 10'

SQL Serversatır başı satır besleme ve ilk sorgunun içinde boşluk karakterlerini yoksayar.İkinci sorgu sırası WHERE b = 10 farklı yorumlandığını WHERE a = 10.durum- ve Aksan Duyarlı eşlemesidir (veritabanı harmanlama durumolduğunda yeri bile-duyarlı), durum durum duyarlı olduğu anahtar sözcükler hariç.Eşleşen anahtar sözcüklerin shortened form için duyarlı.Örneğin, anahtar sözcükler EXECUTE, EXEC, ve execute olarak kabul edilir eşdeğer.

Plan kılavuzları sorguları nasıl eşleştirilir hakkında daha fazla bilgi için bkz: Plan kılavuzları kullanarak sorgular dağıtılan uygulamalar içinde en iyi duruma getirme.

Bir modülde plan kılavuzu oluşturma sorgu planı bu modül için planı önbellekten kaldırır.Nesne veya SQL türü plan kılavuzu üzerinde toplu iş oluşturma aynı karma değerine sahip bir toplu iş sorgu planını kaldırır.ŞABLON türü plan kılavuzu oluşturma tüm tek -deyim toplu o veritabanındaki planı önbellekten kaldırır.

Aşağıdaki örnek, bir uygulama tabanlı saklı yordambağlamında yürütülen bir sorguyla eşleşen bir plan kılavuzu oluşturur ve uygular OPTIMIZE FOR İpucu sorgu.

saklı yordamaşağıdaki gibidir:

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h 
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t 
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END
GO

saklı yordamsorgu üzerinde oluşturulan plan kılavuzu aşağıdadır:

EXEC sp_create_plan_guide 
    @name =  N'Guide1',
    @stmt = N'SELECT *
              FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.Customer AS c 
                 ON h.CustomerID = c.CustomerID
              INNER JOIN Sales.SalesTerritory AS t 
                 ON c.TerritoryID = t.TerritoryID
              WHERE t.CountryRegionCode = @Country_region',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

Aşağıdaki örnek, kullanan bir uygulama tarafından gönderilen toplu iş sorgu eşleştirmek için bir plan kılavuzu oluşturur sp_executesql saklı yordamsistem.

toplu işİşte:

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Bu sorgu oluşturulan paralel yürütme planı engellemek için aşağıdaki plan kılavuzuoluşturun:

EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT TOP 1 * 
              FROM Sales.SalesOrderHeader 
              ORDER BY OrderDate DESC', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (MAXDOP 1)';

Aşağıdaki örnek, belirli bir formu parameterizes ve yönlendiren sorguyla eşleşen bir plan kılavuzu oluşturur SQL Server sorgusu parameterizasyonunu zorlamak içinAşağıdaki iki sorgu sözdizimi kurallarına göre eşdeğerdir, ancak yalnızca kendi sabit hazır bilgi değerleri farklıdır.

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

Sorgunun parametreli formdaki plan kılavuzu aşağıdadır:

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

Önceki örnekte, değeri @stmt parametresidir parametreli formu sorgu.Kullanılmak üzere bu değer elde etmek için yalnızca güvenilir şekilde sp_create_plan_guide kullanmak sp_get_query_template saklı yordamsistem.Aşağıdaki komut dosyası, hem parametreleştirilmiş sorguyu edinmek ve plan kılavuzu üzerinde oluşturmak için kullanılabilir.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';

Önemli
sabit değerlerinde değerini @stmt parametresi geçirilen sp_get_query_template veri türü bu yerine parametre için seçilen etkileyecektirdeğişmez.plan kılavuzu eşleşen etkiler.Farklı parametre değer aralıkları işlemek için birden fazla plan kılavuzu oluşturmak zorunda kalabilirsiniz.

ŞABLON tabanlı plan kılavuzukullanmak için bir sorgu parametreli formu edinme hakkında daha fazla bilgi için bkz: Parametre tabanlı sorguları planı kılavuzları tasarlama.

API server imleç yordamlarından gönderilen sorguları planı kılavuzları eşleştirebilirsiniz.Bu yordamlar dahil sp_cursorprepare, sp_cursorprepexec, ve sp_cursoropen.ADO, ole DBve odbc API'ları sık kullandığınız uygulamaları ile etkileşimine SQL Server kullanarak API sunucu imleçler.Daha fazla bilgi için, bkz. API Server İmleçler.API server imleç yordamları çağrılmasını görebilirsiniz SQL Server Profiler izlemeler görüntülemek tarafından RPC:Starting profiler izleme olay.

Aşağıdaki verileri görünür varsayalım bir RPC:Starting profiler izleme olay ile plan kılavuzuayarlamak istediğiniz sorgu için:

DECLARE @p1 int;
SET @p1=-1;
DECLARE @p2 int;
SET @p2=0;
DECLARE @p5 int;
SET @p5=4104;
DECLARE @p6 int;
SET @p6=8193;
DECLARE @p7 int;
SET @p7=0;
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader h INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate >= @P1 AND <= @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

Fark için plan SELECT sorgu çağrısında sp_cursorprepexec birleştirme birleştirmek, ancak karma birleştirmekkullanmak istediğiniz kullanmaktır.Kullanılarak gönderilen sorgu sp_cursorprepexec , bir sorgu dize ve parametre dizedahil olmak üzere parametrelenmiştir.Sorgu kullanarak plan seçimini değiştirmek için aşağıdaki plan kılavuzu oluşturabilirsiniz ve tam olarak göründükleri gibi parametre dizeleri karakter çağrısında karakteri sp_cursorprepexec.

EXEC sp_create_plan_guide 
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h 
              INNER JOIN Sales.SalesOrderDetail AS d 
                ON h.SalesOrderID = d.SalesOrderID 
              WHERE h.OrderDate >= @P1 AND <= @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

Bu sorgu uygulama tarafından sonraki yürütmeler bu plan kılavuzuetkilenecek ve karma birleştirmek sorgu işlemek için kullanılır.

Bir imleçgönderilen sorgu için plan kılavuzu kullanın plan sorgu ipucu kullanma hakkında daha fazla bilgi için bkz: use plan sorgu ipucu imleçler ile sorgular kullanma.

Aşağıdaki örnek, basit bir ad hoc SQLdeyim plan kılavuzu oluşturur. Bu deyim için istenen sorgu planını plan kılavuzu içinde doğrudan sorgu xml Showplan belirterek sağlanan @hints parametresi.Örnek, ilk önce bir planı plan önbelleğinde oluşturmak için SQL deyim yürütür.Bu örnek amacıyla oluşturulan planı istenen plan ve hiçbir ek sorgu ayarlama gerekli olduğunu kabul edilir.Sorgu için xml Showplan sorgulayarak elde edilen sys.dm_exec_query_stats, sys.dm_exec_sql_text, ve sys.dm_exec_text_query_plan dinamik yönetimi görünümleri ve için atanırsa @xml_showplan değişkeni.@xml_showplan Değişken için geçirilen sonra sp_create_plan_guidedeyim içinde @hints parametresi.Veya, plan kılavuzu bir sorgu planı plan önbelleğinde kullanarak oluşturabilirsiniz sp_create_plan_guide_from_handle saklı yordam.

USE AdventureWorks2008R2;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
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'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');

EXEC sp_create_plan_guide 
    @name = N'Guide1_from_XML_showplan', 
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints =@xml_showplan;
GO