sp_create_plan_guide (Transact-SQL)

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen ÖrneğiMicrosoft Fabric'te SQL veritabanı

Sorgu ipuçlarını veya gerçek sorgu planlarını veritabanındaki sorgularla ilişkilendirmek için bir plan rehberi oluşturur. Plan kılavuzları hakkında daha fazla bilgi için bkz. Plan Kılavuzları.

Transact-SQL söz dizimi kuralları

Sözdizimi

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

Arguments

[ @name = ] ‘name’

Plan rehberinin adı. @name , varsayılan olmayan ve maksimum 124 karakter uzunluğu olan sysname'dir. Plan rehberi isimleri mevcut veritabanına göre kapsamlıdır. @nametanımlayıcı kurallarına uymak zorundadır ve numara işaretiyle başlayamazlar (#).

[ @stmt = ] N'stmt'

Plan rehberi oluşturmak için Transact-SQL bir ifade. @stmtnvarchar(max), varsayılan olarak .NULL SQL Server sorgu optimizatörü @stmt eşleşen bir sorguyu tanıdığında, @name etki hale gelir. Bir plan rehberinin başarılı olması için @stmt @type, @module_or_batch ve @params parametreleriyle belirlenen bağlamda görünmelidir.

@stmt , sorgu optimizatorunun @module_or_batch ve @params tarafından tanımlanan parti veya modül içinde sağlanan ilgili ifadeyle eşleştirebileceği şekilde sağlanmalıdır. Daha fazla bilgi için Açıklamalar bölümüne bakın. @stmt boyutu yalnızca sunucunun mevcut belleği ile sınırlıdır.

[ @type = ] { N'OBJECT' | N'SQL' | N'ŞABLON' }

@stmt göründüğü varlık türü. Bu, @stmt@name ile eşleştirme bağlamını belirler. @typenvarchar(60)'dır ve şu değerlerden biri olabilir:

• OBJECT

  @stmt mevcut veritabanında Transact-SQL depolanmış bir prosedür, skaler fonksiyon, çoklu ifade tablo değerli fonksiyon veya Transact-SQL DML tetikleyicisi bağlamında göründüğünü gösterir.

• SQL

  Herhangi bir mekanizmayla SQL Server'a gönderilebilen bağımsız bir ifade veya toplu bağlamda @stmt göründüğünü gösterir. Transact-SQL Ortak Dil Çalışma Zamanı (CLR) nesneleri veya genişletilmiş depolanmış prosedürler tarafından gönderilen veya EXECUTE N'<sql_string>', kullanılarak sunulan ifadeler, sunucuda toplu olarak işlenir ve bu nedenle @type olarak SQLtanımlanmalıdır. Belirtilmişse SQL , sorgu ipucu PARAMETERIZATION { FORCED | SIMPLE }@hints parametresinde belirtilemez.

• TEMPLATE

  Plan rehberinin, @stmt'de belirtilen forma parametre yapan herhangi bir sorguya uygulandığını gösterir. BelirtilmişseTEMPLATE, PARAMETERIZATION { FORCED | SIMPLE } parametresinde yalnızca sorgu ipucu belirtilebilir. Plan rehberleri hakkında TEMPLATE daha fazla bilgi için, Plan Rehberleri Kullanarak Parametreleştirme Davranışını Sorgulama Davranışını Belirle' bölümüne bakınız.

[ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' }

Ya @stmt göründüğü nesnenin adını ya da @stmt göründüğü toplu metin belirtir. @module_or_batchnvarchar(max)'dır, varsayılan NULLolarak . Toplu metin bir USE <database> ifade içeremez.

Bir başvurudan gönderilen bir grupla eşleşecek bir plan rehberi için, @module_or_batch SQL Server'a gönderildiği aynı formatta, karakter karakter sağlanmalıdır. Bu eşleşmeyi kolaylaştırmak için iç dönüştürme gerçekleştirilmez. Daha fazla bilgi için Açıklamalar bölümüne bakın.

[ <schema_name>. ] <object_name> Transact-SQL depolanmış bir prosedürün, skaler fonksiyonun, çoklu ifade tablo değerli fonksiyonun veya @stmt içeren Transact-SQL DML tetikleyicisinin adını belirtir. Belirtilmediyse <schema_name> , <schema_name> mevcut kullanıcının şemasını kullanır. Eğer NULL belirtilirse ve @type ise SQL, @module_or_batch'in değeri @stmt değerine ayarlanır. Eğer @typeTEMPLATEise , @module_or_batch olmalıdır NULL.

[ @params = ] N'@parameter_namedata_type [ ,... n ]'

@stmt'ye gömülü tüm parametrelerin tanımlarını belirtir. @paramsnvarchar(max)'dır, varsayılan NULLolarak . @params yalnızca aşağıdaki seçeneklerden biri doğru olduğunda geçerlidir:

• @type ya SQLTEMPLATEda . Eğer TEMPLATE, @params olmamalıdır NULL.

• @stmt , kullanılarak sp_executesql gönderilir ve @params parametresi için bir değer belirtilir veya SQL Server parametre yaptıktan sonra dahili olarak bir ifade gönderir. Veritabanı API'lerinden (ODBC, OLE DB ve ADO.NET dahil) parametreli sorguların SQL Server'a gönderilmesi, API sunucusu işaretçi rutinlerine yapılan çağrılar sp_executesql olarak görünür; bu nedenle, bunlar rehberlerle SQL eşleştirilebilir veya TEMPLATE plan rehberleriyle de eşleştirilebilir.

@params , SQL Server'a gönderildiği formatla tam olarak aynı şekilde sunulmalıdır; ya kullanılarak sp_executesql ya da parametrizasyondan sonra dahili olarak gönderilmelidir. Daha fazla bilgi için Açıklamalar bölümüne bakın. Eğer parti parametre içermiyorsa, NULL belirtilmelidir. @params boyutu yalnızca mevcut sunucu belleği ile sınırlıdır.

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

@hintsnvarchar(max), varsayılan olarak .NULL

• OPTION ( <query_hint> [ , ...n ] )

  OPTION ile eşleşen bir sorguya eklenecek bir madde belirtir. @hints, sözdizimsel olarak OPTION bir SELECT cümledeki cümle ile aynı olmalıdır ve herhangi bir geçerli sorgu ipucu dizisini içerebilir.

• <XML_showplan>'

  XML formatındaki sorgu planı ipucu olarak uygulanacak.

  XML showplan'ı bir değişkene atamanızı öneririz. Aksi takdirde, gösteri planındaki tek tırnak işaretlerinden kaçınmak için onları başka bir tırnak işaretiyle öne koymalısınız. Bkz. örnek E.

• NULL

  Sorgu maddesinde belirtilen OPTION herhangi bir mevcut ipucunun sorguya uygulanmadığını gösterir. Daha fazla bilgi için bkz. OPTION maddesi.

Açıklamalar

Gerekecek sp_create_plan_guide argümanlar, gösterilen sırayla sunulmalıdır. Parametreler için sp_create_plan_guidedeğerler verdiğinizde, tüm parametre adları açıkça belirtilmeli ya da hiç belirtilmemelidir. Örneğin, @name = belirtilmişse, @stmt =, @type =, ve benzeri de belirtilmelidir. Benzer şekilde, @name = eğer atlanırsa ve sadece parametre değeri sağlanırsa, kalan parametre isimleri de çıkarılmalı ve sadece değerleri sağlanmalıdır. Argüman isimleri yalnızca tanımlayıcı amaçlar içindir, sözdizimi anlamaya yardımcı olmak için. SQL Server, belirtilen parametre adının, ismin kullanıldığı konumdaki parametrenin adıyla eşleşip eşleşmediğini doğrulamaz.

Aynı sorgu ve toplu ya da modül için birden OBJECT fazla plan SQL rehberi oluşturabilirsiniz. Ancak, aynı anda yalnızca bir plan rehberi etkinleştirilebilir.

Plan rehberleriOBJECT, maddeyi belirten veya geçici olan bir işlem, fonksiyon veya DML tetikleyicisine referans veren WITH ENCRYPTION bir değer için oluşturulamaz.

Plan rehberinde referans verilen bir fonksiyonu, depolanmış prosedürü veya DML tetikleyicisini kaldırmaya veya değiştirmeye çalışmak, etkinleştirilmiş veya devre dışı bırakılmış bir hata oluşturur. Üzerinde tanımlanmış bir tetikleyici olan ve plan rehberiyle referans verilen bir tabloyu düşürmeye çalışmak da hata yaratıyor.

Plan rehberleri SQL Server'ın her sürümünde kullanılamaz. SQL Server sürümleri tarafından desteklenen özelliklerin listesi için bkz. Sürümleri ve SQL Server 2022'nin desteklenen özellikleri. Plan kılavuzları herhangi bir sürümde görünür. Ayrıca herhangi bir sürüme plan kılavuzları içeren bir veritabanı ekleyebilirsiniz. Bir veritabanını SQL Server'ın yükseltilmiş bir sürümüne geri yüklerken veya eklerken plan kılavuzları değişmeden kalır. Sunucu yükseltmesi yaptıktan sonra her veritabanındaki plan rehberlerinin arzu edilebilirliğini doğrulamanız gerekir.

Plan rehberi eşleştirme gereksinimleri

Bir sorguyu başarılı şekilde eşleştirmek için @typeSQLTEMPLATE belirten plan rehberleri için, @module_or_batch ve @params değerleri [, ... n ] başvuru tarafından sunulan muadilleriyle tam olarak aynı formatta sunulmalıdır. Bu, toplu metni tam olarak SQL Server derleyicisinin aldığı şekilde sağlamanız gerektiği anlamına gelir. Gerçek toplu ve parametre metnini yakalamak için SQL Server Profiler kullanabilirsiniz. Daha fazla bilgi için, plan rehberleri oluşturmak ve test etmek için SQL Server Profiler'ı kullanın sayfasına bakınız.

@type ise SQL ve @module_or_batch olarak ayarlandığındaNULL, @module_or_batch'nin değeri @stmt değerine ayarlanır. Bu, @stmt değerinin SQL Server'a gönderildiği şekilde karakter karakter olarak tam olarak aynı formatta sağlanması gerektiği anlamına gelir. Bu eşleşmeyi kolaylaştırmak için iç dönüştürme gerçekleştirilmez.

SQL Server @stmt değerini @module_or_batch ile eşleştirdiğinde ve @params [, ... n ], veya @typeOBJECTise, içindeki <object_name>ilgili sorgu metnine , aşağıdaki dize elemanları dikkate alınmaz:

• Dizin içindeki beyaz boşluk karakterleri (sekmeler, boşluklar, araba dönüşleri veya satır beslemeleri)
• Yorumlar (-- veya /* */)
• Takip nokta virgüller

Örneğin, SQL Server @stmt diziyi N'SELECT * FROM T WHERE a = 10' aşağıdaki @module_or_batch ile eşleştirebilir:

 N'SELECT *
 FROM T
 WHERE a = 10'

Ancak aynı iplik bu @module_or_batch ile eşleştirilmez:

N'SELECT * FROM T WHERE b = 10'

SQL Server, ilk sorgudaki araba dönüşü, satır beslemesi ve boşluk karakterlerini görmezden gelir. İkinci sorguda, dizi WHERE b = 10 farklı şekilde yorumlanır WHERE a = 10. Eşleştirme, büyük harf ve aksan hassasiyetlidir (veritabanının derlenmesi beve duyarsız olsa bile), anahtar kelimeler varsa ve beik harf duyarsızdır. Eşleşme boşluklara duyarlıdır. Eşleştirme, anahtar kelimelerin kısaltılmış biçimlerine karşı duyarsızdır. Örneğin, anahtar EXECUTEkelimeler , EXEC, ve execute eşdeğer olarak kabul edilir.

Plan önbelleği üzerindeki plan rehberi etkisi

Modülde plan kılavuzu oluşturmak, bu modülün sorgu planını plan önbelleğinden kaldırır. Bir toplu türde OBJECT veya SQL üzerinde bir plan rehberi oluşturmak, aynı hash değerine sahip bir parti için sorgu planını kaldırır. Tip TEMPLATE bir plan rehberi oluşturmak, o veritabanındaki plan önbelleğinden tüm tek ifadeli grupları kaldırır.

Permissions

Türde OBJECTbir plan rehberi oluşturmak için, referans edilen nesne üzerinde izin gereklidir ALTER . Türde SQL veya TEMPLATEbir plan rehberi oluşturmak için mevcut veritabanında izin gereklidir ALTER .

Örnekler

A. Bir kayıtlı prosedürde bir sorgu için OBJECT tipinde bir plan rehberi oluşturun

Aşağıdaki örnek, uygulama tabanlı bir depolanmış prosedür bağlamında yürütülen bir sorguyu eşleştiren bir plan rehberi oluşturur ve ipucunu OPTIMIZE FOR sorguya uygular.

İşte saklanan prosedü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

İşte kayıtlı prosedürdeki sorgu üzerine oluşturulan plan rehberi:

EXECUTE 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. Bağımsız bir sorgu için SQL tipinde bir plan rehberi oluşturun

Aşağıdaki örnek, sistem depolanmış prosedürü kullanan bir uygulama sp_executesql tarafından gönderilen bir sorguyu eşleştirmek için bir plan rehberi oluşturur.

İşte parti:

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

Bu sorguda paralel yürütme planı oluşturulmasını önlemek için aşağıdaki plan rehberini oluşturun:

EXECUTE 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. Bir sorgunun parametreli formu için TEMPLATE tipinde bir plan rehberi oluşturun

Aşağıdaki örnek, belirli bir forma parametre yapan herhangi bir sorguyu eşleştiren bir plan rehberi oluşturur ve SQL Server'a sorguyu parametre olarak göstermesini zorunlu kılmasını sağlar. Aşağıdaki iki sorgu sözdizimi olarak eşdeğerdir, ancak yalnızca sabit literal değerleriyle farklılık gösterirler.

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

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

İşte sorgunun parametreli formunun plan rehberi:

EXECUTE sp_create_plan_guide
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2022.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, parametrenin @stmt değeri sorgunun parametreleştirilmiş biçimidir. Bu değeri elde etmenin sp_create_plan_guide tek güvenilir yolu, sp_get_query_template sistem depolanmış prosedürünü kullanmaktır. Aşağıdaki betik parametreli sorguyu alır ve ardından bunun üzerine bir plan rehberi oluşturur.

DECLARE @stmt AS NVARCHAR (MAX);
DECLARE @params AS NVARCHAR (MAX);

EXECUTE sp_get_query_template N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
                  ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;', @stmt OUTPUT, @params OUTPUT;

EXECUTE sp_create_plan_guide N'TemplateGuide1',
    @stmt, N'TEMPLATE', NULL,
    @params, N'OPTION(PARAMETERIZATION FORCED)';

Önemli

Aktarılan @stmtsp_get_query_template parametredeki sabit literallerin değeri, literali değiştiren parametre için seçilen veri türünü etkileyebilir. Bu, plan rehberi eşleşmesini etkiler. Farklı parametre değer aralıklarını yönetmek için birden fazla plan rehberi oluşturmanız gerekebilir.

D. API imleci isteği kullanılarak gönderilen bir sorgu için plan rehberi oluşturun

Plan rehberleri, API sunucu imleç rutinlerinden gönderilen sorguları eşleştirebilir. Bu rutinler arasında sp_cursorprepare, sp_cursorprepexec, ve sp_cursoropen. ADO, OLE DB ve ODBC API'lerini kullanan uygulamalar, API sunucu imlecleri kullanarak SQL Server ile sıkça etkileşime girer. SQL Server Profiler izlerinde API sunucu imleci rutinlerinin çağrılışını, profiler iz olayını görerek RPC:Starting görebilirsiniz.

Aşağıdaki veriler, plan rehberiyle ayarlamak istediğiniz bir sorgu için bir RPC:Starting profiler izleme etkinliğinde görünüyor:

DECLARE @p1 AS INT;
SET @p1 = -1;

DECLARE @p2 AS INT;
SET @p2 = 0;

DECLARE @p5 AS INT;
SET @p5 = 4104;

DECLARE @p6 AS INT;
SET @p6 = 8193;

DECLARE @p7 AS INT;
SET @p7 = 0;

EXECUTE sp_cursorprepexec
    @p1 OUTPUT,
    @p2 OUTPUT, N'@P1 varchar(255),@P2 varchar(255)', 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', @p5 OUTPUT, @p6 OUTPUT, @p7 OUTPUT, '20040101', '20050101';

SELECT @p1,
       @p2,
       @p5,
       @p6,
       @p7;

Çağrıdaki SELECT sorgu planının sp_cursorprepexec merge join kullandığını fark ediyorsunuz, ancak hash join kullanmak istiyorsunuz. Kullanılarak sp_cursorprepexec gönderilen sorgu, hem bir sorgu dizisi hem de bir parametre dizisi dahil olmak üzere parametrelendirilmiştir. Aşağıdaki plan rehberini oluşturarak soru ve parametre dizileri tam olarak göründükleri gibi, karakter karakter arayışta sp_cursorprepexeckullanarak plan seçimini değiştirebilirsiniz.

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

Uygulama tarafından bu sorguyu sonraki yürütmeleri bu plan rehberinden etkilenir ve sorgu işlemek için bir hash birleşimi kullanılır.

E. Önbelleklenmiş bir plandan XML showplan'ı alarak bir plan rehberi oluşturun

Aşağıdaki örnek, basit bir ad hoc SQL ifade için bir plan rehberi oluşturur. Bu ifade için istenen sorgu planı, soru için XML gösteri planı doğrudan @hints parametrede belirtilerek plan rehberinde sağlanır. Örnek, plan önbelleğinde plan oluşturmak için önce ifadeyi SQL çalıştırır. Bu örnek için, oluşturulan planın istenen plan olduğu ve daha fazla sorgulama ayarına gerek olmadığı varsayılır. Sorgu için XML gösteri planı, , sys.dm_exec_query_statsve sys.dm_exec_sql_text dinamik yönetim görünümleri sys.dm_exec_text_query_plansorgulanarak elde edilir ve değişkene @xml_showplan atanır. Değişken @xml_showplan daha sonra parametredeki sp_create_plan_guide ifadeye @hints iletilir. Ya da, plan önbelleğindeki bir sorgu planından sp_create_plan_guide_from_handle stored prosedürünü kullanarak plan rehberi oluşturabilirsiniz.

USE AdventureWorks2022;
GO

SELECT City,
       StateProvinceID,
       PostalCode
FROM Person.Address
ORDER BY PostalCode DESC;
GO

DECLARE @xml_showplan AS 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;%'
);

EXECUTE 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

İlgili içerik

• Plan Kılavuzları
• sp_control_plan_guide (Transact-SQL)
• sys.plan_guides (Transact-SQL)
• Veritabanı Altyapısı saklı 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)
• sys.fn_validate_plan_guide (Transact-SQL)
• sp_get_query_template (Transact-SQL)