MSSQL Server Memory’si Yeterlimi

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.

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.

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ı.

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]
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.

Aşağıdaki sorgu ile verinin page life expectancy değeri ile verinin ne kadar memory üzerinde kaldığını ayrıca Buffer Cache Hit Ratio ile verinin disk üzerinden değilde memory üzerinde bulma ve bulunma sayısını bir tabloya kaydedip daha sonra tablo üzerinden takip işlemi yapılması amaçlanmaktadır. Büyük sistemlerde AG kontrol yapıldıktan sonra komutun çalıştırılması gerekmektedir.

CREATE TABLE PageLifeBufferCachTable (
    [object_name] varchar(50),
    [counter_name] varchar(50),
    [PageLife/BufferCache] varchar(50),
	Tarih datetime
);


-- İlk insert
INSERT INTO PageLifeBufferCachTable (object_name, counter_name, [PageLife/BufferCache],Tarih)
SELECT
    [object_name],
    [counter_name],
   [cntr_value],
   getdate()
FROM
    sys.dm_os_performance_counters
WHERE
    [object_name] LIKE '%Buffer Manager%'
    AND [counter_name] = 'Page life expectancy';

	WAITFOR DELAY '00:00:02'

-- İkinci insert
INSERT INTO PageLifeBufferCachTable (object_name, counter_name, [PageLife/BufferCache],Tarih)
	SELECT
	 a.[object_name],
	  'Buffer Cache Hit Ratio (%)' AS counter_name,
    (a.[cntr_value] * 100.0 / b.[cntr_value]),
   getdate()
FROM
    sys.dm_os_performance_counters AS a
JOIN
    sys.dm_os_performance_counters AS b
ON
    a.[object_name] = b.[object_name]
WHERE
    a.counter_name = 'Buffer cache hit ratio'
    AND b.counter_name = 'Buffer cache hit ratio base'

WAITFOR DELAY '00:00:02'

-- Ücüncü insert
INSERT INTO PageLifeBufferCachTable (object_name, counter_name, [PageLife/BufferCache],Tarih)
SELECT
[object_name],
[counter_name],
[cntr_value],
getdate()
FROM sys.dm_os_performance_counters
WHERE [counter_name] = 'Memory Grants Pending';


--truncate table PageLifeBufferCachTable
--DELETE FROM dbo.PageLifeBufferCachTable WHERE Tarih< DATEADD(DAY, -1, GETDATE());

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

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.

Wait Stats Analizi
SQL Server’ın kaynak bekleme durumlarını analiz ederek bellekle ilgili sorun olup olmadığını anlayabilirsiniz:

WITH Waits AS  
(  
    SELECT  
        wait_type,  
        wait_time_ms / 1000 AS WaitTimeSec,  
        signal_wait_time_ms / 1000 AS SignalWaitTimeSec,  
        (wait_time_ms - signal_wait_time_ms) / 1000 AS ResourceWaitTimeSec,  
        waiting_tasks_count AS WaitCount  
    FROM sys.dm_os_wait_stats  
    WHERE wait_type NOT IN (  
        'SLEEP_TASK', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',  
        'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SQLTRACE_BUFFER_FLUSH',  
        'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT'  
    ) -- Gereksiz beklemeleri hariç tutuyoruz
)  
SELECT  
    wait_type,  
    SUM(WaitTimeSec) AS WaitTimeSec,  
    SUM(SignalWaitTimeSec) AS SignalWaitTimeSec,  
    SUM(WaitTimeSec) * 100.0 / SUM(SUM(WaitTimeSec)) OVER () AS Yüzde  
FROM Waits  
GROUP BY wait_type  
ORDER BY SUM(WaitTimeSec) DESC;

Wait Type: SQL Server’da bir iş parçacığı (thread), CPU, bellek, disk gibi kaynakları kullanmaya çalışırken belirli nedenlerle bekleyebilir. Wait Type, bu bekleme nedenini gösterir.

WaitTimeSec :İlgili bekleme türü için SQL Server’ın toplam beklediği süredir. Ölçüm saniye cinsindendir.

SignalWaitTimeSec :SQL Server’daki iş parçacığının bir kaynağa erişme izni aldıktan sonra CPU’ya atanana kadar geçen süredir. Yüksek Signal Wait Time, CPU darboğazına işaret edebilir. CPU İçin Bekleme Süresidir.

Yüzde (%): Belirli bir wait type’ın toplam bekleme süresi içindeki yüzdesidir. Sistemdeki performans darboğazlarını anlamak için önemlidir.

Wait Type Açıklamaları:

1. VDI_CLIENT_OTHER
SQL Server’ın Virtual Device Interface (VDI) kullanarak yedekleme veya restore yaparken beklediği süreyi gösterir.

2. CXCONSUMER
SQL Server paralel iş parçacıkları arasında veri iletişimi sağlarken oluşur. Genellikle parallelism (paralellik) ile ilgilidir.

3. BROKER_TASK_STOP
Service Broker iş parçacıkları durduğunda beklenen süredir. Service Broker kullanan sistemlerde daha yaygındır.

4. HADR_WORK_QUEUE
AlwaysOn Availability Groups (HADR High Availability Disaster Recovery) için görev kuyruğunda bekleyen iş parçacıklarının süresidir.

5. CXPACKET
Paralel sorgular çalışırken iş parçacıkları arasında bekleme süresidir. Paralel sorguların dengesiz dağılması (skewed parallelism) bu beklemeyi artırabilir.

6. HADR_LOGCAPTURE_WAIT
AlwaysOn Availability Groups’ta log capture (yakalama) işleminin bekleme süresini gösterir.

7. SOS_SCHEDULER_YIELD
SQL Server iş parçacığının CPU zamanı aldıktan sonra CPU’yu başka bir iş parçacığına devretmesini gösterir. Yüksek değerler CPU darboğazına işaret edebilir.

8. HADR_SYNC_COMMIT
AlwaysOn synchronous commit (eşzamanlı işlem tamamlama) sırasında oluşan bekleme süresidir.

9. LAZYWRITER_SLEEP
SQL Server’ın Lazy Writer işlemi bellekteki eski veri sayfalarını temizlerken oluşan bekleme süresidir.

10. LOGMGR_QUEUE
Transaction log yönetimi sırasında beklenen süredir.

11. SLEEP_TASK
SQL Server iş parçacığının belirli bir süre uyku moduna geçmesini gösterir.
12. RESOURCE_SEMAPHORE

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 yeterliliğini kontrol eden scriptleri 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