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 ;