SQL Server DMV ve DMF Kavramları ve Kullanımları -2

Merhaba Arkadaşlar,

Bu yazımda SQL Server da DMV ve DMF ler kullanılarak sistem hakkında önemli bilgiler veren sorguları sizlerle paylaşacağım. Bu sorguları özellikle sorun anlarında çalıştırıp sistem hakkında genel bilgiler alabilirsiniz. Bu yazımı iyi anlayabilmeniz için önceki yazımı okumanızı tavsiye ederim.

Veritabanın da performansla alakalı bir sorun meydana geldiğinde, performans şikayetleri artmaya başladığında yada veritabanında olağan dışı bir yavaşlama meydana geldiğinde ilk akla gelen şey sistemi hangi sorgular yoruyor sorusuna cevap bulmaktır. Hangi sorgular gereksiz yere veritabanını yavaşlatıyor, Veritabanını yoran sorgulardan Gereksiz kaynak kullanan yada en çok sistem kaynaklarını tüketen sorgular nelerdir ? sorularının cevabını bulmamız gerekmektedir. Bu sorulara paralel olarak veritabanı tarafında ilk yaptığımız şeylerden birisi Veritabanın da En çok CPU kaynağı tüketen sorgular hangileri olduğunu ve  Bununla beraber Veritabanında En çok I/O yapan sorguları bulmaktır. Sorunlu olan yada Soruna sebeb olan bu sorgular bulunduğu takdirde SQL Tuning yada Performance Tuning çalışmaları başlatılabilir.

Şimdi ben önemli olan bu sorgulardan ilkini aşağıda vereceğim sorguyu çalıştırmama sebeb olan Soru ” Veritabanında En Çok CPU Tüketen Sorgular , bunların CPU da Geçirdiği zamanlar,Query Planlar nelerdir ? ” şeklindedir.  Bu sorguyu sys.dm_exec_query_stats DMV siyle sys.dm_exec_sql_text, sys.dm_exec_query_plan DMF leriyle Cross join yapıp oluşturuyoruz.Aşağıdaki sorgu genel anlamda Veritabanında koşan sorgular içerisinden En Çok CPU tüketen 20 sorgunun Açık halini, Query Planlarını, CPU da geçirdikleri Zamanı ve Ortalamasını, Logical Read ve Write değerlerini, Execution sayısını, Sorgunun ilk çalıştığı ve Son çalıştığı zamanı ve Memory e ait bilgileri bizlere sunmaktadır. Sorgunun Açık hali aşağıdaki gibidir.

select top 20
 case when sql_handle IS NULL
 then '' 
 else ( substring(st.text,(qs.statement_start_offset+2)/2,
 (
 case when qs.statement_end_offset = -1 
 then len(convert(nvarchar(MAX),st.text))*2 
 else qs.statement_end_offset 
 end - qs.statement_start_offset) /2 ) )
 end as query_text
, qp.query_plan
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [logicalWrites]
, execution_count
, creation_time
, last_execution_time
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO]
, db_name(st.dbid) as database_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
where total_worker_time > 0 
order by total_worker_time desc;

Yukardaki sorguyu testlerim için kullandığım AdventureWorks2012 veritabanında çalıştırdığım zaman aşağıdaki gibi bir çıktı verecektir.

Yukardaki sorgu ile çalışan sorgular yada cache belleğin büyüklüğüne göre yeni bitmiş sorgular gelmektedir. Geçmişe dönük 5-10 dakika öncesi yada 1-2 saat önceki sorguları görmek istiyorsanız Performance Data Collection özelliğini kullanmanız gerekiyor yada SQL Server Profiler gibi toolları kullanıp geçmişe dönük sorguları inceleyebilirsiniz. Sorgu sonucuna göre query leri SQL Tuning ile iyileştirebilirsiniz bunun için 2.kolondaki query_plan ı incelememiz lazım. Mavi harf ile gösterilen linke tıkladığımızda aşağıdaki gibi sorgulardan herhangi birine ait Execution plan görünecektir. Bu execution plana göre gerekli Performance tuning operasyonu yapılmalıdır.

En Çok CPU tüketen sorgunun yanında birde En Çok I/O (input / output) yapan sorgular nelerdir ? diye merak edebiliriz yada En çok I/O yapan sorguları bulup bu sorgularıda SQL Tuning işlemlerinden geçirip sorguları optimize edebiliriz. Bunun için Veritabanında koşan sorgulardan en çok I/O tüketen ilk 20 sorguyu ve bu sorgulara ait Total_logical_read değerini ve yukardaki gibi istatistik bilgilerini toplamak için aşağıdaki sorguyu çalıştırmamız gerekmektedir.

select top 20
 case when sql_handle IS NULL
 then '' 
 else ( substring(st.text,(qs.statement_start_offset+2)/2,
 (
 case when qs.statement_end_offset = -1 
 then len(convert(nvarchar(MAX),st.text))*2 
 else qs.statement_end_offset 
 end - qs.statement_start_offset) /2 ) )
 end as query_text
, qp.query_plan
, qs.total_logical_reads
, total_logical_reads as [LogicalReads]
, total_logical_writes as [logicalWrites]
, execution_count
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, creation_time
, last_execution_time
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO]
, db_name(st.dbid) as database_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
order by qs.total_logical_reads desc;

Yukardaki Veritabanında En Çok I/O yapan ilk 20 sorguyu veren sorguyu AdventureWorks2012 test veritabanımda çalıştırdığımda aşağıdaki gibi örnek bir çıktı elde etmekteyim.

Sorgu sonuçlarından yukardaki gibi herhangi birisinin query_plan ını ilgili kolona tıklayarak aşağıdaki gibi görüntüleyebiliyoruz.

Yukardaki Execution plan ı inceleyerek eğer ihtiyaç varsa SQL Tuning yapabiliriz. Fakat Execution Plan ı dikkatli incelediğimiz takdirde yukardaki sorgunun Tune edilmeye ihtiyacı olmadığını ve Sorgunun Cost değerlerininde çok çok düşük olduğunu görebiliriz.

Yukardaki 2 sorguda da görüldüğü gibi Önceki 2 yazımda en çok kullanılanlar diye bahsettiğim DMV ve DMF lerden faydalanarak bir sorun anında En Çok CPU Tüketen Sorgular, En Çok I/O yapan sorgular hangileridir ? Sorularının cevabını hızlıca bulmaktayım. Her konuda olduğu gibi sorunu ve sorunun kaynağını tespit edebilmek sorunu çözmenin yarısıdır belki bazen daha fazlasıdır. Sizlerde bu DMV ve DMF lerden faydalanarak sistemlerinizi yönetirken işinize yarayacak çok faydalı scriptler üretebilir yada bulabilirsiniz.

Böylece bir yazımın daha sonuna gelmiş bulunmaktayım bir sonraki SQL Server DMV ve DMF yazısında görüşmek dileğiyle Esen kalın…

Oracle Exadata SQL Server Goldengate Weblogic EBS ve Linux konusunda aşağıdaki konularda 7×24 Uzman Danışmanlara yada Eğitimlere mi İhtiyacınız var [email protected] adresine mail atarak Bizimle iletişime geçebilirsiniz.

– Oracle Veritabanı Danışmanlığı
– Oracle Veritabanı Bakım ve Destek
– Exadata Danışmanlığı
– Exadata Bakım ve Destek
– SQL Server Veritabanı Danışmanlığı
– SQL Server Veritabanı Bakım ve Destek
– Goldengate Danışmanlığı
– Goldengate Bakım ve Destek
– Linux Danışmanlığı
– Linux Bakım ve Destek
– Oracle EBS Danışmanlığı
– Oracle EBS Bakım ve Destek
– Weblogic Danışmanlığı
– Weblogic Bakım ve Destek
– Oracle Veritabanı Eğitimleri
– Oracle VM Server Danışmanlığı
– Oracle VM Server Bakım ve Destek
– Oracle EPPM Danışmanlığı
– Oracle EPPM Bakım ve Destek
– Oracle Primavera Danışmanlığı
– Oracle Primavera Bakım ve Destek
– Oracle Eğitimleri
– SQL Server Eğitimleri
– Goldengate Eğitimleri
– Exadata Eğitimleri
– Linux Eğitimleri
– Oracle EBS Eğitimleri
– Oracle VM Server Eğitimleri
– Weblogic Eğitimleri
– Oracle EPPM Eğitimleri
– Oracle Primavera Eğitimleri