sp_create_plan_guide (Transact-SQL)

Sorgu ipuçları veya gerçek sorgu planları, veritabanı sorguları ile ilişkilendirme için plan kılavuzu oluşturur.Plan kılavuzları hakkında daha fazla bilgi için bkz: Plan kılavuzları anlama.

Transact-SQL sözdizimi kuralları

Sözdizimi

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

• [ @ Name = ] n'plan_guide_name'
  plan kılavuzuadıdır.Plan Kılavuzu adları geçerli veritabanına belirlenmektedir.plan_guide_namekurallarýna uymak zorunda tanımlayıcıları ve sayı işareti (#) ile başlatılamıyor.En uzun plan_guide_name olan 124 karakter.

• [ @ stmt = ] n'statement_text'
  Olan bir Transact-SQL deyim plan kılavuzuoluşturmak için.Zaman SQL Servertanıdığı uyan bir sorgusorgu iyileştiricisi statement_text, plan_guide_name sürer etkili.Başarılı olması için bir plan kılavuzu oluşturulması için statement_text tarafından belirtilen içerikte görünmelidir @ türü, @ module_or_batch, ve @ params parametreleri.

  statement_textsorgu iyileştiricisi ile ilgili toplu iş içinde sağlanan deyim veya modülü tarafından tanımlanan eşleştirmeye izin verir biçimde sağlanmalıdır @ module_or_batch ve @ params.Daha fazla bilgi için "Uyarılar" konusuna bakın. bölümüne geçebilirsiniz.Boyutunu statement_text server kullanılabilir bellek tarafından yalnızca sınırlandırılmıştır

• [@ türü = ]n'{nesne | SQL | Şablon}'
  varlık hangi tür statement_text görünür.Bu eşleştirme için içerik belirtir statement_text için plan_guide_name.

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

  • SQL
    Gösterir statement_text deyim tek başına veya toplu iş , gönderilen bağlamında görünür SQL Server üzerinden herhangi bir mekanizma.Transact-SQLortak dil çalışma zamanı (clr) nesneleri veya genişletilmiş saklı yordamlar veya exec kullanarak gönderdiğiniz ifadeleri n'sql_string', sunucu üzerinde toplu olarak işlenir ve bu nedenle olarak tanımlanması gereken @ türü = 'SQL'.SQL belirtilirse, sorgu ipucu PARAMETERİZASYONUNU {ZORLANAN | Basit} içinde belirtilemez @ ipuçları parametresi.

  • ŞABLON
    Gösterilen forma parameterizes herhangi bir sorgu uygulandığı plan kılavuzu gösterir statement_text.Şablon belirtilmezse, yalnızca PARAMETERİZASYONUNU {ZORLANAN | Basit} Sorgu ipucu olarak belirtilebilir @ ipuçları parametresi.Şablon planı kılavuzları hakkında daha fazla bilgi için bkz: Plan kılavuzları kullanarak sorgu parameterizasyonunu davranışı belirtme.

• [@module_or_batch ={ N' schema_name. ] object_name' | N'batch_text' | NULL}
  İçinde ya da nesnenin adını belirten statement_text görünür, ya da toplu iş metni, statement_text görünür.BİR toplu iş metin içeremezdatabase deyim.

  Bir uygulamadan gönderilen toplu iş eşleştirmek bir plan kılavuzu için batch_texaynı biçimde, t 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.Daha fazla bilgi için açıklamalar bölümüne bakın.

  [schema_name.]object_name adını belirtir bir Transact-SQL saklı yordam, skaler işlev, çoklu deyimli tablo-değerli işlevveya Transact-SQL DML tetikleyici içeren statement_text.schema_name Belirtilmezse, schema_name kullandığı şeması, geçerli bir kullanıcı.null belirtilirse ve @ türü = 'SQL', değerini @ module_or_batch değerine küme @ stmt.@ Türü = 'şablon**'**, @ 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.@ params yalnızca aşağıdakilerden biri olduğunda doğru uygular:

  • @ türü = 'SQL' veya 'şablon'.'Şablon', @ params null olmamalıdır.

  • statement_textkullanılarak gönderilen sp_executesql ve değeri @ params parametresi belirtilmişse, ya da SQL Server bir deyim parameterizing it.'dahili olarak gönderirParametreli sorgular teslimini veritabanındaki API (dahil olmak üzere odbc, ole DBve ADO.net) gibi SQL Server çağrıları olarak sp_executesql veya API sunucu imleç yordamlar; Bu nedenle, bunlar da SQL veya şablonu tarafından eşleştirilir plan kılavuzları.Parameterizasyonunu ve planı kılavuzları hakkında daha fazla bilgi için bkz: Nasıl SQL Server eşleşmeleri sorgular kılavuzları Plan.

  @parameter_name data_type için göndermiş gibi tam aynı biçimde sağlanmalı SQL Server kullanarak sp_executesql veya dahili olarak parameterizasyonunu sonra gönderilen.Daha fazla bilgi için açıklamalar bölümüne bakın.toplu iş parametreleri içermiyorsa, boş belirtilmelidir.Boyutunu @ params yalnızca sunucu kullanılabilir bellekle sınırlıdır.

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

  • N'OPTION (query_hint , ...n )
    Eşleşen bir sorgu eklemek için OPTION yan tümce belirtir @ stmt.@ ipuçları sözdizimi kurallarına göre bir select deyimiçinde OPTION yan tümce ile aynı olması gerekir ve sorgu ipuçları geçerli bir dizi içerebilir.

  • N'XML_showplan'
    Bir ipucu uygulanacak sorgu xml biçiminde planıdır.

    xml Showplan bir değişkene atama öneririz; Aksi halde, tek bir tırnak işareti koyarak herhangi tek tırnak içine Showplan çıkış gerekir.e. örneğe bakın

  • NULL
    OPTION yan tümce sorgunun içinde belirtilen herhangi bir varolan ipucu sorguya uygulanmaz gösterir.Daha fazla bilgi için, bkz. SEÇENEK yan tümcesi (Transact-SQL).

Açıklamalar

Bağımsız sp_create_plan_guide gösterilen sırayla sağlanmalıdır.Ne zaman sizin verdiğiniz parametreleri için değerleri sp_create_plan_guide, tüm parametre adlarını açıkça belirtilmelidir veya yok.Örneğin, @name = belirtilmişse, sonra @stmt = , @type =, vb. de belirtilmelidir.Benzer şekilde, @name = atlanmış ve ancak parametre değeri sağlanan kalan parametre adları da gözardı edilmesi gerekir ve yalnızca değerleri sağlanan.Bağımsız değişken adlarını sözdizimi anlamanıza yardımcı olacak açıklayıcı yalnızca, amaçlıdır.SQL ServerBelirtilen parametre adını parametre adının kullanıldığı konumda adı eşleşen doğrulamaz.

Birden fazla nesne veya SQL plan kılavuzu aynı sorgu ve toplu iş veya modül oluşturabilirsiniz.Ancak, tek bir plan kılavuzu saatetkinleştirilebilir.

Türü için nesne oluşturulamıyor kılavuzları planladığınız bir @ module_or_batch başvuru saklı yordam, işlevveya WITH ENCRYPTION yan tümce veya geçici belirtir DML tetikleyici değeri.

Bırakın veya bir işlev, saklı yordamveya etkin veya devre dışı, bir plan kılavuzutarafından başvurulan DML tetikleyici değiştirmek çalışılırken bir hata neden olur.plan kılavuzu tarafından başvurulan bir tetikleyici tanımlanmış olan bir tablo bırakma çalışılırken bir hata oluşur.

Not

Plan kılavuzları yalnızca kullanılabilir SQL Server Standart, geliştirici, değerlendirme ve Kurumsal sürümleri; Ancak, herhangi bir sürümündeki planı kılavuzları görülebilir.Ayrıca, herhangi bir sürümüne planı kılavuzları içeren bir veritabanı ekleyebilirsiniz.Plan kılavuzları kalır sağlam ne zaman geri yükleme veya yükseltilmiş sürüm için bir veritabanını iliştirmek SQL Server 2008.Sunucu yükseltme işleminden sonra her veritabanında planı kılavuzlarının desirability doğrulamanız gerekir.

Kılavuzu gereksinimleri eşleşen planlayın

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.

Plan Kılavuzu planı önbellek etkisi

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.

İzinler

NESNE türünün plan kılavuzu oluşturmak için başvurulan bir nesne üzerinde alter izni gerektirir.plan kılavuzu türü SQL veya şablonu oluşturmak için geçerli veritabanı üzerinde alter izni gerektirir.

Örnekler

A.plan kılavuzu , bir sorgu için object türündeki bir saklı yordamoluşturma

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''))';

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

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)';

C.plan kılavuzu türü bir sorgu parametreli formu için şablon oluşturma

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.

D.APIimleç isteği kullanılarak gönderilen bir sorguyu plan kılavuzu oluşturma

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.

E.Önbelleğe alınmış planından xml Showplan elde ederek plan kılavuzu oluşturma

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

Ayrıca bkz.

Başvuru

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

Veritabanı Altyapısı depolanan yordamlar (Transact-SQL)

Sistem saklı yordamları (Transact-SQL)

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)

fn_validate_plan_guide (Transact-SQL)

sp_get_query_template (Transact-SQL)