ALTER DATABASE SCOPED CONFIGURATION ile Veritabanı Seviyesinde Konfigürasyon

SQL Server 2016 ile beraber, MAXDOP gibi instance seviyesinde ayarlanan bazı kritik konfigürasyonları veritabanı seviyesinde konfigüre edebilmemize olanak veren ALTER DATABASE SCOPED CONFIGURATION ifadesi hayatımıza girdi. Özellikle konsolide edilmiş sistemlerde veritabanı bazında farklı konfigürasyon yapmanız gerekebiliyor. Bu anlamda bence bu çok güzel bir gelişme.

 

Veritabanı seviyesinde aşağıdaki ayarları konfigüre edebiliyoruz. Veritabanı üzerine sağ tıklayarak properties dedikten sonra Options sekmesinden erişebilirsiniz.

 

Legacy Cardinality Estimation: SQL Server 2014’te, sorgular için, sorguların sonucunun kaç satır geleceğini tahmin ederek daha iyi query plan üretilmesini sağlayan Cardinality Estimator yeniden dizayn edildi. Sorguların yeni cardinality estimator’ı kullanması için Compatibility Level’in 120 ve üzeri olması gerekir.

 

Sorguların %98’i için bu cardinality estimator ile daha iyi query plan üretiliyor. Fakat %2’lik dilim için Legacy CE ters etki yapabiliyor. Böyle durumlarda compatibility level’i 110’a çekebilirsiniz. Ama yeni compatibility level’in getirdiği bir sürü yenilikten mahrum kalmış olursunuz.

 

Ya da compatibility level’i downgrade yapmak yerine aşağıdaki script yardımıyla sorgunun çalıştırıldığı veritabanı için eski cardinality estimator’ı kullan diyebilirsiniz.  Default değeri OFF’tur ve compatibility level 120 ve üzerisi yeni cardinality estimator’ı kullanır.

 

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=ON ;

 

 

Sorgu bazlı eski cardinality estimator’ı kullanmasını isteyebilirsiniz. Bunu için SQL Server 2016 SP1 ile gelen query hint’i aşağıdaki şekilde kullanabilirsiniz. Sorgunun sonuna eklemelisiniz.

 

OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

 

 

Eğer Always ON kullanıyorsanız aşağıdaki sorgu yardımıyla secondary veritabanınız için primary veritabanında nasıl hareket ediyorsa o şekilde hareket et diyebilirsiniz.

 

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=PRIMARY ;

 

 

Ya da Secondary veritabanı için primary veritabanından farklı bir ayar da set edebilirsiniz.

 

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION=OFF;

 

 

MAXDOP: Sorguların paralellik seviyesini, öncesinde instance ya da sorgu seviyesinde ayarlayabiliyorduk. Artık spesifik veritabanları için spesifik MAXDOP değerleri ayarlayabiliyoruz. MAXDOP için “Numa Nodes, MAX/MIN Server Memory, Lock Pages In Memory, MAXDOP” isimli makaleyi okuyabilirsiniz.

 

Veritabanı seviyesinde ayarladığımız konfigürasyon instance seviyesinde ayarladığımız konfigürasyonu override ediyor. Eğer session seviyesinde bir maxdop set edilirse o da veritabanı seviyesindekini override edecektir.

 

Aşağıdaki sorgu yardımıyla veritabanı seviyesinde maxdop set edilebilir.

 

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP =1 ;  

 

 

Eğer Always On kullanıyorsanız seconday veritabanınız için aşağıdaki şekilde farklı bir maxdop ayarı set edebilirsiniz.

 

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=4 ;

 

 

Ya da aşağıdaki gibi primary veritabanında hangi maxdop ayarı kullanılıyorsa o ayar kullanılsın diyebiliyorsunuz.

 

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP=PRIMARY;

 

 

Parameter Sniffing: Veritabanı seviyesindeki bu ayarın ne olduğunu anlamak için öncelikle parameter sniffing’in ne olduğunu idrak etmemiz gerekir. “Parameter Sniffing” isimli makalede detaylı bir içerik bulabilirsiniz.

 

Veritabanı seviyesinde parameter sniffing sorununu çözmek için aşağıdaki script’i kullanabilirsiniz. Bu script ile veritabanına gelen bütün sorgular sonuna “OPTIMIZE FOR UNKNOWN” eklenmiş gibi hareket ediyor.

 

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING =OFF;

 

 

 

Aşağıdaki script yardımıyla da always on kullanıyorsanız secondary veritabanında parameter sniffing olmasını engelleyebilirsiniz.

 

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING=OFF;

 

 

Ya da aşağıdaki script yardımıyla parameter sniffing konusunda secondary veritabanı, primary veritabanı nasıl davranıyorsa öyle davransın diyebilirsiniz.

 

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING=PRIMARY;

 

 

 

Query Optimizer Fixes: Bu konfigürasyonu aktif ederek veritabanının compatibility level’ine bakmadan Query Optimizer ile ilgili son hotfix’lerin avantajlarından faydalan demiş oluyoruz.

 

Aşağıdaki şekilde aktif edebiliriz.

 

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON;

 

 

Aşağıdaki script ile de, always on kullanıyorsanız secondary veritabanı, primary veritabanı nasıl davranıyorsa öyle davransın diyebilirsiniz.

 

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES =PRIMARY;

 

 

Clear Procedure Cache: Veritabanındaki procedure cache’i temizlemeye yarıyor. Aşağıdaki script ile bu işlemi gerçekleştirebilirsiniz.

 

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

 

 

Identitiy Cache: SQL Server 2017 ile beraber gelen bir veritabanı konfigürasyonu. Eğer bir identity kolonunuz varsa ve bu kolonun olduğu tabloya bir insert yapılırken commit etmeden failover gerçekleşirse yada servis beklenmedik bir şekilde kapanırsa otomatik olarak artan identitiy değerlerinizde boşluklar oluşur. Örneğin 5 kayıt insert ettiniz. ID değerleri 1,2,3,4,5 diye gitti. Sonraki kayıtların 6,7,8 şeklinde devam etmesi gerekirken servisin beklenmedik şekilde kapanmasının ya da failover olmasından sonra 1003,1004 şeklinde devam eder. Identity cache’i aşağıdaki script yardımıyla disable ederek bu sorunu çözebiliyoruz.

 

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

 

SQL Server Query Hint Kavramı ve Bazı Query Hint’ler

Query Hint’ler sorguların sonuna eklenerek sorgunun normal çalışma şeklini değiştirebilirler. Örneğin tek cpu’yu kullanarak çalışan bir sorgunun sonuna maxdop query hint’ini ekleyerek birden fazla cpu üzerinde çalışmasını ya da sorgunun her çalışmada yeniden query plan üretmesini sağlamak için recompile query hint’ini kullanabilirsiniz.

 

Query Hint’i uygulamadan önce yaptığınız işlemin istediğiniz sonucu verdiğinden emin olmanız gerekir. Çünkü query hint kullanarak SQL Server’ı normal davranışından uzaklaştırarak istediğiniz şekilde hareket etmeye zorlamış oluyorsunuz. Makalede belirtilen script’ler microsoft’un sitesinden alınarak AdventureWorks2012 veritabanı üzerinde test edilmiştir.

 

Aşağıda işinize yarayabileceğini düşündüğüm bazı query hint’lerinin açıklamalarını ve örneklerini paylaşıyorum.

 

RECOMPILE Query Hinti:

SQL Server’da stored procedure’ler ve parametreli gelen sorgular ilk kez çalıştığı anda compile edilerek bir query plan üretirler ve sonraki çalışmalarında tekrar compile edilmeden daha önce ürettikleri query plan’ı kullanarak sorgu her çalıştığında tekrar compile etme yükünden kurtulmuş olurlar.

 

Bu davranış şekli çoğu zaman performansı artırır. Fakat tablodaki veri dağılımı dengesizse bazen parameter sniffing’e sebep olur. “Parameter Sniffing” isimli makalede parameter sniffing ile ilgili detayları bulabilirsiniz. Bu yüzden Recompile Query Hint’i ile sorgunun her çalıştığında yeniden query plan üretmesini isteyebilirsiniz. “Parameter Sniffing” isimli makalede recompile örneğini bulabilirsiniz.

 

OPTIMIZE FOR Query Hinti:

Query Plan üretilirken query optimizer’a, Lokal bir değişken için belirli bir değer kullanması için talimat veren query hint’idir. RECOMPILE query hint’i gibi bu query hint’de parameter sniffing’de işimize yarayabilir. “Parameter Sniffing” isimli makalemde bu query hint çeşidi ile de örnek bulabilirsiniz.

 

USE PLAN Query Hinti:

 

Query Plan üretilirken query optimizer’a, bir sorgu için mevcut bir query plan kullanması için talimat veren query hint’idir.

 

Eğer çalışma süresinin uzun olduğunu ve yanlış query plan ile çalıştığını düşündüğünüz sorgularınız varsa ve sorgunun daha hızlı çalışabilmesi için gerekli bir query plan olduğunu biliyorsanız bu query hint’ini kullanabilirsiniz.

 

Ama benim tavsiyem bu işi sql server’a bırakmanız ve istatistiklerinizi güncel tutmanızdır. İstatistikler hakkında detaylı bilgi almak için “SQL Server’da İstatistik Kavramı ve Performansa Etkisi” isimli makaleyi okumak isteyebilirsiniz.

 

Yinede ben USE PLAN Query Hint’ini kullanmak istiyorum diyorsanız aşağıdaki linkte nasıl kullanacağınızla ilgili detaylar bulabilirsiniz.

 

https://technet.microsoft.com/en-us/library/ms186954(v=sql.105).aspx

 

 

PARAMETERIZATION Query Hinti:

 

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.

 

Template Plan Guide için,

 

 “sp_create_plan_guide Sistem Stored Procedure’ü ile Sorgularınızın Performansını Artırın” isimli makaleyi,

 

 

Parameterization için,

 

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

 

 

MERGE JOIN Query Hinti:

 

Aşağıdaki örnekte join işlemini MERGE JOIN yapmaya zorlayan bir query hint’i kullanılmış. SQL Server’da join işlemleri ile ilgili detaylı bilgi almak için “SQL Server’da Join Türleri” ve “SQL Server Execution Plan’daki JOIN Türleri” isimli makalelerden faydalanabilirsiniz.

SELECT *  
FROM Sales.Customer AS c 
INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID 
WHERE TerritoryID = 5 
OPTION (MERGE JOIN); 
GO   

 

 

MAXRECURSION Query Hinti:

 

MAXRECURSION Query Hinti kötü tasarlanmış bir CTE(Common Table Expression)’nin sonsuz döngüye girmesini engeller. CTE(Common Table Expression) hakkında detaylı bilgi almak için “Common Table Expression(CTE)” isimli makaleyi okumak isteyebilirsiniz.

 

Aşağıdaki örnekte sınırsız döngüye giren bir CTE oluşturulup arkasından MAXRECURSION query hint’i kullanılarak maksimum döngü sayısını 2 olarak set ediyoruz.

 

–Sonsuz bir döngü oluşturan CTE

WITH cte (CustomerID, PersonID, StoreID) AS 
( 
    SELECT CustomerID, PersonID, StoreID 
    FROM Sales.Customer 
    WHERE PersonID IS NOT NULL 
  UNION ALL 
    SELECT cte.CustomerID, cte.PersonID, cte.StoreID 
    FROM cte  
    JOIN  Sales.Customer AS e  
        ON cte.PersonID = e.CustomerID 
) 
--CTE'yi kullan ve MAXRECURSION query hint'ini kullanan sorgu
SELECT CustomerID, PersonID, StoreID 
FROM cte 
OPTION (MAXRECURSION 2); 
GO 

 

 

Sorguyu veritabanında çalıştırdığımda 2 kere loop’a girdikten sonra aşağıdaki gibi bir hata ile script’i sonlandırdı.

 

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 2 has been exhausted before statement completion.

 

 

Sorgudaki OPTION(MAXRECURSION 2)  hint’ini kaldırdığınızda hata şekli aşağıdaki gibi oldu. SQL Server 2014 üzerinde bu testi gerçekleştirdim. 100 kere loop’a girdikten sonra SQL Server otomatik olarak script’i durdurdu.

 

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

 

 

MAXDOP Query Hinti:

 

Sorgunun sonuna OPTION(MAXDOP 2) hint’ini koyarak, maxdop ayarı instance üzerinde ne seviyede ayarlanmış olursa olsun bu sorgunun 2 core’u kullanarak çalışması gerektiğini belirtmiş olduk.

 

Özellikle iş yükü fazla olan sorgular için kullanmakta faydalı olacak bir query hint’idir.

 

Aşağıdaki şekilde kullanabilirsiniz.

 

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty OPTION (MAXDOP 2); GO

 

 

FORCE INDEX Query Hinti:

 

SQL Server’da bir sorguya şu index’i kullanarak çalış demek için bu hint’i kullanırız. Aslında SQL Server hemen hemen her zaman en iyi seçimi yapar. Bu yüzden istatistiklerinizi güncel tutup, index’lerini düzenli olarak rebuild-reorganize yapıyorsanız bu hint’i kullanmanız genelde sizi hızlandırmaktan çok yavaşlatacaktır.

 

İstatistikler ve index’ler hakkında aşağıdaki makaleleri okumanızı tavsiye ederim.

 

“SQL Server’da Index Kavramı ve Performansa Etkisi“,

“SQL Server’da İstatistik Kavramı ve Performansa Etkisi“

 

Aşağıdaki select ifadesinin aşağıdaki ekrandaki kutucuğa tıklayarak execution plan’ını çıkaralım. Gördüğünüz gibi normal şartlar altında aşağıdaki gibi Clustred Index Scan işlemi gerçekleşiyor.

 

USE AdventureWorks2014Yeni
GO
SELECT *
FROM Person.Person
GO

 

 

 

Sorguya aşağıdaki gibi bir hint ekleyelim. Gördüğünüz gibi belirttiğimiz Index’i kullandı ve daha sonra ihtiyaç duyalan kayıtları getirmek için Key Lookup yapmak durumunda kaldı.

 

 

 

Kullanımını çok ender durumlarda lazım olabileceği için göstermek istedim fakat gerçekten gerekmedikçe kullanmak daha öncede belirttiğim gibi performansınızı azaltacaktır.

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