Aracılığıyla paylaş

  • Makale

Plan kılavuzları anlama

Bu konuda planı kılavuzları açıklar ve nasıl bunlar olamaz ya da doğrudan sorgu metnini değiştirmek istemiyor, sorguların performansını arttırmak için kullanılabileceğini açıklamaktadır.Plan kılavuzları, küçük alt küme küme küme sorgular dağıtılan bir üçüncü taraf satıcıdan bir veritabanı uygulaması değil yerine getirirken beklendiği gibi yararlı olabilir.Sorgu ipuçları veya sabit bir sorgu planı için eklemeden tarafından sorguları planı kılavuzları etki iyileştirme.Plan Kılavuzu'nda en iyi duruma getirilmiş istediğiniz Transact-sql deyim ve kullanmak istediğiniz sorgu ipuçları içeren bir seçenek yan tümce tümce tümce ya da sorgu en iyi duruma getirmek için kullanmak istediğiniz bir özel bir sorgu planı belirleyin.Sorgu yürütüldüğünde, SQL Server plan kılavuzu için Transact-sql deyim eşleşen ve sorgu Çalıştır seçeneği yan tümcesi ekler saat veya kullandığı belirtilen sorgu planı.

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.Plan kılavuzları, herhangi bir sürümünü içeren bir veritabanı da ekleyebilirsiniz.Plan kılavuzları kalır geri yükleme yüklemek veya bir veritabanı eklemek için yükseltilmiş sürüm olduğu gibi SQL Server 2008.

Eşleştirme sorguları planı kılavuzları

Plan kılavuzları aşağıdaki bağlamlarda yürütülen sorguları eşleşecek şekilde oluşturulabilir:

  • Bir object plan Kılavuzu yürütmek bağlamında sorguları eşleşir Transact-SQL saklı yordamları, skaler kullanıcı tanımlı işlevler, multi-deyim tablo değerli kullanıcı tanımlı işlevler ve dml Tetikleyiciler.

  • Sorguları yürütmek bağlamında tek başına bir sql plan Kılavuzu eşleşen Transact-SQL ifadeler ve toplu işlemleri olan parçası olmayan bir veritabanı nesnesi.sql tabanlı planı kılavuzları stratejisinin belirli bir formu sorguları eşleştirmek için de kullanılabilir.

  • Belirli bir formu stratejisinin tek başına sorguları bir şablon plan Kılavuzu eşleşir.Bu planı kılavuzları geçerli PARAMETERİZASYONUNU veritabanı sorguları bir sınıf için bir veritabanı ayarlama seçeneği geçersiz kılmak için kullanılır.Daha fazla bilgi için bkz: Basit parameterizasyonunu ve Zorunlu parameterizasyonunu.

Daha fazla bilgi için bkz: Nasıl SQL Server eşleşmeleri sorgular kılavuzları Plan.

Nesne planı kılavuzları

Aşağıdaki varsayalım saklı yordam, hangi işlemlerin @Country_region karşı dağıtılmış bir veritabanı uygulamasının bulunmaktadır parametresi, AdventureWorks2008R2 Veritabanı:

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ş için en iyi duruma getirilmiş varsayar ve @Country_region = N'AU' (Avustralya).Ancak, Avustralya kaynaklanan nispeten daha az sayıda satış siparişleri olduğundan, daha fazla satış siparişleri, ülkelerin parametre değerlerini kullanarak sorguyu yürütür, performans düşer.Çünkü en çok satış siparişleri için oluşturulan bir sorgu planı Amerika Birleşik Devletleri'nde kaynaklanan @Country_region = N'US' tüm olası değerleri için büyük olasılıkla daha iyi yapacağı @Country_region parametresi.

Eklemek için saklı yordam değiştirerek bu sorunu çözmek OPTIMIZE FOR sorgu ipucu sorgu.Ancak, çünkü saklı yordam ise dağıtılan uygulamasında, uygulama kodu doğrudan değiştiremezsiniz.Bunun yerine, aşağıdaki plan Kılavuzu'nda oluşturabilirsiniz AdventureWorks2008R2 veritabanı.

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

Ne zaman belirtilen sorgu içinde sp_create_plan_guide deyim yürütür, sorgu iyileştirme dahil etmek için önce değişiklik yapıldığında OPTIMIZE FOR (@Country = N''US'') yan tümce tümce tümce.

sql planı kılavuzları

sql planı kılavuzları uygulama çok tek başına Transact-SQL ifadeler ve toplu işlemleri.Sık sık, bu ifadeler bir uygulama tarafından kullanılarak gönderilen sp_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 sorgu oluşturulan paralel yürütme planı engellemek için aşağıdaki plan Kılavuzu oluşturun ve küme MAXDOP sorgu ipucu için 1 , @hints parametresi.

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

Oluşturmak istediğiniz deyim içeren toplu iş iş bir plan kılavuzu bir kullanım içeremez database deyim.

Önemli notÖnemli

İçin sağlanan değerleri @module_or_batch ve @params bağımsız değişkenleri sp_create_plan guide deyim gönderilen gerçek sorgu. karşılık gelen metin aynı olmalıdırDaha fazla bilgi için bkz: sp_create_plan_guide (Transact-sql) ve Oluşturmak ve sınamak için SQL Server Profiler'I kullanarak Plan kılavuzları.

sql plan kılavuzus de oluşturulabilir ZORLANAN için PARAMETERİZASYONUNU veritabanı seçeneği ayarladığınızda veya şablon aynı forma stratejisinin sorgulamaları plan kılavuzu belirten oluşturulan sorgu parametreli bir sınıf.Daha fazla bilgi için bkz: Parametre tabanlı sorguları planı kılavuzları tasarlama.

Şablon planı kılavuzları

Şablon planı kılavuzları için belirli sorgu formları parameterizasyonunu davranışı geçersiz kılmak için kullanılır.Bir şablon plan Kılavuzu aşağıdaki durumlardan birini oluşturabilirsiniz:

  • PARAMETERİZASYONUNU veritabanı seçeneği için ZORLANAN AYARLANDI ancak istediğiniz basit parameterizasyonunu kurallarına göre derlenmiş sorgu vardır.

  • PARAMETERİZASYONUNU veritabanı seçenek basit (varsayılan ayar) olarak AYARLANIR, ancak sorgularının bir sınıf üzerinde denenmesi için zorla parameterizasyonunu istediğiniz.

Daha fazla bilgi için bkz: Plan kılavuzları kullanarak sorgu parameterizasyonunu davranışı belirtme.

Aşağıdaki örnek, belirli bir formu parameterizes ve yönlendiren bir sorguyla eşleşen bir plan Kılavuzu oluşturur SQL Server parameterizasyonunu sorgu. zorlamak içinAşağıdaki iki sorgu sözdizimi kurallarına göre eşdeğerdir, ancak sadece 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 formunda sorgu.Kullanılmak üzere bu değeri elde etmek için yalnızca güvenilir şekilde sp_create_plan_guide kullanmak sp_get_query_template sistem saklı yordamı.Aşağıdaki komut dosyası, hem parametreleştirilmiş sorguyu elde etmek 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 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 notÖnemli

İçinde sabit hazır bilgi değeri @stmt parametresi geçirilen sp_get_query_template da etkileyecektir bu yerine parametre için seçilen veri türüdeğişmez. Bu plan Kılavuzu eşleşen etkiler.Birden fazla oluşturmanız gerekebilir plan kılavuzu farklı parametre değer aralıkları işlemek için.

Şablon planı kılavuzları sql planı kılavuzları ile birlikte de kullanabilirsiniz.Örneğin, bir sınıfı sorgu parametrelenmiştir emin olmak için bir şablon plan kılavuzu oluşturabilirsiniz.Daha sonra bir sql plan Kılavuzu sorgu parametreli formunda oluşturabilirsiniz.

Sabit bir sorgu planı için bir Plan kılavuz uygulanıyor

Nesne veya sql türü bir plan Kılavuzu sabit sorgu planı uygulayabilirsiniz.Sabit sorgu planı uygulama planı kılavuzları belirli bir sorgu için en iyi duruma getiricisi tarafından seçilmiş olandan daha iyi bir varolan yürütme planı hakkında bilmeniz yararlı olur.

Aşağıdaki örnek, basit bir ad hoc sql deyim için bir plan Kılavuzu oluşturur.Bu deyim için istenen sorgu planı plan Kılavuzu'nda doğrudan sorgu xml Showplan belirterek sağlanan @hints parametresi.Örneğin, ilk kez 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 ek sorgu ayarlama gerekli olduğunu kabul edilir.Sorgulayarak sorgu xml Showplan 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 atandığı @xml_showplan değişkeni.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ğ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

Yükseltme işleminden sonra planı kılavuzları doğrulanıyor

Re-evaluating ve plan Kılavuzu tanımları, uygulamanız için yeni bir sürümünü yükseltirken test öneririz SQL Server.Performans ayarlama gereksinimleri ve plan Kılavuzu eşleşen davranışını değiştirebilir.Geçersiz bir plan Kılavuzu bir sorgunun başarısız olmasına neden olur, ancak planı plan Kılavuzu kullanmadan derlenmiş ve en iyi seçim olabilir.Bir veritabanı için yükseltme yaptıktan sonra SQL Server 2008, aşağıdaki görevleri gerçekleştirmenizi öneririz: