Bu makalede mssql server üzerinde Memory bekleme tiplerini detaylı bir şekilde görmüş olacağız. İlk olarak çalıştığım sistemde en çok bekleme tiplerine sebep olan bekleme tiplerinden başlayacağız. Bunun için aşağıdaki komut kullanılır.
SELECT
wait_type,
wait_time_ms/1000 AS WaitTimeSec,
signal_wait_time_ms/1000 AS SignalWaitTimeSec,
(wait_time_ms*100.0)/SUM(wait_time_ms) OVER() AS Yüzde
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
ORDER BY Yüzde DESC;
- WaitTimeSec: Bu bekleme türünün toplam bekleme süresini saniye cinsinden gösterir. Yani SQL Server, ilgili kaynak için toplamda ne kadar beklemiş.
- SignalWaitTimeSec: İşlemcinin (CPU) kullanıma hazır olmasını beklerken geçen süredir. Yüksek değerler, CPU darboğazı olabileceğini gösterir.
- Yüzde: Toplam bekleme süresine göre bu bekleme türünün yüzdesel oranını gösterir. En çok hangi bekleme türünün sistemi yavaşlattığını anlamak için kullanılır.
Şimdi yukarıdaki bekleme tiplerini detaylı bir aşağıda katagorilere ayrılmış şekilde detaylı bir şekilde açıklayalım.
Ayrıca Session bazlı hangi sorguların bekleme tipleri olduğunu görmek için aşağıdaki komut kullanılır.
SELECT session_id, wait_type, wait_time/1000 as bekleme_Saniye, wait_resource
FROM sys.dm_exec_requests
WHERE wait_type <> 'WAITFOR'
SQL Server’daki bekleme türleri (wait types), veritabanı motorunun çeşitli kaynaklar için yaptığı beklemeleri izler ve yönetir. Bu beklemeler, SQL Server’ın işlemleri nasıl gerçekleştirdiğini, hangi kaynakların talep edildiğini ve hangi işlemlerin zaman alarak beklediğini anlamanızı sağlar. Bekleme türlerinin doğru analiz edilmesi, SQL Server’daki performans sorunlarının tespit edilmesi ve çözülmesi için kritik öneme sahiptir.
SQL Server’da Bekleme Türlerini Neden İzleriz?
- Performans Analizi: Bekleme türleri, hangi kaynakların darboğaz oluşturduğunu belirlemek için kullanılır. Eğer belirli bir kaynakta sürekli bekleme oluyorsa, bu kaynak üzerinde iyileştirme yapılması gerekebilir.
- Veritabanı Optimizasyonu: Bir işlem ya da sorgu belirli bir kaynağı bekliyorsa, bu kaynağın optimizasyonu (örneğin, disk I/O’su ya da bellek yönetimi) sorgunun daha hızlı çalışmasını sağlayabilir.
- Veritabanı Yönetimi: Bekleme türleri, veritabanı yöneticilerinin (DBA) sistemdeki kaynak kullanımını izlemesine ve veritabanı altyapısını optimize etmesine yardımcı olur.
SQL Server Bekleme Türlerinin Temel Kategorileri
SQL Server’daki bekleme türlerini birkaç ana kategoride toplayabiliriz:
- CPU ile ilgili beklemeler
- Disk I/O ile ilgili beklemeler
- Memory (bellek) ile ilgili beklemeler
- Network (ağ) ile ilgili beklemeler
- Task Scheduler ve Resource ile ilgili beklemeler
Memory ile ilgili kategoriyi detaylı olarak inceleyelim:
3. Memory (Bellek) ile İlgili Beklemeler
SQL Server, veritabanı işlemlerini hızlı bir şekilde işlemek için belleği kullanır, ancak eğer bellek yeterli değilse veya belleğe erişimle ilgili sorunlar varsa, bu beklemeler ortaya çıkabilir. Bellek kaynakları üzerinde yapılan beklemeler, performans problemlerine neden olabilir ve genellikle bellek yönetimi ve yetersiz bellek ile ilişkilidir.
SQL Server, belleği daha verimli kullanabilmek için farklı bellek yönetimi stratejileri uygular. Bellek ile ilgili beklemeler, bu yönetim stratejilerinin verimli çalışmadığı durumlarda ortaya çıkabilir.
Örnek Bekleme Türleri:
· RESOURCE_SEMAPHORE
RESOURCE_SEMAPHORE beklemesi, SQL Server, büyük sorgular (özellikle SORT, HASH JOIN, HASH AGGREGATE vs. kullananlar) için çalışma belleği (work memory) tahsis eder. Ancak bellek sınırlı olduğunda, bazı sorgular diğer sorguların bitmesini ya da bellek serbest kalmasını beklemek zorunda kalır. İşte bu bekleme durumu RESOURCE_SEMAPHORE olarak adlandırılır. SQL Server, sorgu yürütme sırasında her bir işlem için bellek ayırmak zorundadır. Eğer bellekte yeterli yer yoksa, SQL Server işlemi bekletir. Bu tür beklemeler, SQL Server’ın belirli bir işlemi çalıştırmak için bellek tahsis etmeye çalışırken ortaya çıkar. Bu bekleme türü genellikle SQL Server’ın, bir işlemi gerçekleştirmek için yeterli bellek kaynağı bulamadığı durumlarda görülür. Eğer bellekte yeterli yer yoksa veya bellek havuzunda sınırlı bellek varsa, işlemler bekletilir. Bu tür beklemeleri azaltmak için SQL Server’da bellek yapılandırmasını optimize edebilirsiniz. Ayrıca, bellek yönetimi ve bellek havuzu ayarlarını gözden geçirmek önemlidir. Bellek ekleme veya daha fazla bellek tahsisi de çözüm olabilir.
Nedenleri
- Büyük veri setleri üzerinde çalışan karmaşık sorgular
- Bellek yetersizliği
- Aynı anda çok fazla sorgu çalışması
- İyi ayarlanmamış bellek sınırları (max server memory, query memory grant settings)
Yukarıdaki ayarların gözden geçirilmesi gerekmektedir.
· RESOURCE_SEMAPHORE_QUERY_COMPILE
RESOURCE_SEMAPHORE_QUERY_COMPILE, SQL Server’ın sorgu derleme sırasında bellek kaynaklarını almak için beklediği bir durumdur. Sorgular çalıştırılmadan önce SQL Server, sorgu planlarını oluştururken belirli bir miktarda bellek kullanır. Eğer bellek yeterli değilse, bu bekleme türü devreye girer. Bu bekleme türü genellikle çok karmaşık sorgular veya çok sayıda işlem sırasında görülür. SQL Server, sorgu derleme sırasında gerekli olan bellek kaynaklarını tahsis edemediğinde bu bekleme türü meydana gelir. Bu tür beklemeleri minimize etmek için sorgu karmaşıklığını azaltmak, gereksiz karmaşık işlemlerden kaçınmak ve sorgu optimizasyonu yapmak faydalı olacaktır. Ayrıca, bellek tahsisatını artırmak da çözüm olabilir.
Kısaca, SQL Server’da bir sorgu derlenirken (yani execution plan hazırlanırken) yeterli derleme belleği (compile memory) tahsis edilemediği için beklemesi gereken durumlarda ortaya çıkar.
Yani özetle:
“Sorgu derlenmek istiyor ama derleme işlemini yapmak için yeterli bellek yok, o yüzden sıraya alınıyor.”
Ne zaman görülür?
- Sistemde çok fazla eşzamanlı sorgu derleniyorsa
- Karmaşık ve bellek yoğun sorgular (özellikle dinamik SQL, çok büyük JOIN’ler)
- Plan cache sık sık temizleniyorsa (örneğin RECONFIGURE, DBCC FREEPROCCACHE, sp_recompile)
- SQL Server’da bellek baskısı varsa
Çözüm Önerileri:
- Sorguları yeniden yazmak: Karmaşık sorguları daha basit hale getirmek.
- Parameter Sniffing sorunlarına dikkat etmek.
- Plan cache’in çok sık temizlenmediğinden emin olmak.
- Bellek ayarlarını gözden geçirmek (max server memory, resource governor varsa)
- Query Store aktifse plan sabitlemeyi (plan forcing) düşünmek.
- Optimize for ad-hoc workloads opsiyonunu kontrol etmek: İlgili makale
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;· MEMORY_ALLOCATION_EXT
MEMORY_ALLOCATION_EXT beklemesi, SQL Server’ın ek bellek tahsisi yapmak için beklediği durumu ifade eder. SQL Server, büyük veri işlemleri için ekstra bellek kaynakları ayırmaya çalışırken bu tür beklemeler meydana gelebilir. Bu bekleme türü, bellek yönetiminin optimize edilmediği veya bellek kaynaklarının tükenmeye yakın olduğu durumlarda ortaya çıkar. Bu bekleme türü, SQL Server’ın işlemleri için ek bellek tahsis etmek istediği, ancak bu kaynakları alamadığı durumlarda meydana gelir. Bellek eklemek, bellek havuzunun düzenli olarak izlenmesi ve bellek kullanımını izlemek bu tür beklemeleri minimize edebilir. Ayrıca, bellek yöneticisi yapılandırmalarının gözden geçirilmesi ve optimize edilmesi önemlidir.
SELECT
wait_type,
wait_time_ms AS wait_time,
waiting_tasks_count
FROM
sys.dm_os_wait_stats
WHERE
wait_type LIKE 'MEMORY_ALLOCATION_EXT'Bu, doğrudan sorgulara değil; SQL Server’ın aşağıdaki gibi sistemsel bileşenlerine ait olabilir:
- Buffer pool yönetimi
- Lock yönetimi
- TempDB kullanımı
- Plan cache işlemleri
- Metadata tahsisi
Eğer bu bekleme türü çok fazla gözüküyorsa ve performans sorunları varsa:
- Genelde altta yatan bir memory pressure ya da sistemsel dengesizlik vardır.
- Ama bu wait type tek başına bir sorun değildir, genellikle belirti (symptom) gibidir.
Not: Memory pressure (bellek baskısı), SQL Server’ın çalışmak için yeterli bellek bulamaması durumudur. Yani SQL Server’ın RAM ihtiyacı artmış ama sistem buna yetişemiyor.
• CMEMTREAD
Sql serverda bellek içerisindeki bazı kritik bölümlere birden fazla tread ulaşmaya çalışınca bu bekleme tipi görülmektedir. Çünkü her memory objectinin bir gate(kapı)’i vardır. Sorgu sayısı çok olursa gate’ler kilitlenir. Treadler beklemektedir. Bu yapıda cpu kullanımının artmasına sebep verir. Bu bekleme türünün sebebi aynı anda çok fazla ad hoc sorgunun olması ikinci ifade ise parametreleşmenin olmamasıdır.
· MEMORY_GRANT_PENDING
MEMORY_GRANT_PENDING beklemesi, SQL Server’ın sorgu çalıştırmadan önce gerekli olan bellek kaynağını henüz almadığı durumları ifade eder. Bu bekleme türü, SQL Server’ın işlemi gerçekleştirmek için gerekli olan bellek kaynağını almak için bellek yönetimini tamamlamaya çalıştığı zaman meydana gelir. Bu bekleme türü, SQL Server bir işlem için gerekli olan bellek tahsisatını tamamlamadan işlemi başlatmaya çalıştığında görülür. Genellikle büyük sorgular veya işlemler sırasında, yeterli bellek kaynağı ayarlanamadığında oluşur. Bu tür beklemelerin önlenmesi için bellek havuzlarını genişletmek, bellek tahsisatını izlemek ve sorgu planlarını optimize etmek faydalı olabilir. Ayrıca, sorguların daha küçük parçalar halinde çalıştırılması da bu tür beklemeleri azaltabilir.
SELECT
wait_type,
wait_time_ms AS wait_time,
waiting_tasks_count
FROM
sys.dm_os_wait_stats
WHERE
wait_type LIKE 'MEMORY_GRANT_PENDING'Kısacası:
MEMORY_GRANT_PENDING, bir sorgunun çalışmaya başlamadan önce, ihtiyaç duyduğu query execution memory’yi alamadığı için beklemede olduğunu gösterir.
Bu, sorgunun henüz başlamadığı, çünkü belleğin hala verilmediği (grant edilmediği) anlamına gelir.
Ne Yapabilirsin?
- Sorguları optimize et
Özellikle ORDER BY, JOIN, GROUP BY, OVER() içeren sorgulara dikkat et. - Missing Index var mı kontrol et
Execution planlarda yüksek memory grant alanlar genelde index eksikliğinden dolayı olur. - Query hint kullan (gerekiyorsa)
Örneğin OPTION (HASH JOIN) yerine MERGE JOIN gibi bir strateji seçmek belleği azaltabilir. - Server memory ayarlarını kontrol et
Max server memory çok düşükse artırmayı düşün. - Resource Governor kullanıyorsan ayarlarını gözden geçir
Belki bazı sorgular gereksiz kısıtlamalara tabi tutuluyor olabilir.
Örnek Durum
Diyelim ki bir raporlama uygulaması aynı anda 10 büyük sorgu gönderiyor. Her biri çalışmak için 500 MB memory grant istiyor. Ama toplamda sadece 2 GB memory grant alanı var. Bu durumda sadece 4 sorgu aynı anda çalışabilir, diğer 6 tanesi MEMORY_GRANT_PENDING durumunda bekler.
· LAZYWRITER_SLEEP
LAZYWRITER_SLEEP beklemesi, SQL Server’ın Lazy Writer işlemi ile ilgilidir. Lazy Writer, bellekteki kullanılmayan veri sayfalarını temizleyerek boş alan yaratmak için çalışır. Bu bekleme türü, SQL Server’ın bellek alanı yönetimi sırasında görülebilir. Bellekte yeterli alan varsa, Lazy Writer bekler. Lazy Writer, SQL Server’ın belleği daha verimli kullanmak amacıyla sayfa temizlik işlemi yaparken bekler. Eğer bellek yeterliyse ve sayfa temizleme işlemi yapılması gerekmiyorsa, bu bekleme türü görülür. Bu beklemeler genellikle performans sorunlarına yol açmaz. Ancak, bellek kapasitesinin izlenmesi ve gerektiğinde artırılması, bellekle ilgili daha verimli işlem yapılmasına yardımcı olabilir.
Kısaca Özetle:
| Durum | Anlamı |
|---|---|
| LAZYWRITER_SLEEP çoksa | Bellek yönetimi iyi çalışıyor |
| LAZYWRITER_SLEEP hiç yoksa | Bellek baskısı olabilir, dikkat |
· PAGELATCH_xx
PAGELATCH_XX beklemeleri, SQL Server içindeki veri sayfalarına erişim sırasında, bu sayfaların latch (kilit) ile korunduğu durumlarda ortaya çıkar.
Bu beklemeler, I/O (disk) beklemesi DEĞİLDİR. Veri zaten bellekte, ama sayfa üzerinde eşzamanlı işlem yapılmasını engellemek için SQL Server geçici olarak sayfayı kilitler.
PAGELATCH vs. PAGEIOLATCH
| Wait Type | Açıklama |
|---|---|
| PAGEIOLATCH_XX | Disk’ten veri okunmasını bekliyor (I/O) |
| PAGELATCH_XX | Bellekteki bir sayfa üzerinde latch bekliyor (RAM) |
Latch Kavramı
Buffer pool, işletim sisteminin SQL server için hafızada(memory) rezerve ettiği bir alandır. SQL Server gelen taleplere göre diskten ilgili page’leri memory de buffer pool’a atar. Bu sistem içerisinde, verinin buffer pool içerisinde tutarlılığını korumak için bir sisteme ihtiyaç vardır. Latch, SQL Server’in memory içindeki bu verinin tutarlılığını sağladığı bir lock mekanizmasıdır. Paylaşılan hafıza kaynaklarını korumaya yarar. Bir thread bir page üzerinde çalışmayı talep ettiğinde, aynı anda bir page’i sadece bir thread değiştirebileceğinden bu latch üzerinde “latch queue” denilen kuyruk oluşur. Bu page taleplerini dagıtmak amacıyla tempdb data file sayısının, işlemci çekirdek sayısıyla orantılı olarak artırılması önerilir. Çünkü her bir data file için ayrı bir “latch queue” vardır. Bu data file sayısını artırma işlemi thread’in page’i bekleme süresini azaltır. Bu da sorgu performansının artması demektir.
XX ne demek? Bu, beklemenin ne tür bir sayfa için olduğunu gösterir:
| Wait Type | Açıklama |
|---|---|
| PAGELATCH_SH | Shared latch (okuma) |
| PAGELATCH_EX | Exclusive latch (yazma) |
| PAGELATCH_UP | Update latch (geçici kilit) |
En yaygın nedenler:
- TempDB contention
- Çok sayıda eşzamanlı işlem TempDB’de geçici objeler (örneğin #temp tables) oluşturuyorsa.
- Özellikle Page ID = 2 (allocation pages: PFS, GAM, SGAM) için.
- Yoğun insert/update işlemleri
- Aynı tablonun aynı sayfasına birden fazla session aynı anda yazmak istiyorsa.
- Identity insert bottleneck
- Identity alanlı tabloya tek bir noktadan insert olurken yazma beklemeleri yaşanabilir.
Tempdb contetion ile ilgili detaylı bilgi almak için ilgili makale okunabilir.
Hangi sayfada olduğunu anlamak için:
SELECT
session_id,
wait_type,
resource_description -- İçinde "dbid:fileid:pageid" bilgisi var
FROM sys.dm_exec_requests
WHERE wait_type LIKE 'PAGELATCH%';resource_description çıktısı örneğin şöyle görünür: 2:1:123 → Database ID 2, file ID 1, page ID 123
TempDB Contention Varsa Ne Yapmalı?
- TempDB dosya sayısını artır (genelde CPU sayısı kadar önerilir)
- Örnek: 8 CPU varsa → 8 TempDB 7 .ndf dosyası-1 .mdf dosyası
- Trace flag 1118 ve 1117 (eski sürümler için)
- Artık SQL Server 2016+ sürümlerinde varsayılan davranış haline geldi
- Sorguları gözden geçir
- Gereksiz temp table kullanımı var mı?
- TVF, CTE, SELECT INTO, vs. aşırı kullanılıyor mu?
Özetle:
| Durum | Anlamı | Aksiyon |
|---|---|---|
| PAGELATCH_EX | Bellekte yazma sırasında latch bekliyor | Insert yoğunluğu kontrol et |
| PAGELATCH_SH | Okuma sırasında bekleme | Aşırı paralel okuma/sorgu olabilir |
| PAGEIOLATCH_XX | Disk’ten veri yükleniyor (I/O beklemesi) | I/O performansını artır |
| PAGELATCH_XX (özellikle TempDB) | TempDB contention olabilir | TempDB dosyalarını çoğalt |
Makalenin başında belirtilen diğer bekleme tiplerine değinelim.
1. HADR_LOGCAPTURE_WAIT
AlwaysOn Kullanılabilirlik Grupları’nda log kayıtlarının yakalanması sırasında oluşan bekleme süresidir. Log yakalama işlemi yavaşsa, bu bekleme türü artar. Disk performansını kontrol etmek faydalı olabilir. primary replica’daki log capture thread’in yeni log verisi gelmesini beklediğini ifade eder.
Yani şu an transaction log’ta yeni bir şey yok, SQL Server bekliyor:
“Yeni bir şey oldu mu? Göndereyim mi?”
Genelde ne zaman olur?
- Primary server’da çok az transaction varsa
- Ya da sistem idle durumdaysa
Normal mi?
Bu tamamen beklenen ve masum bir bekleme türüdür. Sadece “log değişmediği için bekliyorum” anlamına gelir.
2. HADR_SYNC_COMMIT
HADR_SYNC_COMMIT, bir transaction’ın primary replica’da commit olmadan önce, secondary replica’dan “sync tamam” onayı gelmesini beklediğini gösterir.
Eğer Availability Group “SYNCHRONOUS” modda çalışıyorsa, her commit, secondary’den onay gelene kadar bekler.
Bu gecikme ne kadar uzarsa, uygulama o kadar yavaş hissettirir.
Ne zaman sorun olur?
- Secondary sunucu yavaşsa
- Network latency yüksekse
- Secondary çok yük altındaysa
- Disk yavaşsa → log disk’ine sync yazılamıyorsa
Uygulama ne hisseder?
Sorgu çalışır ama commit aşamasında takılır gibi olur.
Kritik transactional sistemlerde ciddi yavaşlamalar yaratabilir.
3. LAZYWRITER_SLEEP
Lazy Writer’ın belleği temizlemesi sırasında oluşan bekleme süresidir. Bellek baskısı varsa, bu bekleme türü artabilir. Yüksek olması iyidir.
Bu makalede MSSQL Server Memory Bekleme Tiplerini detaylı bir şekilde görmüş olduk. Başka bir makalede görüşmek dileğiyle..
“Allah içinizden iman edenlerin ve kendilerine ilim verilenlerin derecelerini yükseltir.” Mücâdele – 11
