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.

DMV3

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.

SQL Server TOP CPU Query

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.

DMV4

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.

SQL Server TOP IO Queries

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

DMV6

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…

Reklamlar

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap / Değiştir )

Twitter resmi

Twitter hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap / Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap / Değiştir )

Google+ fotoğrafı

Google+ hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap / Değiştir )

Connecting to %s