SQL Server’da bir sorgu ilk kez çalıştırıldığında, SQL motoru bu sorgu için en uygun yolu hesaplar ve bu “planı” gelecekte tekrar kullanmak üzere Plan Cache adı verilen bellek alanında saklar. Ancak her plan verimli değildir. Veritabanı performansını optimize etmek için cache üzerindeki planları incelemek ve en çok kaynak tüketen veya en sık çalışan sorguları analiz etmek kritik bir adımdır.
Bu makalede, Plan Cache üzerindeki execution planlara (sorgu planlarına) nasıl erişeceğimizi ve bu verileri nasıl anlamlandıracağımızı inceleyeceğiz.
Grafiksel arayüz her zaman yeterli olmayabilir. DMVs (Dynamic Management Views) kullanarak plan cache’e doğrudan sorgu atmak bize şu avantajları sağlar:
- Hangi sorgunun kaç kez çalıştırıldığını (usecounts) görmek.
- Bellekte gereksiz yer kaplayan “Ad-hoc” sorguları tespit etmek.
- Belirli bir veritabanına ait tüm aktif planları tek bir tabloda listelemek.
Aşağıdaki sorgu; sys.dm_exec_cached_plans, sys.dm_exec_sql_text ve sys.dm_exec_query_plan yapılarını birleştirerek, belirttiğiniz veritabanına ait planları kullanım yoğunluğuna göre listeler.
SELECT [cp].[refcounts],[cp].[usecounts],[cp].[objtype]
,[st].[dbid],[st].[objectid],[st].[text]
,[qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE [st].[dbid] = db_id('Database_Name') ORDER BY usecounts desc
usecounts‘a göre filtreleyebiliriz. Bu sayede hangi planın daha çok kullanıldığını görmüş oluruz.
usecounts ilgili planının kullanım sayısını vermektedir. query_plan kısmından sorgumuza ulaşıp gerekli düzenlemeler yapılabilir.
Veritabanı filtrelemesi kaldırıldığında tüm sistem üzerinde veritabanlarının genel plan yapısını görebiliriz.
Usecounts (Kullanım Sayısı): Bu sütun bizim için en önemli metriktir. Çok yüksek usecounts değerine sahip sorgular, sistemin yükünü çeken ana sorgulardır. Bu planlardaki en küçük bir iyileştirme, toplam sistem performansında devasa farklar yaratır.
Query Plan Sütunu: Sorgu sonucunda gelen XML linkine tıkladığınızda, SQL Server size o sorgunun grafiksel planını açar. Burada “Missing Index” uyarılarını veya yüksek maliyetli “Scan” işlemlerini görebilirsiniz.
Objtype Filtreleme: Eğer cache’de çok fazla “Adhoc” sorgu görüyorsanız, bu durum veritabanında parametreleştirme (parameterization) eksikliği olduğuna işaret eder ve bellek şişmesine (Plan Cache Bloating) neden olabilir.
Query_plan kısmına tıkladığımızda sorgunun execution plan yapısını görebiliriz.
Performans tuning süreci sadece yavaş sorguları bulmak değildir; aynı zamanda sistemde en çok tekrarlanan işlemleri optimize etmektir. Yukarıdaki betiği kullanarak veritabanınızın “en çok yorulan” damarlarını tespit edebilir ve nokta atışı iyileştirmeler yapabilirsiniz.
Not: Sql server da execution planları görmek için sysadmin, db_owner ve db_creator yetkisinin olması gerekmektedir. Yada herhangi bir kullanıcı
Aşağıdaki komut ile tüm veritabanlarında plan cache üzerinde bulunan execution planları detaylı bir şekilde görebiliriz. Useaccount sayılarını büyükten küçüğe doğru sıralamaktadır.
EXEC sp_MSforeachdb'
USE [?];
IF DB_ID(''?'') > 4 -- Sistem veritabanlarını atla (master, tempdb, model, msdb)
BEGIN
SELECT TOP 5
''?'' AS [databasename],
[cp].[refcounts],
[cp].[usecounts],[cp].[objtype]
,[st].[dbid],[st].[objectid],[st].[text]
,[qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE [st].[dbid] = db_id(''?'') ORDER BY usecounts DESC
end ';
Başka makalede görüşmek dileğiyle..
De ki: “Eğer Allah’ı seviyorsanız bana uyun ki, Allah da sizi sevsin ve günahlarınızı bağışlasın. Çünkü Allah çok bağışlayandır, çok merhamet edendir.”Âl-i İmrân-31
