Plan Kılavuzları'nı anlama

Bu konu, plan kılavuzları açıklanır ve nasıl olamaz ya da doğrudan sorgu metnini değiştirmek istemiyorsanız, sorgu performansını arttırmak için kullanılabilirler açıklar.Küçük alt küme küme küme sorgu, bir üçüncü taraf satıcıdan dağıtılmış bir veritabanı uygulaması değil gerçekleştirmek, beklendiği gibi plan kılavuzları yararlı olabilir.Sorgu için sorgu ipuçlarının ya da sabit bir sorgu planı ekleme tarafından kılavuzları etki iyileştirme planı.plan kılavuzu En iyi duruma getirilmiş istediğiniz Transact-SQL deyimni belirtmek ve kullanım veya sorguyu en iyi duruma getirmek için kullanmak istediğiniz bir özel bir sorgu planı için istediğiniz sorguyu içeren herhangi bir seçenek yan ipuçları.Sonra sorguyu yürütür,SQL Serverplan kılavuzu için Transact-SQL deyim ile eşleşir ve seçenek yan zamanında sorguya ekler veya kullandığı belirtilen sorgu planı.

Not

Planı kılavuzları yalnızca kullanılabilirSQL ServerStandart, geliştirici, değerlendirme ve Kurumsal sürümleri; ancak, plan kılavuzları bir sürüm. görülebilirHerhangi bir sürümüne planı kılavuzları içeren bir veritabanı da ekleyebilirsiniz.Plan kılavuzları, yükseltilmiş sürüm bir veritabanına eklemek veya geri yükleme olduğu gibi kalırSQL Server 2008.

Sorgu için eşleşen planı kılavuzları

Plan kılavuzu aşağıdaki bağlamlarda çalıştırılan sorguların eşleşecek şekilde oluşturulabilir:

• Bir nesne Kılavuzu eşleşen sorgu yürütmek bağlamında planıTransact-SQLsaklı yordam, skaler kullanıcı tanımlı işlevler, multi-deyim tablo deerli kullanıcı tanımlı işlevler ve DML tetikler.

• Bir SQL plan kılavuzu eşleşen kapsamında tek başına yürütmek sorgularıTransact-SQLifadeler ve toplu olan parçası olmayan bir veritabanı nesnesi.SQL tabanlı planı kılavuzları, belirtilen forma parameterize sorguları eşleştirmek için de kullanılabilir.

• Bir TEMPLATE plan kılavuzu belirtilen forma parameterize tek başına sorgu eşleşir.Bu plan kılavuzları geçerli PARAMETERIZATION veritabanında bir veritabanı sorgu bir sınıf için küme seçeneği geçersiz kılmak için kullanılır.Daha fazla bilgi için bkz:Basit ParameterizationveZorlanmış Parameterization.

Daha fazla bilgi için bkz:SQL Server sonuçları, sorgu kılavuzlara nasıl planlanır.

OBJECT planı kılavuzları

Aşağıdaki varsayalım saklı yordam, hangi alır@Country_regionkarşı dağıtılmış bir veritabanı uygulaması bulunmaktadır parametresi,AdventureWorksVeritabanı:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    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 = @Country_region
END;

Bu saklı yordam derlenmiş ve için en iyi varsay@Country_region = N'AU'(Avustralya).Ancak, Avustralya ' nispeten daha az sayıda satış siparişleri için daha fazla satış siparişleri, ülkelerin parametre değerlerini kullanarak sorgu çalıştırıldığında performans düşer.Çünkü en çok satış siparişleri kaynaklanan Amerika Birleşik Devletleri'nde, için oluşturulan sorgu planı@Country_region = N'US'büyük bir olasılıkla daha iyi tüm olası değerleri için yapmak@Country_regionparametre.

Eklemek için saklı yordam değiştirerek bu sorunu gidermekOPTIMIZE FORsorgu İpucu sorgu.Ancak, çünkü saklı yordam olan dağıtılmış bir uygulama, uygulama kodu doğrudan değiştiremezsiniz.Bunun yerine, aşağıdaki oluşturabileceğiniz plan kılavuzu,AdventureWorksveritabanı.

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT *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 = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))'

Sorgu, belirtilensp_create_plan_guidedeyimini yürütür, dahil etmek için en iyileştirme önce sorgu değiştirildiğindeOPTIMIZE FOR (@Country = N''US'')yan tümce.

SQL planı kılavuzları

SQL planı kılavuzlarını uygulamak için tek başınaTransact-SQLifadeler ve toplu işlemi.Sık sık Bu ifadeler kullanarak bir uygulama tarafından gönderilirSp_executesql sistem saklı yordamı.Örneğin, tek başına aşağıdaki toplu iş iş işi göz önünde bulundurun:

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şturmak ve ayarlamakMAXDOPsorgu ipucu için1,@hintsparametre.

sp_create_plan_guide 
@name = N'Guide2', 
@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)';

Not

toplu iş iş iş, istediğiniz bir plan kılavuzu oluşturmak deyim içeren bir USE içeremezdatabasedeyim.

Important Note:
İçin sağlanan değerler@ module_or_batch ve @ paramssp_create_plan kılavuz deyim bağımsız gönderilen gerçek sorgusu. karşılık gelen metin aynı olmalıdır Daha fazla bilgi için bkz:sp_create_plan_guide (Transact-SQL)veOluşturma ve sınama için SQL Server Profiler'ı kullanarak kılavuzları planlanır..

SQL plan kılavuzu s de aynı forma PARAMETERIZATION veritabanı seçeneğini FORCED için küme veya bir TEMPLATE parameterize sorgulamaları oluşturulur plan kılavuzu belirterek, oluşturulan sorgu parametreli bir sınıf.Daha fazla bilgi için bkz:Parametreli sorgular için plan kılavuzları tasarlama.

TEMPLATE planı kılavuzları

TEMPLATE planı kılavuzları özel bir sorgu formu parameterization davranışını geçersiz kılmak için kullanılır.Aşağıdaki durumlardan birinde bir TEMPLATE plan kılavuzu oluşturabilirsiniz:

• PARAMETERIZATION veritabanı seçeneğini FORCED için küme, ancak basit parameterization kurallarına göre derlenmiş istediğiniz sorgu vardır.

• PARAMETERIZATION veritabanı seçeneğini (varsayılan ayar) SIMPLE için küme, ancak isterseniz sorguları bir sınıfın çalıştı için zorunlu parameterization.

Daha fazla bilgi için bkz:Plan Kılavuzları'nı kullanarak sorgu Parameterization davranışı belirleme.

Aşağıdaki örnek oluşturur bir plan kılavuzu uyan herhangi bir sorgu yönlendirir ve belirli bir forma parameterizesSQL Serversorgu. parameterization zorlamak içinAşağıdaki iki sorgu sözdizimi eşdeğerdir, ancak yalnızca sabit hazır bilgi değerleri değişir.

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 formdaki sorgu planı Kılavuzu aşağıda verilmiştir:

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

Değeri önceki örnekte@stmtparametresi parametreli formun sorgu.Sp_create_plan_guide kullanmak kullanmak için bu değeri elde etmek için yalnızca güvenilir şekildesp_get_query_template saklı yordam. sistemi Aşağıdaki komut dosyası, hem de Parametreli Sorgu elde edilir ve bir 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 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:
Değeri sabit harfleri,@stmtsp_get_query_template için geçirilen parametre veri türü bu yerine parametre için seçilen etkiler değişmez.Bu plan kılavuzu eşleşen etkiler.Farklı parametre değeri aralık işlemek için birden fazla plan kılavuzu oluşturmak zorunda kalabilirsiniz.

TEMPLATE planı kılavuzları SQL planı kılavuzları ile birlikte de kullanılabilir.Örneğin, bir TEMPLATE oluşturabileceğiniz plan kılavuzu sınıf sorguları parametrelenmiştir emin olun.Daha sonra bir SQL plan kılavuzu parametreli sorgu formunda oluşturabilirsiniz.

Sabit bir sorgu planı için bir plan kılavuzu uygulama

Nesne veya SQL türü bir plan kılavuzu, bir sabit sorgu planı uygulayabilirsiniz.Bir sabit sorgu planı uygulama planı kılavuzları, belirli bir sorgu için en iyi duruma getiricisi tarafından seçilmiş olandan daha iyi yapan varolan yürütme planı bilmeniz olduğunda yararlıdır.

Aşağıdaki örnek oluşturur bir plan kılavuzu basit, özel bir SQL deyim için.Bu deyim için istenen Sorgu planını sağlanan plan kılavuzu doğrudan sorgu için XML gösterim planı belirleyerek@hints parametre.Örneğin ilk planı önbellekte bir plan oluşturmak üzere SQL deyim yürütür.Bu örneğin amaçları için oluşturulan plan istediğiniz plan ve ek sorgu ayarı gerekli olduğunu kabul edilir.Sorgu için the XML gösterim planı sys.dm_exec_query_stats sys.dm_exec_sql_text ve sys.dm_exec_text_query_plan Dinamik yönetim görünümü sorgulayarak alınır ve atanan@xml_showplandeğişken.The @xml\_showplan variable is then passed to the sp_create_plan_guide statement in the @hints parameter.Veya bir plan kılavuzu bir sorgu planı plan önbelleğe alınan kullanarak oluşturabileceğinizsp_create_plan_guide_from_handle saklı yordam.

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

Yükseltmeden sonra doğrulama planı kılavuzları

Re-evaluating ve test öneririz plan kılavuzu uygulamanıza yeni bir sürümü yükselttiğinizde tanımlarıSQL Server.Performans ayarlama gereksinimleri ve plan kılavuzu eşleştirme davranışını değiştirebilir.Bir plan geçersiz kılavuz bir sorgunun başarısız olmasına neden olur, ancak planı plan kılavuzu kullanmadan derlenir ve en iyi seçenek olmayabilir.Bir veritabanına yükseltme yaptıktan sonraSQL Server 2008, aşağıdaki görevleri gerçekleştirmeniz önerilir:

• Mevcut planın kılavuzları kullanarak doğrulasys.fn_validate_plan_guide işlev.

• KullanmaSQL Server Profiler, monitör kullanarak bazı saat misguided planları içinPlanlama Kılavuzu başarısız olay.

See Also

Concepts

Kılavuzlar, tasarım ve uygulama planı

Other Resources

Plan kılavuzları kullanarak sorguları, dağıtılmış uygulamaları en iyi duruma getirme

sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide_from_handle (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

sys.plan_guides (Transact-SQL)

sys.fn_validate_plan_guide (Transact-SQL)

Zorlanmış Parameterization

Basit Parameterization

Sorgu ipuçları (Transact-SQL)