sp_create_plan_guide (Transact-SQL)

Bir plan kılavuzu, bir veritabanındaki sorgularla sorgu ipuçları veya gerçek sorgu planları için ilişkilendirme oluşturur.Plan kılavuzları hakkında daha fazla bilgi için bkz: Plan Kılavuzları'nı anlama.

Transact-SQL sözdizimi kuralları

sp_create_plan_guide [ @name = ] N'plan_guide_name'
    , [ @stmt = ] N'statement_text'
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
    , [ @module_or_batch = ]
      { 
                    N'[ schema_name. ] object_name'
        | N'batch_text'
        | NULL
      }
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL } 
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )' 
                 | N'XML_showplan'
                 | NULL }

Bağımsız değişkenler

• [adı =] N ' plan_guide_name'
  Plan kılavuzu adıdır.Plan guide names are scoped to the current database.plan_guide_name must comply with the rules for identifiers and cannot start with the number sign (#).En fazla uzunluğu plan_guide_name 124 karakter olabilir.

• [stmt =] N ' statement_text'
  Iş bir Transact-SQL deyim oluşturulacağı karşı bir plan kılavuzu. Zaman SQL Server sorgu iyileştiricisi uyan bir sorgu tanır. statement_text, plan_guide_name etkili olur. Oluşturulması için bir plan kılavuzu başarılı olması için , statement_text @ türü, @ module_or_batch ve @ params parametreleri ile belirtilen içeriği görünmelidir.

  statement_text Toplu iş veya @ module_or_batch ve @ params tarafından tanımlanan bir modül içinde sağlanan ilgili ifadesiyle eşleşen sorgu iyileştiricisi için izin verir şekilde sağlanması gerekir.Daha fazla bilgi için "Uyarılar" bölümüne bakın.Boyutu statement_text Yalnızca kullanılabilir bellek sunucu tarafından sınırlandırılır.

• [@type = ]N'{ OBJECT | SQL | TEMPLATE }'
  Varlık türü statement_text görünür. Bu eşleştirme için içerik belirtir. statement_text Kime plan_guide_name.

  • NESNE
    Gösterir statement_text bağlamında görünür bir Transact-SQL saklı yordam, skaler fonksiyon, çoklu deyimli tablo değerli işlev veya Transact-SQL Geçerli veritabanında DML tetikleyicisi.

  • SQL
    Indicates statement_text appears in the context of a stand-alone statement or batch that can be submitted to SQL Server through any mechanism.Transact-SQL statements submitted by common language runtime (CLR) objects or extended stored procedures, or by using EXEC N'sql_string', are processed as batches on the server and, therefore, should be identified as @type = 'SQL'.Sorgunun SQL belirtilirse, ipucu PARAMETERIZATION {FORCED | SIMPLE} @ ipuçları parametresinde belirtilemez.

  • Şablon
    Belirtilen form parameterizes herhangi bir sorgu planı kılavuz uygulandığı gösterir. statement_text. TEMPLATE belirtilirse, yalnızca PARAMETERIZATION {FORCED | SIMPLE} sorgu ipucu ipuçları @ parametresinde belirtilebilir.TEMPLATE planı kılavuzları hakkında daha fazla bilgi için bkz: Plan Kılavuzları'nı kullanarak sorgu Parameterization davranışı belirleme.

• [@module_or_batch =]{ N'[ schema_name. ] object_name' | n 'batch_text' | null}
  Her iki ad nesnesinin içinde belirtir. statement_text görüntülenen veya, toplu iş iş iş metni statement_text görünür. Toplu iş metin bir USE içeremezdatabase deyim.

  Bir uygulamadan gönderilen bir toplu iş eşleştirmek bir plan kılavuzu batch_texaynı biçimde, t sağlanan karakter-için-karakter için göndermiş gibi SQL Server. Iç dönüştürme, bu eşleşme sağlamak için yapılır.Daha fazla bilgi için Açıklamalar bölümüne bakın.

  [schema_name.]object_name specifies the name of a Transact-SQL stored procedure, scalar function, multistatement table-valued function, or Transact-SQL DML trigger that contains statement_text.If schema_name belirtilmemişse, schema_name Geçerli kullanıcının şemasını kullanır. NULL belirtilirse ve türü ' @ 'SQL' = @ module_or_batch değerinin @ stmt değerine küme.=, Türü ' TEMPLATE**'**module_or_batch @ NULL olmalıdır.

• [ @params = ]{ N'@parameter_name data_type [ ,...n ]' | NULL }
  Katıştırılmış tüm parametrelerinin tanımları belirtir. statement_text. yalnızca, aşağıdakilerden biri doğruysa, @ params geçerlidir:

  • = 'SQL' ya da 'TEMPLATE' türü.'TEMPLATE' params ' NULL olmamalı.

  • statement_text @ params parametresi belirtildi için sp_executesql ve bir değer kullanılarak gönderilen veya SQL Server dahili bir deyim, sonra da parameterizing gönderir. Parametreli sorgular teslimini veritabanından için Apı (ODBC, OLE DB ve ADO.NET dahil) görünür. SQL Server API sunucu imleci yordamlar veya sp_executesql olarak çağırır; bu nedenle, bunlar da SQL veya TEMPLATE eşlenmesi kılavuzlar planlayın. Parameterization ve planı kılavuzları hakkında daha fazla bilgi için bkz: SQL Server sonuçları, sorgu kılavuzlara nasıl planlanır.

  @parameter\_name data_type gönderilmiş gibi tam aynı biçimde sağlanmalı. SQL Server sp_executesql kullanarak veya dahili parameterization sonra gönderilen. Daha fazla bilgi için Açıklamalar bölümüne bakın.Toplu iş parametreleri içermiyorsa, NULL belirtilmiş olmalı.@ Params boyutunu, yalnızca kullanılabilir sunucu belleği tarafından sınırlandırılır.

• [@hints = ]{ N'OPTION ( query_hint [ ,...n ] )' | N'XML_showplan' | NULL }

  • (N'OPTIONquery_hint ,...n ] )
    @ Stmt uyan bir sorgu eklemek için bir SEÇENEK yan tümce belirtir.@ ipuçları, sözdizimsel olarak bir deyim içinde bir SEÇENEK yan tümce ile aynı olması gerekir ve sorgu ipuçları geçerli bir dizi içerebilir.

  • N ' XML_showplan'
    Bir ipucu uygulanacak Sorgu planını XML biçiminde belirtilir.

    XML gösterim planı bir değişkene atama öneririz; aksi durumda, gösterim planı herhangi tek tırnak işaretleri tek bir tırnak işareti önceki çıkış gerekir.Örnek e bakın.

  • null
    Varolan herhangi bir ipucu SEÇENEĞINI belirttiğiniz gösterir yan tümce sorguyu Sorgu uygulanır.Daha fazla bilgi için bkz: OPTION yan tümce (Transact-SQL).

Remarks

Gösterilen sırayla sp_create_plan_guide bağımsız sağlanması gerekir.Parametrelerinin değerlerini sağladığınız sp_create_plan_guide, tüm parametre adlarını açıkça belirtilmesi gerekir ya da tüm konumunda yok. Örneğin, @name = , ardından belirtilen @stmt = , @type =ve böyle devam eder, bu da belirtilmelidir. Benzer şekilde, @name = atlanmış ve yalnızca parametre değeri olarak sağlanan de kalan parametre adlarına göz ardı ve değerlerini yalnızca sağlanan. Argument names are for descriptive purposes only, to help understand the syntax.SQL Server does not verify that the specified parameter name matches the name for the parameter in the position where the name is used.

Birden fazla NESNE veya SQL plan kılavuzu aynı sorguyu ve toplu iş veya modül oluşturabilirsiniz.Ancak, herhangi bir anda yalnızca bir plan kılavuzu etkinleştirilebilir.

Kılavuzları'türündeki NESNE bir saklı yordam, işlev veya WITH şifreleme yan tümcesini belirtir veya geçici DML tetikleyicisi başvuran bir @ module_or_batch değeri oluşturulamıyor planlayın.

Bırakın veya bir işlev, saklı yordam veya bir plan Kılavuzu tarafından başvurulmaktadır DML tetikleyicisi değiştirme denemesi, etkin veya devre dışı bir hatasına neden oluyor.Bir plan Kılavuzu tarafından başvuruda bulunulan bir tetikleyici tanımlanmış olan bir tabloda bırak çalışılırken bir hata neden.

Not

Plan kılavuzları yalnızca kullanılabilir SQL Server Standart, Enterprise Developer ve değerlendirme sürümleri; ancak, herhangi bir sürümünde planı kılavuzları görülebilir. Ayrıca, herhangi bir sürümü için plan kılavuzları içeren bir veritabanı iliştirebilirsiniz.geri yükleme yüklemek veya bir veritabanı yükseltilmiş bir sürüm için iliştirin planı kılavuzları sağlam kalır SQL Server 2008. Sunucu bir yükseltme gerçekleştirdikten sonra her veritabanında plan kılavuzu desirability doğrulamalısınız.

plan kılavuzu Eşleşen gereksinimleri

For plan guides that specify @type = 'SQL' or @type = 'TEMPLATE' to successfully match a query, the values for batch_text and @parameter\_name data_type [,... n ] must be provided in exactly the same format as their counterparts submitted by the application.This means you must provide the batch text exactly as the SQL Server compiler receives it.Gerçek bir toplu iş ve parametre metni yakalamak için SQL Server Profiler. Daha fazla bilgi için bkz: Oluşturma ve sınama için SQL Server Profiler'ı kullanarak kılavuzları planlanır..

Ne tür ' @ 'SQL' = ve @ module_or_batch NULL olarak küme, bu değeri, @ module_or_batch @ stmt değerine küme.Bunun için değer anlamı statement_text tam olarak aynı biçimde, sağlanan karakter-için-karakter için göndermiş gibi SQL Server. Iç dönüştürme, bu eşleşme sağlamak için yapılır.

Ne zaman SQL Server değeri ile eşleşiyor statement_text Kime batch_text ve @parameter\_name data_type ,... n ], or if @type = 'OBJECT', to the text of the corresponding query inside object_name, the following string elements are not considered:

• Dize içinde boşluk karakterleri (sekme, boşluk, satırbaşları veya satır beslemeleri).

• Açıklamalar)-- or /* */).

• Izleyen noktalı

Örneğin, SQL Server eşleştirebilirsiniz statement_text Dize N'SELECT * FROM T WHERE a = 10' şu şekilde batch_text:

n'select *

t

WHERE bir 10 ' =

Ancak, aynı dize için eşlenmesi. batch_text:

N'SELECT * FROM T WHERE, b = 10 '

SQL Server satır başı, satır besleme ve ilk sorguyu içinde boşluk karakterleri dikkate almaz.Ikinci sorguyu, sırası WHERE b = 10 gelen farklı yorumlanır WHERE a = 10. Büyük/küçük durum ve Aksan (Veritabanı Harmanlama büyük/küçük durum duyarlı olsa bile) duyarlı, dışında anahtar olması durumunda, büyük/küçük durum/küçük durum duyarlı olduğu eşlemesidir.Büyük küçük harf duyarlı anahtar sözcük formları shortened eşlemesidir.Örneğin, anahtar sözcükler EXECUTE, EXEC, ve execute eşit kabul edilir.

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

Plan önbelleğinde plan kılavuzu efekt

Bu modül için sorgu planını bir modülde bir plan kılavuzu oluşturma plan önbelleğinden kaldırır.Nesne veya SQL türü bir plan kılavuzu üzerinde bir toplu iş oluşturma, aynı karma değere sahip bir toplu iş için sorgu planını kaldırır.Tür TEMPLATE bir plan kılavuzu oluşturma plan önbelleğinden o veritabanındaki tüm tek deyimli toplu işlemleri kaldırır.

İzinler

Oluşturulacak bir plan kılavuzu türü OBJECT, başvurulan nesne ALTER izni gerektirir.Oluşturulacak bir plan kılavuzu türü SQL veya TEMPLATE, geçerli veritabanında ALTER iznine gerek duyar.

Örnekler

C.Bir plan kılavuzu bir sorgu için OBJECT türündeki bir saklı yordam oluşturma

Aşağıdaki örnek, uygulama tabanlı saklı yordam içeriğinde çalıştırılan bir sorguyla eşleşen bir plan kılavuzu oluşturur ve geçerli OPTIMIZE FOR Sorgu için ipucu.

saklı yordam aşağıdadır:

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ı yordam sorgu oluşturduğunuz 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''))';

b.Tek başına bir sorgu için SQL türü bir plan kılavuzu oluşturma

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

toplu iş iş işte:

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

Bu sorguyu oluşturulan paralel yürütme planı önlemek için , aşağıdaki plan kılavuzu oluş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)';

c.Oluşturma bir plan kılavuzu TEMPLATE parametreli formun sorgu türü

Aşağıdaki örnek, belirtilen bir forma parameterizes ve yönlendirir herhangi bir sorgu için eşleşen bir plan kılavuzu oluşturur. SQL Server Sorgunun parameterization zorlamak için . Aşağıdaki iki sorgu sözdizimi kurallarına göre eşdeğeri, ancak yalnızca sabit, hazır bilgi değerleri farklı.

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

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

Parametreli Sorgu formunda plan kılavuzu aşağıdadır:

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.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 parametre sorgusunun parametreli hale getirilmiş bir formdur. Kullanılacak sp_create_plan_guide kullanımda olduğu için bu değer elde etmek için yalnızca güvenilir bir şekilde sp_get_query_template sistem saklı yordamı.Aşağıdaki komut dosyası, hem parametreli hale getirilmiş bir sorguya edinmek ve bu planı kılavuz üzerinde oluşturmak için kullanılabilir.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.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)';

Important Note:
Içinde bir sabit harfleri değerinin @stmt sp_get_query_template için geçirilen parametre veri türü, sabit değer yerine bir parametre için seçilen etkileyebilir. Bu plan kılavuzu eşleşen etkileyecektir.Farklı parametre değeri aralık işlemek için birden çok plan kılavuzu oluşturmanız gerekebilir.

Parametreli hale getirilmiş bir TEMPLATE tabanlı içinde kullanmak üzere bir sorgu biçiminde elde etme hakkında daha fazla bilgi için plan kılavuzu, bkz: Parametreli sorgular için plan kılavuzları tasarlama.

d.Sorguda bir API imleç isteği'ni kullanarak gönderilen bir plan kılavuzu oluşturma

API sunucu imleci yordamlar gönderilen sorgu planı kılavuzları eşleştirir.Bu yordamlar, sp_cursorprepare sp_cursorprepexec ve sp_cursoropen içerir.ADO, OLE DB ve ODBC apı'ları sık kullandığınız uygulamaları etkileşimli SQL Server Sunucu imleçler API kullanarak. Daha fazla bilgi için bkz: API Server imleçler. Çağrı sunucu imleci yordamları API Bkz: SQL Server Profiler RPC görüntüleyerek izlemeler: profiler izleme başlatma olay.

Aşağıdaki veriler bir RPC görünür varsayalım: profiler izleme olayı ile ayarlamak istediğiniz bir sorgu için başlangıç bir plan kılavuzu:

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 AdventureWorks.Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;

Fark plan için SELECT Sorguya yapılan çağrı sp_cursorprepexec bir birleştirmeyi birleştirmek; ancak karma birleştirmek kullanacağınıza kullanmaktır. Kullanılarak gönderilen sorgu sp_cursorprepexec , bir sorgu dizesi hem de bir parametre dizesini içeren parametrelenmiştir. Tam olarak, karakter, karakter çağrısı için göründükleri şekilde, sorgu ve parametre dizesini kullanarak planın seçimi değiştirmek için aşağıdaki plan kılavuzu oluşturabilirsiniz 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 BETWEEN @P1 AND @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

Bu plan Kılavuzu tarafından etkilenen uygulama tarafından bu sorgunun sonraki çalıştırma ve karma birleştirmek sorgu işlemek için kullanılır.

KULLANÝN PLANLA sorgu ipucu kullanma hakkında bilgi için bir plan kılavuzu olan bir imleç gönderilen sorgu için bkz: USE PLAN Query ipucu işaretçiler içeren sorgular kullanma.

e.Önbelleğe alınmış bir planından XML gösterim planı alma tarafından bir plan kılavuzu oluşturma

Aşağıdaki örnek, ad hoc basit bir SQL deyim için bir plan kılavuzu oluşturur.Bu tablo için istenen sorgu planı plan kılavuzda doğrudan sorgu için XML gösterim planı belirterek sağlanan @hints Parametre. Örneğin, ilk planı önbelleğinde bir plan oluşturmak için SQL deyim yürütür.Bu örnek amacıyla, oluşturulan planı istediğiniz plan ve ek sorgu ayarlama gerekli olduğunu kabul edilir.Sorgu için XML gösterim planı sorgulayarak elde sys.dm_exec_query_stats, sys.dm_exec_sql_text, ve sys.dm_exec_text_query_plan dinamik yönetimi görüntüler ve atanan @xml\_showplan değişken. The @xml\_showplan variable is then passed to the sp_create_plan_guide deyim in the @hints parameter. Veya, bir plan kılavuzu planı önbelleğindeki bir sorgu planı kullanarak oluşturabilirsiniz sp_create_plan_guide_from_handle saklı yordamını.

USE AdventureWorks;
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

See Also

Reference

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

Yordam (Transact-SQL) veritabanı altyapısı depolanan

Yordam (Transact-SQL) sistem saklı

sys.dm_exec_sql_text (Transact-SQL)

sys.dm_exec_cached_plans (Transact-SQL)

sys.dm_exec_query_stats (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)

sp_get_query_template (Transact-SQL)