MSSQL Server Expensive Queries(Pahalı Sorgular)

Bu makalede sql server üzerinde dönen sorguların maliyeti en yüksek olan sorguları görmüş olacağız. Bu sayede hangi sorgunun sql üzerinde maliyetini görüp ona göre bakım performans işlemlerimizi yapmış olacağız. Maliyetli sorguları bulmak için aşağıdaki sorgu kullanılmaktadır.

select top 50 coalesce (db_name(st.dbid), db_name(convert (int, pa.value)), 'Empty') as DBName,
             qs.last_execution_time as LastExecutionTime,
             SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
                           ((CASE qs.statement_end_offset
                             WHEN -1 THEN DATALENGTH(st.text)
                            ELSE qs.statement_end_offset
                            END - qs.statement_start_offset)/2) + 1) AS StatementText,
             st.text as ProcedureTextOrBatchText,        
             qs.execution_count as ExecutionCount,
             (qs.total_worker_time/1000) as CPUTimeTotal,
             ((qs.total_worker_time/1000)/qs.execution_count) as CPUTimeAvg,
             (qs.total_elapsed_time/1000) as DurationTimeTotal,
             ((qs.total_elapsed_time/1000)/qs.execution_count) as DurationTimeAvg,
             qs.total_physical_reads as PhysicalReadsTotal,
             (qs.total_physical_reads/qs.execution_count) as PhysicalReadsAvg,
             qs.total_logical_reads as LogicalReadsTotal,
              (qs.total_logical_reads/qs.execution_count) as LogicalReadsAvg

from sys.dm_exec_query_stats qs
             cross apply sys.dm_exec_sql_text(sql_handle) st
             cross apply sys.dm_exec_query_plan(plan_handle) qp
             cross apply sys.dm_exec_plan_attributes(qs.plan_handle) pa
where attribute = 'dbid'
       and execution_count> 1000
--order by CPUTimeTotal desc
order by CPUTimeAvg desc
--order by DurationTimeTotal desc
--order by DurationTimeAvg desc
--order by PhysicalReadsTotal desc
--order by PhysicalReadsAvg desc
--order by LogicalReadsTotal desc
--order by LogicalReadsAvg desc

Sorgunun hangi veritabanı üzerinde çalıştığını, sorgunun çalışma sayısını, ne kadar CPU’da harcadığı zamanı, diskten veya memoryden ne kadar okuma yaptını ve toplam harcanan zamanı göstermektedir.

Sorgu sonunda bulunan order by ifadelerini aktif ederek sıralama işlemini istediğimiz koluna göre yapabiliriz.

--order by DurationTimeTotal desc

--order by DurationTimeAvg desc

--order by PhysicalReadsTotal desc

--order by PhysicalReadsAvg desc

--order by LogicalReadsTotal desc

--order by LogicalReadsAvg desc

Sql server üzerinde dönen sorgulardan maliyeti en fazla olan 50 sorgumuzu görmekteyiz. İlgili script çalıştırılarak performans tuning yapılabilir.

Maliyetli sorgularımızı anlık olarak görebileceğimiz bir başkan bölüm SSMS üzerinden instance sağ tıklayıp Active monitor bölümünden detaylı bir şekilde görülebilir.

Recent Expensive Queries (Yakın Zamandaki Pahalı Sorgular)

Bu bölüm, son birkaç dakika içinde tamamlanmış veya hala çalışan, sistem kaynaklarını (CPU, RAM, Disk) en çok tüketen sorguları listeler.  Kaynak tüketimine bakılmaktadır. Bir sorgu 1 milyon satırı okumaya çalışıyorsa veya çok karmaşık hesaplamalar yapıyorsa burada görünür. Canlı performans sorunu olduğunda ilk bakılacak yerlerden biridir.

Active Expensive Queries (Aktif Pahalı Sorgular)

Bu bölüm, şu an o saniyede (real-time) çalışmaya devam eden ve yüksek kaynak tüketen sorguları gösterir. Neye Bakar. Henüz bitmemiş, işlemcide veya diskte yoğun yük oluşturan canlı işlemlere. Farkı Nedir. “Recent” listesinde işi bitmiş sorguları da görebilirsiniz ancak “Active” listesinde sadece o an sistemi meşgul edenleri görürsünüz. Burada CPU,Logical read ve diğer parametrelere göre sıralama yapabilirsiniz.

Herhangi bir sorgunun üzerine gelerek Query ve Execution Plan yapısı görülmektedir.

Sql server üzerinde Anlık olarak çalışan sorguların maliyetli sorguları görmek için aşağıdaki script kullanılabilir.

select r.total_elapsed_time / 1000.0 as total_elapsed_s,percent_complete,r.session_id,r.blocking_session_id,
r.last_wait_type,
s.login_name,'kill ' + cast(r.session_id as varchar) as MySessionID,DB_NAME(r.database_id) as DatabaseName,command
,SUBSTRING(t.text, (r.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(t.text)
        ELSE r.statement_end_offset END 
            - r.statement_start_offset)/2) + 1) AS statement_text
,r.status,wait_time
,wait_type,wait_resource,text,start_time,s.program_name
,r.last_wait_type,s.host_name,r.granted_query_memory * 8 / 1024 as memory_mb
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) t
where r.session_id <> @@SPID  
--and r.database_id =DB_ID('Hangi DB'ye gelen sorguları listelemek istiyorsanız')
--and t.text like  '%text  bazlı sorgulama yapmak için text içeriği%' 
--and r.session_id = sessionidA
--and s.Login_Name like '%Login bazlı filtreleme%'  
--and s.program_name LIKE '%Program bazlı filtreleme örneğin Management%'
--and r.wait_type !='bekleme tipine göre filtreleme'
--and r.blocking_session_id<>0"C
and r.last_wait_type !='SP_SERVER_DIAGNOSTICS_SLEEP'
and r.last_wait_type !='WAITFOR'
order by 1 desc ;   

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

“İlminle övünme, Şeytan’a bak!” Araf-12