sp_create_plan_guide Sistem Stored Procedure’ü ile Sorgularınızın Performansını Artırın

sp_create_plan_guide sistem stored procedure’ü sql server 2005 ile hayatımıza girdi. Bu sp ile özellikle müdahale edemediğiniz yada değiştiremediğiniz sorgularınıza plan guide ekleyerek(plan guide’ın içersinde ilgili sp ya da tsql’e query hint ekliyoruz) performansını arttırabilirsiniz.

Plan guide’ı oluşturduktan sonra sp’yi ya da sql ifadesini normal bir şekilde çalıştırdığınızda artık plan guide’da belirttiğiniz şekilde çalışacaktır.

Veritabanında plan guide olup olmadığını anlamak için aşağıdaki script’i kullanabilirsiniz.

SELECT * FROM sys.plan_guides

Var olan bir plan guide’ı silmek istiyorsanız veritabanının altındaki programmability ve sonra Plan Guides sekmesine gelerek silebilirsiniz.

Belirli bir plan guide’ı silmek için aşağıdaki script’i de kullanabilirsiniz.

EXEC sp_control_plan_guide N'DROP', N'Guide3';

3 tip plan quide vardır.

1. Object Plan Guides
2. SQL Plan Guides
3. Template Plan Guides

Object Plan Guides:

Veritabanındaki bir nesneye uygulanan plan guide çeşididir. Örneğin bir stored procedure’de parameter sniffing yaşıyorsunuz ve sorgunun sonuna OPTIMIZE FOR hintini belirli nedenlerle ekleyemiyorsunuz.

Örneklere geçmeden önce parameter sniffing’i anlamak için “Parameter Sniffing” isimli makaleyi okumanızı tavsiye ederim.

Aşağıdaki şekilde stored procedure’e plan guide oluşturabilirsiniz.

Örnek Stored Procedure:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END

Gördüğünüz gibi stored procedure’ün parametre olarak Country(Ülke) parametresi var. Tablodaki Ülke kolonunda Amerika değerinin çok az olduğunu Türkiye değerinin çok fazla olduğunu düşünelim. Sorgu ilk çalıştığında Amerika değeri ile çalışırsa bir query plan üretecek ve daha sonra Türkiye ile çalıştığında parameter sniffing yaşanacaktır. Bu yüzden bir plan guide oluşturarak sp’ye aşağıdaki gibi bir query hint ekleyerek sp her çalıştığında query plan’ı türkiye için üret diyebiliriz. Bu şekilde gelen her parametre için daha hızlı sonuç verecektir.

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
        Sales.Customer c,
        Sales.SalesTerritory t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''TR''))'

SQL Plan Guides:

Adından da anlaşılacağı gibi bir nesne için değil de bir sql ifadesi için oluşturulan plan guide çeşididir.

Aşağıdaki gibi bir sorgu olduğunu düşünün.

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

Sorgunun çalıştığı instance üzerinde maxdop ayarının 8 olarak set edildiğini ve bu sorgunun da instance’daki maxdop ayarından dolayı yavaş çalıştığını varsayalım. Sorgunun sonuna OPTION (MAXDOP 1) query hint’ini ekleyerek sorgunun tek cpu’yu kullanarak çalışmasını sağlayabiliriz.

MAXDOP’un ne olduğunu anlamak için “Numa Nodes, MAX/MIN Server Memory, Lock Pages In Memory, MAXDOP” isimli makaleyi okumak isteyebilirsiniz.

Aşağıdaki örnekte sql plan guide kullanarak sorgunun sonuna maxdop hint’ini nasıl ekleyeceğimizi görebilirsiniz.

sp_create_plan_guide 
@name = N'Guide1', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC',  
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)'

Template Plan Guides:

 Veritabanında parameterization set ettiğimizde bazı sorguların davranış şekli değişir. Template plan guide ile spesifik sorgular için veritabanı seviyesinde set edilen parameterization şeklini değiştirebiliriz.

Parameterization için “Optimize for ad hoc workloads ve Parameterization” isimli makalemi okumak isteyebilirsiniz.

Örneğin veritabanında parameterization ayarı FORCE olarak set edilmişse ve siz bir sorgunun simple olarak çalışmasını istiyorsanız bu plan guide işinize yarayacaktır.

Aşağıdaki örnekte bunun nasıl yapıldığını görebilirsiniz.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
      FROM Production.ProductModel AS pm 
      INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID 
      WHERE pi.ProductID = 101 
      GROUP BY pi.ProductID, pi.Quantity 
      HAVING sum(pi.Quantity) > 50',
    @stmt OUTPUT, 
    @params OUTPUT;
EXEC sp_create_plan_guide 
    N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';