MSSQL Server’da sp_configure(Server Seviyesinde Konfigurasyon)

SQL Server performansını ve davranışlarını özelleştirmek için kullanılan temel araçlardan biri de sp_configure komutudur. Bu sistem procedure sayesinde, sunucu düzeyinde pek çok yapılandırma ayarı dinamik veya statik olarak değiştirilebilir. Bu yazıda, özellikle sık kullanılan ve sistem yöneticilerinin dikkat etmesi gereken sp_configure parametrelerini öne çıkaracağız.

Server seviyesinde konfigürasyon yapmak için çok önemli bir system stored procedure var sp_configure ile system seviyesinde ayarlamalarımızı yapabiliriz. System’den system’e değişiklik göstereceği için çoğu ayar bunların bulunduğunuz sisteme göre configure edilmesi gerekmektedir.

Parametre	Açıklama	Neden Açılır / Ayarlanır
show advanced options	Gelişmiş ayarları görünür kılar	Diğer ayarları yapabilmek için ilk adım
max server memory (MB)	Maksimum kullanılacak RAM	SQL Server’ın tüm RAM’i kullanmasını engellemek için sınır konur
min server memory (MB)	Minimum bellek eşiği	SQL Server’ın ihtiyaç duyduğu minimum belleği sabit tutar
max degree of parallelism	Maksimum CPU çekirdeği kullanımı	OLTP sistemlerde genelde 1, OLAP sistemlerde 4–8 arası seçilir
cost threshold for parallelism	Paralel sorgular için maliyet eşiği	Daha fazla sorgunun paralellikten kaçınması için 50–100 gibi artırılır
backup compression default	Yedeklerde sıkıştırma açılır	Depolama tasarrufu ve daha hızlı yedekleme
optimize for ad hoc workloads	Ad-hoc sorgu plan optimizasyonu	Plan cache şişmesini engellemek için açılır
remote admin connections	Uzak yönetimsel bağlantılara izin verir	Olağanüstü durumlarda DAC bağlantısı için
clr enabled	.NET tabanlı kod çalıştırma	Eğer özel CLR fonksiyonları/prosedürleri varsa
xp_cmdshell	Komut satırı erişimi	Büyük sistemlerde genellikle kapalı tutulur (güvenlik için)
lightweight pooling	Fiber threading	Çok özel durumlar dışında kullanılmaz (gelişmiş tuning gerekebilir)
default trace enabled	Varsayılan izleme	Performans ve izleme amaçlı, genelde açık bırakılır

Yukarıdaki kavramlardan bazılarını instance seviyesinde açalım ve gerekli bilgileri vermiş olalım.

Bazı ayarlar sunucunun yeniden başlatılmasını veya RECONFIGURE WITH OVERRIDE kullanılmasını gerektirebilir. Ve çoğu zaman önce show advanced options ayarını açman gerekir. RECONFIGURE ile set edilen değeri çalışan değer olarak yapıyoruz.

Bir kere açılması yeterlidir. Tüm sorgularda kullanılmasına gerek yoktur. Bazı kullanıcıların genel ayarları görmemesi için en sonda aşağıdaki komut 0 yapılmalıdır.

-- Gelişmiş ayarları aç (bir kez yapılır)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

Dedicated Admin Connection(DAC): DAC makalemde detaylı anlattığım yöntem herhangi bir sorun anında dba’in bu yordam sayesinde default 1434 portunu kullanarak bağlanmasıdır. Uzak sunucular için geçerlidir.

EXEC sp_configure 'remote admin connections',1
reconfigure

Remote Access: Uzaktan bağlantı sağlayan kullanıcıların local stored procedure kullanmasını engeller.

EXEC sp_configure'remote access',1
reconfigure

Max Server Memory: SQL SERVER’a maksimum bir memory set etmektir. Eğer set etmezsek sql server memory’in hepsini kullanma eğilimindedir. Aşağıdaki görsellerde örneği yapılmıştır.

-- max server memory ayarını değiştir
EXEC sp_configure 'max server memory (MB)', 4096;
RECONFIGURE;

Maximum Degree Of Parallelism: Sql server’da sorgularımızın paralellik seviyesini gösterir. İnstance bazında yada veritabanı bazında configure edilebilir. Herhangi bir değer set edilmezse paralellik seviyesini kendisi belirler. Aşağıdaki komut ile tüm sorgularda tek çekirdek çalışmaktadır. 0 değeri ile SQL Server, tüm uygun CPU çekirdeklerini kullanabilir.

EXEC sp_configure 'max degree of parallelism', 1;
RECONFIGURE;

Compression: Backup alınırken verinin sıkıştırılarak alınmasını sağlamaktadır. Bu özelliği instance bazlı aktif ederek her backup’ın otomatik olarak sıkıştırılarak alınması sağlanır.

EXEC sys.sp_configure 'backup compression default', '1'
reconfigure

CLR: Common Language Runtime sayesinde TSQL ile yapamayacağınız bir çok kompleks işlemi .NET Framework’ünü kullanarak gerçekleştirebilir ve ürettiği dll’i SQL Server’a import ederek veritabanı seviyesinde kullanabilirsiniz. CLR, .NET tabanlı dillerle (C#, VB.NET gibi) yazılmış kodları SQL Server içinde çalıştırmanı sağlar. Aşağıdaki kod yardımıyla da CLR’yi instance seviyesinde aktif edebilirsiniz. Güvenlik açığı oluşturacağı için bu yapının kapatılması tavsiye edilir. SSIS yapısı için bu yapının aktif olması gerekmektedir.

Kullanım Alanı	Açıklama
CLR Fonksiyonlar	C# ile yazılmış matematiksel veya string işlemleri daha performanslı yapabilirsin.
Stored Procedure / Trigger	.NET ile yazılmış prosedürleri çalıştırabilirsin.
Assembly Kullanımı	Dış kütüphaneleri (DLL) yükleyip çağırabilirsin.
Şifreleme / Hash Fonksiyonları	MD5, SHA256 gibi fonksiyonları daha esnek biçimde kullanabilirsin.
Dış Kaynaklara Erişim (Sınırlı)	Örneğin XML parsing, HTTP istekleri vs. (güvenlik izinlerine göre).

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

Yukarıdaki örnekleri çoğaltabiliriz. 77 tane değiştirebileceğimiz konfigürasyon var. System’in o an ihtiyacına göre karar verilmesi gerekiyor eğer önceden  bir değer set edersek veya herhangi bir değer set etmezsek system önceden verilmiş değeri veya default değeri kullanmaktadır. Fill Factor, Backup Compression, xp_cmdshell(SQL Server içerisinden işletim sistemi Command Prompt(CMD)’a komut göndermeyi ve çalıştırmaya imkan sağlayan Extended Stored Procedure’dür.)

Normalde sp_configure komutunu çalıştırdıktan sonra 23 tane tool geldi sizde sayısı farklı olabilir.

Aşağıdaki kod bloğunu çalıştırdıktan sonra tool sayımız artmış oldu bu ayarı işlemlerimiz bittikten sonra kapatmamız lazım yoksa başka kullanıcılar bu ayarları görebilir. Şimdi neden kapatmalıyız ona bakalım. İnstance bazında bu özelliği açık bıraktığımızda instance altında sa yetkilisi olmayan diğer kullanıcılarda bazı tanımlanan yetkilerle(login’e instance bazında yetkiler) bu ayarları değiştirebilirler. İlk başta sa kullanıcımızda show advanced options’u aktif edelim elimizdeki gerçek değerleri görmüş olalım.

EXEC sp_configure'show advanced options',1
reconfigure

Sa kullanıcımızla sp_configure’yi aktif ettikten sonra 77 kaydımızın olduğunu görmüş olduk. Şimdi sa yetkili olmayan loginimizle giriş yapalım ve bu kayıtları görüp görmediğimiz değişiklik yapıp yapamayacağımızı görmüş olacağız. Aşağıdaki resimde A1 kullanıcısının tüm system stored procedure’leri gördüğünü görüyoruz.

Diğer kullanıcıların system seviyesindeki tüm konfigürasyonları görmemesi içinde  sp_configureyi disable diyoruz.

EXEC sp_configure 'show advanced options',0
reconfigure

Artık sa kullanıcıda dahil tüm kullanıcılar tüm system konfigürasyonlarını görmemiş olacak.(default değerler hariç) Sadece minumum düzeyde yetkileri görmüş olacak ve değişikliği sadece sa kullanıcısı yapabilecek diğer tüm  kullanıcılar yetki verilmediği için değiştiremeyecek. Herhangi bir sql loginin sp_configure ayarlarını değiştirilmesi isteniyorsa sayfadaki ilgili makale okunabilir.

Resimde görüldüğü gibi sadece default değerler gelmiş oldu.

Buraya kadar ne yaptık system procedure’lerin kimlerin görüp görmediğine değinmiş olduk.

Yukarıdaki resimde bulunan config_value değeri allow poly.. procedure’in aktif edildiğini ama çalışıyor olmadığını gösteriyor. run_value değerinin 0 olduğu görülmektedir.

Recongifure ifadesiyle atanmış değeri çalışır hale getirmiş olduk.

Aşağıdaki resimde Show advanced options kapalı olmasına rağmen ekranda default olarak görünen değerleri değiştirebileceğimiz görünüyor ve değişirdik.

Şimdi sırada sp_configure çalıştırdığımızda görünen 23 system procedure’ü dışında bir  procedure işleme almaya çalışalım hatamı verecek mi. Yoksa show_advanced options seçeneği bir mi yapılır.

Kısacası hata almamızın sebebi Show advanced options’ın açık olmaması. Şimdi show advanced options enable yapıyorum tekrar çalıştıralım bakalım  max server memory değeri set edilecek mi?

Görüldüğü gibi değerimiz güncellenmiş oldu.

Sql server üzerinde güvenlik açığı yaratabilecek bazı özellikler:

Ole Automation Procedures: SQL Server içinde T-SQL üzerinden işletim sistemi (OS) seviyesinde bazı işlemleri yapabilmenizi sağlar. Örneğin: Dosya okuma/yazma, Sistem nesnelerine erişim (ör. registry), COM nesnelerini çağırma (ör. dosya oluştur, klasör listele) Bu özellik açık bırakılırsa: Saldırgan, SQL Server üzerinden işletim sistemi dosyalarına yazabilir/silebilir. Yetkili bir SQL hesabı ele geçirilirse, OS üzerinde zararlı işlemler yapılabilir. Güvenlik duvarınızı aşmadan sistem dosyalarınıza erişim sağlanabilir.

Eğer özellikle bu özelliğe ihtiyacınız yoksa devre dışı bırakılmalı:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;

CLR strict security: SQL Server içinde .NET (CLR) kodlarının çalıştırılmasını sıkı bir güvenlik denetimine tabi tutar. Yani yalnızca güvenli olarak işaretlenmiş ve doğru imzalanmış .NET derlemelerinin çalıştırılmasına izin verir.

Sıkı güvenlik kapalıysa açılmalı:

EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;

Temp_meta_data_optimization sql server 2022 ile birlikte gelen bir özellik olarak karşımıza çıkmaktadır. SQL Server’da geçici tablolar (#temp tabloları) ve tablo değişkenleri ile çalışırken ortaya çıkan meta veri kilitlenmelerini (metadata contention) optimize eden bir özelliktir. Bu yapını faydalarına söylemek gerekirse. Geçici tablo oluşturma/silme işlemlerinde sistem tabloları üzerindeki kilit çakışmalarını önler. Özellikle yoğun tempdb kullanımı olan sistemlerde performansı ciddi şekilde artırır. Aynı anda çok sayıda kullanıcının geçici tablo işlemleri yaptığı durumlarda sistemin daha iyi ölçeklenmesini sağlar. Sistem tabloları (sys.sysschobjs vb.) üzerindeki PAGELATCH_EX kilit çakışmalarını minimize eder. tempdb kaynaklı sıkıntılar varsa bu özellik aktif edilebilir.

Sunucumuzun SSMS yoksa  cmd komut satırından bağlanıp istediğimiz ayarları yapabiliriz.

SQL Authentication:

sqlcmd -S [ServerName] -U [loginname] -P [Password]

Windows Authentication:

sqlcmd –S [ServerName]

Not: İnstance üzerinde herhangi bir configuration değişikliklerini görmek için aşağıdaki İnstance altındaki Report kısmından görülmektedir.

Not: 32 bitlik işletim sistemlerinde sql server’ın kullanabileceği maksimum memory 4 gb’dır. sp_configure üzerinde bu ayarı aktif ederek 32 bitlik işletim sistemlerinde memory kısıtlaması olmadan memory’i kullanabiliriz. Bu işlem yapılmadan önce işletim sistemi tarafından Lock Pages In Memory yapısının aktif edilmesi gerekmektedir. 64 bitlik işletim sistemlerinde bu özelliğin aktif edilmesine gerek yoktur.

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO

Temp_meta_data_optimization sql server 2022 ile birlikte gelen bir özellik olarak karşımıza çıkmaktadır. SQL Server’da geçici tablolar (#temp tabloları) ve tablo değişkenleri ile çalışırken ortaya çıkan meta veri kilitlenmelerini (metadata contention) optimize eden bir özelliktir. Bu yapını faydalarına söylemek gerekirse. Geçici tablo oluşturma/silme işlemlerinde sistem tabloları üzerindeki kilit çakışmalarını önler. Özellikle yoğun tempdb kullanımı olan sistemlerde performansı ciddi şekilde artırır. Aynı anda çok sayıda kullanıcının geçici tablo işlemleri yaptığı durumlarda sistemin daha iyi ölçeklenmesini sağlar. Sistem tabloları (sys.sysschobjs vb.) üzerindeki PAGELATCH_EX kilit çakışmalarını minimize eder. tempdb kaynaklı sıkıntılar varsa bu özellik aktif edilebilir.

SQL Server’da performans optimizasyonu ve güvenlik için sp_configure üzerinden yapılan yapılandırmalar kritik bir rol oynar. Ancak bu ayarların bilinçli ve kontrollü yapılması gerekir; çünkü yanlış yapılandırmalar sistem genelinde olumsuz sonuçlara yol açabilir. Bu parametreleri tanımak, ne zaman ve nasıl kullanılacağını bilmek, güçlü bir SQL Server yönetiminin temel taşlarından biridir.

Bu makalede sp_configure ayarlarını detaylı bir şekilde görmüş olduk. Başka bir makalede görüşmek dileğiyle..

“Şüphesiz biz, ölüleri mutlaka diriltiriz. Onların yaptıklarını ve bıraktıkları eserlerini yazarız. Biz, her şeyi apaçık bir kitapta (Levh-i Mahfuz’da) bir bir kaydetmişizdir.”Yasin-12