SQL Server MAXDOP ve Cost Threshold for Parallelism: Önerilen Ayarlar

SQL Server büyük bir sorguyu birden fazla CPU çekirdeğine bölerek çalıştırabilir. Doğru sorguda bu, dakikaları saniyelere indirir. Yanlış ayarlarla ise küçük sorgular da gereksiz yere paralel çalışır, çekirdekler birbirini bekler, CPU kullanımı yükselir ve aynı anda çalışabilen sorgu sayısı düşer. Paralelliği iki sunucu ayarı yönetir: max degree of parallelism (MAXDOP) ve cost threshold for parallelism (CTFP).

Bu rehberde iki ayarın ne yaptığını, Microsoft’un MAXDOP önerilerini, CTFP için makul bir başlangıç değerini nasıl bulacağınızı, CXPACKET beklemelerinin ne anlama geldiğini ve ayarları sunucu dışında hangi düzeylerde değiştirebileceğinizi anlatıyoruz.

İki Ayar, İki Soru

• Cost threshold for parallelism “bu sorgu paralel çalışmaya değer mi?” sorusunu yanıtlar. Optimizer’ın tahmini maliyeti bu değerin üzerindeki sorgular için paralel plan değerlendirilir. Varsayılan değer 5’tir.
• Max degree of parallelism “paralel çalışırsa en fazla kaç çekirdek kullanabilir?” sorusunu yanıtlar. Varsayılan değer 0’dır; yani SQL Server kullanılabilir tüm işlemcileri (en fazla 64) kullanabilir.

İki ayar birlikte düşünülmelidir. CTFP çok düşükse, tek çekirdekte milisaniyede bitecek küçük sorgular da paralel plana geçer. MAXDOP çok yüksekse, tek bir paralel sorgu sunucunun tüm çekirdeklerini meşgul eder ve diğer kullanıcılar bekler.

MAXDOP: Microsoft’un Önerileri

SQL Server 2016 ve sonrası için Microsoft’un önerisi, paralel bir sorgunun tüm worker’larını aynı NUMA düğümünde tutmayı hedefler:

Buradaki NUMA düğümü, SQL Server 2016 ve sonrasında otomatik oluşturulan soft-NUMA düğümleridir. SQL Server 2019 ve sonrasında kurulum sihirbazı, işlemci sayısına göre bir MAXDOP değeri önerir; çoğu kurulumda bu öneriyi kabul etmek iyi bir başlangıçtır. Azure SQL Database ve Azure SQL Managed Instance’ta yeni veritabanları için varsayılan değer zaten 8’dir.

Sunucunuzun NUMA yapısını görmek için:

SELECT node_id, memory_node_id, online_scheduler_count, cpu_count
FROM sys.dm_os_nodes
WHERE node_state_desc = N'ONLINE';

Cost Threshold for Parallelism: Varsayılan 5 Neden Düşük?

CTFP’nin varsayılan değeri 5, onlarca yıl önceki donanıma göre belirlenmiştir ve bugünün sunucularında birçok küçük sorgunun gereksiz yere paralel çalışmasına yol açar. Bu değer saniye değil, optimizer’ın kendi maliyet birimidir.

Microsoft tek bir değer önermez. Sahada yaygın yaklaşım, 30 ile 50 arasında bir değerle başlamak ve ardından iş yüküne göre ayarlamaktır. Daha isabetli bir başlangıç için plan cache’teki paralel planların maliyet dağılımına bakabilirsiniz:

-- Plan cache'teki paralel planların tahmini maliyet dağılımı
WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (50)
       n.value(N'@StatementSubTreeCost', N'float') AS estimated_cost,
       qs.execution_count,
       qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
       SUBSTRING(st.text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY qp.query_plan.nodes(N'//StmtSimple') AS s(n)
WHERE qp.query_plan.exist(N'//RelOp[@Parallel = "1"]') = 1
ORDER BY qs.execution_count DESC;

Çok sık çalışan, maliyeti düşük ve paralel plan kullanan sorgular, CTFP’nin artırılması gerektiğinin işaretidir. Bu sorgu plan cache’i taradığı için büyük ve yoğun sunucularda sakin bir saatte çalıştırın.

Ayarları Değiştirmek

EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;

EXEC sys.sp_configure N'max degree of parallelism', 8;
EXEC sys.sp_configure N'cost threshold for parallelism', 50;
RECONFIGURE;

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN (N'max degree of parallelism', N'cost threshold for parallelism');

İki ayar da yeniden başlatma gerektirmez ve hemen geçerli olur. Ancak değişiklik plan cache’i temizler; sorgular bir süre yeniden derleneceği için değişikliği yoğun saat dışında yapın ve sonrasında CPU ile bekleme istatistiklerini izleyin.

CXPACKET ve CXCONSUMER Beklemeleri

Paralel bir sorgu çalıştığında thread’ler birbirini bekler ve bu beklemeler CXPACKET ve CXCONSUMER olarak görünür. Bu beklemelerin varlığı tek başına sorun değildir; paralelliğin doğal bir sonucudur. CXCONSUMER genellikle zararsızdır. Dikkat edilmesi gereken durum, CXPACKET‘in toplam beklemelerin büyük bölümünü oluşturması ve buna yüksek CPU ile düşük eşzamanlılığın eşlik etmesidir; bu genellikle eşit dağılmayan iş yükünü, eksik index’leri ya da güncel olmayan istatistikleri işaret eder.

Sunucu Dışındaki Ayar Düzeyleri

MAXDOP yalnızca sunucu düzeyinde ayarlanmaz; daha dar kapsamlı ayarlar sunucu ayarını geçersiz kılar:

• Veritabanı düzeyi: ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4; Aynı sunucuda OLTP ve raporlama veritabanları farklı ihtiyaçlara sahipse kullanışlıdır.
• Sorgu düzeyi: OPTION (MAXDOP 1) sorgu ipucu ya da kodu değiştirmeden Query Store hint’leri.
• İş yükü düzeyi: Resource Governor workload group’larının MAX_DOP seçeneği; SQL Server 2025 ile Standard edition’da da kullanılabilir.
• Index işlemleri: ALTER INDEX ... REBUILD WITH (MAXDOP = 4); değer yalnızca o işlem için geçerlidir.

Paralellik Kontrol Listesi

• MAXDOP, NUMA yapısına göre Microsoft önerileriyle uyumlu; 0 bırakılmamış.
• CTFP varsayılan 5’ten yükseltilmiş ve plan cache maliyetlerine göre doğrulanmış.
• Sunucu genelinde MAXDOP 1 kullanılmıyor; gereken yerde veritabanı ya da sorgu düzeyinde ayar var.
• Değişiklik öncesi ve sonrası CPU ve bekleme istatistikleri karşılaştırıldı.
• Yüksek CXPACKET görülen sorgularda index ve istatistik durumu incelendi.