MSSQL Server'da En çok Memory Tüketen Sorgular

SQL Server’da performans sorunlarının en büyük iki kaynağından biri CPU, diğeri ise Bellek (Memory) kullanımıdır. Bir sorgu çalıştırıldığında, SQL Server bu sorgunun düzgün işlemesi (sıralama – sorting, birleştirme – joining gibi işlemler) için belirli bir miktar bellek rezervasyonu yapar. Buna “Memory Grant” denir.

Sql server üzerinde en çok memory kullanan sorguları göstermektedir.

SELECT
    mg.session_id
    ,t.text AS [sql]
    ,qp.query_plan AS [plan]
    ,mg.is_small  /* Resource Semaphore Queue information */
    ,mg.dop
    ,mg.query_cost
    ,mg.request_time
    ,mg.grant_time
    ,mg.wait_time_ms
    ,mg.required_memory_kb
    ,mg.requested_memory_kb
    ,mg.granted_memory_kb
    ,mg.used_memory_kb
    ,mg.max_used_memory_kb
    ,mg.ideal_memory_kb
FROM
    sys.dm_exec_query_memory_grants mg WITH (NOLOCK)
        CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) t
        CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
--WHERE -- Uncomment to see only pending memory grants
--    mg.grant_time IS NULL
ORDER BY
    mg.granted_memory_kb DESC
OPTION (RECOMPILE, MAXDOP 1);

Aşağıdaki resimde görüldüğü gibi sorgumuz ve aynı zamanda execution plan yapısı görülmektedir.

granted_memory_kb SQL Server’ın bu sorguya fiilen ayırdığı RAM miktarı.
requested_memory_kb Sorgunun çalışmak için talep ettiği ideal miktar.
used_memory_kb Tahsis edilen belleğin ne kadarının o an aktif kullanıldığı. (Fark büyükse sorgu verimsizdir).
query_cost Sorgunun karmaşıklık puanı. Genelde maliyet arttıkça bellek ihtiyacı artır.
wait_time_ms Sorgu, bellek alabilmek için ne kadar süredir sırada bekliyor?
is_small Sorgunun küçük bir bellek havuzunda çalışıp çalışmadığını belirtir.

Yukarıda herhangi bir kolonun

required_memory_kb (66.624 KB): Sorgunun çalışmaya başlayabilmesi için ihtiyaç duyduğu minimum bellek miktarıdır. Bu miktar yaklaşık 65 MB’dır. Bu miktar olmadan sorgu asla çalışmaya başlamaz.
requested_memory_kb (18.480.064 KB): SQL Server’ın istatistiklere ve sorgu planına bakarak “bu sorgu en performanslı şekilde yaklaşık 17.6 GB RAM ile çalışır” dediği talep edilen miktardır.
granted_memory_kb (18.480.064 KB): İşte krizin çıktığı nokta burası. SQL Server, talep edilen 17.6 GB RAM’i bu sorguya fiilen tahsis etmiş (grant). Şu an bu bellek bu sorgu için rezerve edildi.
used_memory_kb (664.664 KB): Sorgunun o anki gerçek bellek kullanımı. Sadece 649 MB kullanıyor.
max_used_memory_kb (664.664 KB): Sorgunun ömrü boyunca şu ana kadar ulaştığı en yüksek bellek noktası. Yani hala 1 GB bile değil.
ideal_memory_kb (18.480.064 KB): Tüm verileri belleğe sığdırmak için gereken (hashing/sorting için) en ideal miktar tahmini.

Burada bir İstatistik (Statistics) Yanılgısı veya Kötü Sorgu Planı söz konusu.

Tahmin Hatası: SQL Server, tablodaki verilere (istatistiklere) bakarak milyarlarca satırı sıralayacağını veya birleştireceğini sanmış. Bu yüzden sorguya “Sana 17.6 GB yer ayırdım, rahat rahat çalış” demiş.
Büyük İsraf: Sorgu gerçekte sadece ~650 MB veri işliyor. Yani sistemden 17.6 GB RAM çalmış ama bunun sadece %4’ünü kullanıyor. Geri kalan yaklaşık 17 GB RAM tamamen boşta yatıyor ancak başka hiçbir sorgu bu boş alanı kullanamıyor.
Zincirleme Etki: Eğer sunucunda 32 GB RAM varsa, bu sorgudan sadece 2 tane aynı anda çalışsa, sunucudaki tüm bellek tükenir. 3. bir sorgu gelirse, o sorgu küçücük bir işlem yapacak olsa bile RAM bulamadığı için RESOURCE_SEMAPHORE beklemesine girer ve sistem kilitlenmiş gibi görünür.

Çözüm Önerileri

İstatistik Güncelleme: UPDATE STATISTICS TabloAdi komutuyla ilgili tabloların verilerini güncelleyin. Tahminler düzelirse requested_memory_kb dramatik şekilde düşecektir.
MAX_GRANT_PERCENT: Sorguya özel bir HINT vererek bellek tahsisini %10 gibi bir oranla sınırlayabilirsiniz.
Eksik İndeksler: Genellikle bu kadar yüksek bellek talebi, büyük tabloların RAM üzerinde “Sort” (Sıralama) veya “Hash Match” (Eşleştirme) yapmasından kaynaklanır. Uygun bir indeks bu işlemin RAM ihtiyacını sıfıra indirebilir.

Eğer sunucuda sorgular çalışmaya başlamıyor ve bekleme tipi (wait type) olarak RESOURCE_SEMAPHORE görünüyorsa, bu sorgu hayat kurtarır. Bu durum, bir veya birkaç sorgunun RAM’in büyük kısmını rezerve ettiğini ve diğerlerine yer kalmadığını gösterir.

Bir sorguda ORDER BY veya DISTINCT kullanıldığında, SQL Server verileri bellekte sıralar. Eğer indeksleme eksikse, SQL Server devasa miktarda bellek talep eder. Bu sorgu ile bu verimsiz planları yakalayabilirsiniz.

Bazen SQL Server, bir sorgunun 10 satır döndüreceğini tahmin ederken aslında 1 milyon satır döner. Ya da tam tersi olur ve gereksiz yere GB’larca RAM talep eder. ideal_memory_kb ile granted_memory_kb arasındaki uçurumlar, istatistiklerin güncellenmesi gerektiğine işarettir.

Yukarıdaki kodun içindeki şu satırın yorumunu kaldırırsan:

WHERE mg.grant_time IS NULL

Henüz bellek alamadığı için park edilmiş (suspended) olan sorguları görürsün. Eğer burada çok fazla satır varsa, sunucunuzda ciddi bir bellek yetersizliği veya bir “sorgu darboğazı” var demektir.

Bu sorgu, anlık bellek tüketimini izlemek ve sistemi yavaşlatan “obur” sorguları teşhis etmek için mükemmeldir. Bellek tahsislerini düzenli izlemek, sunucunun disk (paging) yerine RAM üzerinde yüksek performansla çalışmasını sağlar.

Başka makalede görüşmek dileğiyle..

Kötülüğe iyilikle karşılık verin.Fussilet-34

MSSQL Server’da En çok Memory Tüketen Sorgular

Bir yanıt yazın