Bu makalede MSSQL Server Memory’si Yeterlimi olup olmadığını görmüş olacağız. SQL Server’ın belleğinin (memory) yeterli olup olmadığını anlamak için, sistemin belleği nasıl kullandığını analiz etmek ve performans göstergelerini değerlendirmek gerekir. Aşağıda bu analizi yapmak için kullanılabilecek yöntemler ve göstergeler yer alıyor:
İşletim sistemi üzerinde bulunan tüm memory’i kullanma eğilimde olduğu için max server memory konfigürasyonunu yapmamız lazım bu yapıyı sql server kurulu olan instance üzerinde yapabilirsiniz.
Ayrıca max ve min server memory eşit değerde olması tavsiye işletim sisteminden allocate ve deallocate işlemiyle uğraşmaması içindir.
Memory kontrolü için önemli değerlere bakalım:
Page Life Expectancy (PLE)
Page Life Expectancy (PLE), SQL Server’ın veri sayfalarını bellekte ne kadar süre tutabildiğini ölçer. Daha yüksek bir değer genelde daha iyi performans anlamına gelir.
SELECT
[object_name],
[counter_name],
[cntr_value] AS PageLifeExpectancy
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy';
PLE değeri 300 saniyeden (5 dakikadan) düşük ise, bu genelde bellek yetersizliği anlamına gelebilir. Ancak, bu eşik sistemin boyutuna ve iş yüküne göre değişebilir. Aşağıdaki resimdeki gibi:
Bir tablo oluşturulup bir job aracılığıyla PageLifeExpectancy değerini tabloya insert işlemi yapabiliriz. Performans anlamında bizlere iyi bir katkı sağlayacaktır. Ayrı zamanda geriye dönük belirli zaman dilimlerinde ilgili değerleri gözlemlemiş oluruz.
Buffer Cache Hit Ratio
İkinci olarak Windows ve SQL Server performans sayaçları, belleğin yeterli olup olmadığını anlamak için kritik bilgiler sağlar. Bu sayaç, SQL Server’ın ihtiyaç duyduğu verilerin bellekte bulunma sayısını gösterir. Sql server’ın fiziksel diske erişmeden veriyi buffer cache üzerinden ne kadar sıklıkla sağlıya bildiğini ifade eder. Yüksek bir oran (genelde %90’ın üstü) belleğin yeterli olduğuna işaret eder.
SELECT
[object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [counter_name] = 'Buffer cache hit ratio';
Cntr_value değeri sql server’ın çalışmaya başladığı andan itibaren bellekte cache hit sayısını ifade eder. Sql server bu kadar kez veriyi bellekten bulduğunu göstermektedir.
Ancak oranı hesaplamak için bu değeri başka bir performans sayaç değeriyle karşılaştırmanız gerekir.
Gerçek oranı hesaplamak için, aşağıdaki iki sayaç birlikte kullanılır:
1. Buffer Cache Hit Ratio → Veriyi bellekte bulma sayısı.
2. Buffer Cache Hit Ratio Base → Toplam okuma isteği sayısı.
Değerler filtrelemeye özgü cntr_value değerini almaktadır. Farklı satırlardaki cntr_value değerini almaktadır.
SELECT
(a.[cntr_value] * 100.0 / b.[cntr_value]) AS BufferCacheHitRatio_Percentage
FROM
sys.dm_os_performance_counters AS a
JOIN
sys.dm_os_performance_counters AS b
ON
a.[object_name] = b.[object_name]
--AND a.[instance_name] = b.[instance_name] Birden fazla instance varsa kullanılır.
WHERE
a.counter_name = 'Buffer cache hit ratio'
AND b.counter_name = 'Buffer cache hit ratio base'
%90+ değerler idealdir ve SQL Server’ın çoğunlukla belleği kullanarak veriye eriştiğini gösterir.
Düşük değerler (ör. %70 altı) disk I/O yükünü artırabilir ve performans sorunlarına yol açabilir.
Eğer oran düşükse, aşağıdaki optimizasyonları düşünebilirsiniz:
- Bellek miktarını artırmak.
- Veritabanı sorgularını ve indeksleri optimize etmek.
- Disk altyapısını incelemek.
Memory Grants Pending
SQL Server’ın sorgular için bellek tahsisi yaparken bekleyen taleplerin sayısını gösterir. Bu sayaç 0 olmalıdır. Daha yüksek bir değer bellek baskısı olduğunu gösterir.
SELECT
[object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [counter_name] = 'Memory Grants Pending';
Hangi sorguların bellek beklediğini ve ne kadar talep ettiklerini görmek için şu sorguyu kullanabilirsin:
SELECT
session_id,
request_time,
grant_time, -- NULL ise bekliyor demektir
requested_memory_kb / 1024.0 AS Requested_MB,
granted_memory_kb / 1024.0 AS Granted_MB,
plan_handle
FROM sys.dm_exec_query_memory_grants
WHERE grant_time IS NULL;Bellek bekleyen her sorgu aslında verimsiz bir plandan kaynaklanıyor olabilir.
- Eksik Indexler: Eğer bir tablo taranıyorsa (Table Scan) ve büyük bir sıralama (Sort) veya birleştirme (Hash Join) işlemi yapılıyorsa, SQL Server devasa bir bellek talep eder. Index ekleyerek bu talebi düşür.
- İstatistikleri Güncelle: Güncel olmayan istatistikler, SQL’in küçük bir veri beklerken aslında milyonlarca satırla karşılaşmasına (Cardinality Estimation hatası) ve yanlış bellek talebine yol açar.
Plan Cache İncelemesi
SQL Server’da sık kullanılan sorgu planları bellekte saklanır. Eğer bellek yetersizse, sorgu planları sık sık temizlenebilir (eviction)
SELECT
[objtype],
COUNT(*) AS [Plan Count],
SUM(cast(([size_in_bytes] / 1024 / 1024) as bigint)) AS [Total Cache Size (MB)]
FROM sys.dm_exec_cached_plans
GROUP BY [objtype]
ORDER BY [Total Cache Size (MB)] DESC;
Her bir objtype için önbellekte saklanan planların sayısını verir.
Çok fazla Adhoc plan varsa, “Optimize for Adhoc Workloads” ayarı aktif edilebilir. Bu, SQL Server’ın sadece ilk çalıştırmada küçük bir plan oluşturmasını sağlar. Parametreleştirme kullanılarak plan yeniden kullanımı artırılabilir.
Prepared sorgular ve prosedürler en fazla bellek tüketimini yapıyor. Bu, doğal bir durumdur ama bellek limitlerini aşmamak için bu değerler izlenmelidir. Genellikle parametreli sorguların kullanıldığı yerlerde yaygındır.
Prepared sorgular, SQL Server (ve diğer veritabanı sistemleri) üzerinde önceden derlenmiş ve saklanmış SQL ifadeleridir. Bu yaklaşım performans, güvenlik ve tekrar kullanım açısından birçok avantaj sağlar.
Eğer Total Cache Size büyükse, SQL Server’ın tahsis ettiği maksimum bellek ayarları gözden geçirilmeli ve gerektiğinde artırılmalıdır.
Bellek yetersizliği sonucu sorguların bellek beklediğini gösterir.
SQL Server Maksimum Bellek Ayarını Kontrol Etme
SQL Server’ın kullandığı maksimum bellek miktarı ayarını kontrol edin. Eğer bellek kullanımı fiziksel belleğin tamamını kullanacak şekilde ayarlanmışsa, sistem genelinde performans sorunları yaşanabilir.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory';EXEC sp_configure 'max server memory (MB)', 8192; --Örnek: 8 GB
RECONFIGURE;
Çözüm ve Öneriler
1. Bellek Yetersizliği Belirtileri:
- Yüksek Memory Grants Pending değeri.
- Düşük Page Life Expectancy (300 saniyeden az).
- Buffer Cache Hit Ratio %90’ın altında.
- RESOURCE_SEMAPHORE beklemelerinin artması.
2. Belleği Optimize Etme:
- Sorguları ve indeksleri optimize edin.
- Gereksiz büyük sorgu planlarından kaçının.
- SQL Server maksimum bellek ayarını kontrol edin.
3. Fiziksel Belleği Arttırma:
Eğer belleği artırma imkanınız varsa, performans kazanımı için fiziksel belleği artırabilirsiniz. SQL Server’ın bellek kullanımı, sistemin genel performansında kritik bir rol oynar. Yukarıdaki yöntemler ve göstergeler, SQL Server’ın belleğinin yeterli olup olmadığını anlamaya ve gerekli önlemleri almaya yardımcı olur. Analiz sırasında hem SQL Server performans sayaçlarını hem de işletim sistemi metriklerini göz önünde bulundurmak önemlidir.
Bu makalede mssql server memory ile ilgili detaylı bilgileri görmüş olduk. Başka bir makalede görüşmek dileğiyle.
“Lokmân oğluna öğüt verirken ona şöyle dedi: “Sevgili oğlum! Allah’a ortak koşma; çünkü O’na ortak koşmak kesinlikle çok büyük bir haksızlıktır.””Lokman-13
