SQL Server Eğitimi: Memory Grant Sorunlarını Ölçme ve Düzeltme

SQL Server eğitimi: Memory grant darboğazını doğru tanılamak

Bir sorgu Hash Match veya Sort operatörüne gelmeden önce optimizer, tahmini satır sayısı, satır genişliği ve operatörün çalışma modelinden bir bellek hibesi hesaplar. Bu hibe, buffer pool belleği değil sorgu çalışma alanıdır. Tahmin fazla yüksekse sorgu kullanılmayan belleği rezerve eder ve eşzamanlı sorgular RESOURCE_SEMAPHORE beklemeye başlar. Tahmin düşükse operatör belleğe sığmaz, çalışma dosyalarını tempdb'ye yazar ve actual execution plan içinde SpillLevel uyarısı oluşur. Aktif baskıyı anlık görmek için aşağıdaki DMV sorgusunu 1 saniyelik örneklerle çalıştırın; yalnızca geçmişe bakarak sorunu teşhis etmeye çalışmayın çünkü sys.dm_exec_query_memory_grants tamamlanmış sorguları tutmaz.

SELECT
    mg.session_id,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    mg.used_memory_kb,
    mg.wait_time_ms,
    r.status,
    r.wait_type,
    DB_NAME(r.database_id) AS database_name,
    SUBSTRING(t.text,
              (r.statement_start_offset / 2) + 1,
              ((CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(t.text)
                    ELSE r.statement_end_offset
                END - r.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_memory_grants AS mg
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = mg.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY mg.requested_memory_kb DESC;

Bu sorguda requested_memory_kb ile used_memory_kb arasındaki farkı kaydedin. Örneğin 524288 KB isteyen fakat 18000 KB kullanan bir sorgu tek başına yavaş görünmeyebilir; ancak aynı anda 20 benzer istek geldiğinde grant havuzunu tüketir. Tersine, granted_memory_kb değerinin required_memory_kb değerine yakın olması ve planda Sort veya Hash Warning görülmesi, sorgunun minimum hibeyle başladığını ve tempdb spill ürettiğini gösterir. Uygulama tarafındaki salt süre metriği bu iki durumu ayırt edemez.

Bir veritabanı eğitimi veya sql eğitimi kapsamında sık yapılan hata, yalnızca CPU yüzdesine bakmaktır. CPU düşükken RESOURCE_SEMAPHORE bekleyen oturumlar bulunabilir; çünkü bekleyen sorgu henüz CPU üzerinde çalışmaya başlamamıştır. NoSQL eğitimi sırasında MongoDB tarafında kullanılan executionStats yaklaşımına benzer biçimde, SQL Server'da da tahmin edilen ve kullanılan kaynakları aynı yürütme bağlamında incelemek gerekir: MongoDB'de explain('executionStats'), SQL Server'da ise actual plan ile MemoryGrantInfo birlikte okunmalıdır.

Veritabanı performans ayarlama: Önce-sonra ölçüm düzeneği kurmak

Üretimde her sorgu için actual plan açmak yerine Extended Events ile bellek hibesi olaylarını dosyaya alın. sqlserver.query_memory_grant_usage olayı istenen, verilen ve kullanılan hibeyi; sqlserver.query_memory_grant_blocking ise bir sorgunun hibe havuzu nedeniyle engellendiğini yakalar. Olay dosyasını ayrı ve yeterli boş alanı olan bir diske yazın. Bu oturumu açmak için ALTER ANY EVENT SESSION izni gerekir.

CREATE EVENT SESSION [MemoryGrantWatch] ON SERVER
ADD EVENT sqlserver.query_memory_grant_usage
(
    ACTION
    (
        sqlserver.database_id,
        sqlserver.session_id,
        sqlserver.sql_text
    )
),
ADD EVENT sqlserver.query_memory_grant_blocking
(
    ACTION
    (
        sqlserver.database_id,
        sqlserver.session_id,
        sqlserver.sql_text
    )
)
ADD TARGET package0.event_file
(
    SET filename = N'D:\\XEvents\\MemoryGrantWatch.xel',
        max_file_size = (100),
        max_rollover_files = (4)
);
GO
ALTER EVENT SESSION [MemoryGrantWatch] ON SERVER STATE = START;
GO

Değişiklikten önce ve sonra aynı parametrelerle en az 30 yürütme alın; ilk yürütmeyi plan ve veri önbelleği ısınma etkisi nedeniyle ayrı işaretleyin. Karşılaştırma tablonuzda p50 ve p95 süre, logical reads, GrantedMemory, MaxUsedMemory, spill sayısı ve RESOURCE_SEMAPHORE bekleme sayısı bulunmalıdır. Test ortamında aşağıdaki ayarlarla plan ve I/O verisini yakalayabilirsiniz. Üretimde DBCC DROPCLEANBUFFERS veya DBCC FREEPROCCACHE ile ortamı yapay olarak soğutmak, diğer iş yüklerini bozduğu için ölçüm yöntemi değildir.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;
GO

DECLARE @TenantId int = 42;
DECLARE @From datetime2(0) = '2026-08-01 00:00:00';

SELECT o.OrderId, o.Status, o.Total, o.CreatedAt
FROM dbo.Orders AS o
WHERE o.TenantId = @TenantId
  AND o.CreatedAt >= @From
ORDER BY o.CreatedAt DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;
GO

SET STATISTICS XML OFF;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Actual plan XML'de Sort operatörünü ve MemoryGrantInfo düğümünü inceleyin. GrantedMemory yüksek fakat MaxUsedMemory düşükse fazla hibe vardır. Sort altında SpillLevel görülüyorsa hibe yetersizdir. Sadece estimated plan kullanmak bu ayrımı yapmaz; estimated plan, runtime'da verilen veya fiilen kullanılan bellek miktarını içermez.

Database indexleme ile Sort operatörünü plandan çıkarmak

Sayfalama sorgularında yaygın bir anti-pattern, TenantId filtresinden sonra binlerce satırı Sort edip ilk 100 satırı döndürmektir. Sorgu mantıksal olarak yalnızca 100 satır istese de uygun sıralı erişim yolu yoksa SQL Server ara kümenin tamamını sıralamak için grant ister. TenantId eşitlik filtresi ve CreatedAt sıralaması için bileşik indeks, motorun doğrudan sıralı index seek veya range scan ile ilk 100 satıra ulaşmasını sağlar. Bu, memory grant'i bir hint ile bastırmaktan daha güvenlidir; çünkü bellek ihtiyacını üreten fiziksel Sort operatörünü kaldırır.

CREATE INDEX IX_Orders_TenantId_CreatedAt
ON dbo.Orders (TenantId, CreatedAt DESC)
INCLUDE (OrderId, Status, Total)
WITH (SORT_IN_TEMPDB = ON, ONLINE = ON);
GO

Bu database indexleme örneğinde INCLUDE listesi sorgunun select listesini karşılar ve key lookup ihtiyacını azaltır. Ancak ONLINE = ON her kurulumda aynı lisans, sürüm veya veri tipi koşullarında desteklenmeyebilir; bakım penceresi öncesinde hedef ortamda doğrulayın. Ayrıca çevrimiçi indeks işlemi bile kısa süreli schema lock alabilir. İndeks oluşturduktan sonra aynı 30 yürütmelik testte planın Sort yerine IX_Orders_TenantId_CreatedAt erişimi kullandığını, GrantedMemory değerinin düştüğünü ve logical reads değerinin kabul edilebilir kaldığını doğrulayın.

İncelik şudur: CreatedAt tek başına indekslenirse optimizer TenantId filtresini her zaman verimli uygulayamaz ve çok kiracılı tabloda geniş bir tarih aralığını tarayabilir. Anahtar sırası burada sorgu yüklemiyle belirlenir: önce eşitlik koşulu olan TenantId, sonra range ve ORDER BY kolonu olan CreatedAt gelir. Yazma maliyetini de ölçün; sys.dm_db_index_usage_stats içindeki user_updates ve user_seeks değerleri indeksin iş yüküne değip değmediğini gösterir, fakat bu DMV'nin SQL Server yeniden başlatıldığında sayaçlarını sıfırladığını unutmayın.

Veritabanı optimizasyonu: Feedback, plan önbelleği ve güvenli sınırlar

Memory grant feedback, tekrar kullanılan bir planın önceki çalışmadaki fiili bellek tüketimine bakarak sonraki hibeyi ayarlayabilir. Bu mekanizma kötü fiziksel planı otomatik olarak iyi plana dönüştürmez: büyük bir Sort hala büyük bir Sort'tur. Ayrıca test sorgusuna OPTION (RECOMPILE) eklemek her seferinde yeni derleme ürettiği için geri bildirimin aynı plan üzerinde birikmesini engeller. Feedback davranışını değerlendirirken plan önbelleğini kasıtlı temizlemek yerine aynı parametre kalıbıyla tekrar eden uygulama trafiğini veya kontrollü yük testini kullanın.

Parametre dağılımı çok dengesizse tek bir grant değeri her kiracı için doğru olmayabilir. Örneğin TenantId 42 için 100 satır, TenantId 7 için 20 milyon satır dönüyorsa küçük kiracıdan öğrenilen düşük grant büyük kiracıda spill üretebilir. Bu durumda önce gerçek dağılımı ölçün: dbo.Orders üzerinde TenantId ve CreatedAt istatistiklerinin güncel olup olmadığını kontrol edin, ardından hedefli güncelleme yapın. FULLSCAN pahalı olabileceğinden bunu körlemesine zamanlanmış iş haline getirmeyin; önce sampled statistics ile actual row count farkını ölçün.

SELECT
    s.name AS statistics_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY sp.modification_counter DESC;
GO

UPDATE STATISTICS dbo.Orders IX_Orders_TenantId_CreatedAt WITH RESAMPLE;
GO

Query Store açıksa, değişiklikten sonra sorgu kimliği bazında plan sayısını, yürütme sayısını, ortalama süreyi ve maksimum kullanılan belleği karşılaştırın. Aşağıdaki sorgu, aynı SQL metninin beklenmedik şekilde çok sayıda plan üretip üretmediğini görünür kılar. Çok plan görmek tek başına hata değildir; fakat plan başına bellek tüketimi dramatik değişiyorsa parametre dağılımı, istatistik ve indeks erişim yolunu birlikte incelemek gerekir.

SELECT
    q.query_id,
    p.plan_id,
    rs.count_executions,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.avg_query_max_used_memory,
    qt.query_sql_text
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
WHERE qt.query_sql_text LIKE N'%FROM dbo.Orders%'
ORDER BY rs.avg_duration DESC;

Sık Sorulan Sorular

Veritabanı optimizasyonu sırasında RESOURCE_SEMAPHORE beklemesi nasıl analiz edilir?

Önce sys.dm_exec_query_memory_grants ile requested_memory_kb, granted_memory_kb ve wait_time_ms değerlerini örnekleyin. Eşzamanlı olarak sys.dm_exec_requests.wait_type alanını kaydedin. Ardından query_memory_grant_blocking Extended Event olayındaki sql_text ile en büyük hibeyi isteyen sorguyu eşleştirin. Sorun CPU değil grant havuzu ise CPU grafiği düşük kalırken bekleyen oturum sayısı artar.

Database indexleme memory grant miktarını gerçekten azaltır mı?

Yalnızca indeks, Sort veya Hash gibi bellek isteyen operatörü kaldırıyor ya da giriş satırlarını ciddi biçimde azaltıyorsa azaltır. Örneğin WHERE TenantId = @id ORDER BY CreatedAt DESC sorgusunda (TenantId, CreatedAt DESC) indeksi sıralı erişim sağlar ve Sort ortadan kalkabilir. Önce-sonra actual plan içindeki GrantedMemory, MaxUsedMemory ve Sort SpillLevel alanlarını karşılaştırmadan sonucu kabul etmeyin.

SQL eğitimi ve SQL Server eğitimi kapsamında memory grant feedback ne zaman güvenilmez olur?

OPTION (RECOMPILE) kullanılan sorgularda plan tekrar kullanılmadığı için feedback birikmez. Ayrıca parametre değerleri arasında satır sayısı çok değişiyorsa tek bir ayarlanmış grant bir değer için fazla, başka bir değer için yetersiz kalabilir. Query Store'da plan bazında avg_query_max_used_memory değerini ve actual plan satır tahminlerini tenant veya tarih aralığı gibi parametre sınıflarına göre inceleyin.

Veritabanı performans ayarlama için memory grant sorununda MAX_GRANT_PERCENT kullanmalı mıyım?

İlk adım olarak hayır. MAX_GRANT_PERCENT, büyük hibeyi sınırlayabilir ancak Hash veya Sort operatörünün spill üretmesine yol açabilir. Önce eksik sıralama indeksini, gereksiz geniş select listesini ve kardinalite hatasını düzeltin. Hint ancak XEvent ölçümünde belirli sorgunun grant havuzunu tükettiği kanıtlandıysa, kontrollü yük testi ve spill ölçümüyle değerlendirilmelidir.