SQL Server DMV ve DMF – 3

DMV ve DMF serimizin 3. Serisinde sql server executionları arka planda çalışması sonucunda dataları performans anlamında nelere bakabileceğimizi ve bu parametreleri nasıl anlamlandırmamız gerektiğine bakacağız.
Özellikle IO ve Memory dar boğazı yaşadığımız zamanlarda arka planda koşan sorgular büyük önem arz etmektedir. Bir önceki makalede kullandığımız DMV ve DMF’lerin detaylandırılmış ve bize hangi sonuçlarını verdiğini bu yazıda öğrenmiş olacağız.
Özellikle Expensive Query olarak adlandırdığımız maliyetli sorguları kimi zaman sorun anında bulmak büyük sorunlar teşkil edebilmektedir. Sorunu doğru adreslediğimiz zaman çözüm noktasında ilerleme kaydetmiş oluyoruz. Şimdi hep birlikte performs dar boğazı yaratan sorguları nasıl bulabilirim konusunda bakalım.

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 LogicalReadsAvg desc

Yukarıdaki sorgu sayesinde, En fazla Logical Reads (ortamalamaya göre) yapan query’leri tespit etmiş olacağım. Sorguyu İstersek where koşuluna göre değiştirerek hatalı yada sorunlu sorgunun tespitini doğru adresleyebilirim.
Maliyetli sorguları tespit ederken, yararlandığım DMV ve DMF’ler bir önceki yazımızda detaylı olarak anlattığımız sys.dm_exec_ ile başlayanlar oldu. Bu DMC ve DMF’lere ilişkin bilgi almak isterseniz SQL Server DMV ve DMF – I adlı makaleyi okumanızı öneririm.
Sorunu yaşadığım kaynak durumuna göre tespit etmek istediğim zaman daha specific olarak giderek çözüm noktasında ilerleme kaydedebilirim.
Özellikle Fiziksel Read ve Logical Reads benim için önemli noktalardan biri olacaktır. Fiziksel Read Diskten okumayı gösterirken Logical Reads Buffer’dan okumayı göstermektedir.

Görsel – 1

Execution Count ise text içerisinde gelen sorgunun SQL Server restart olduğundan ayada iligili Database detach attach olduğundan beri sorgunun geldiği sayıyı gösterecektir.
Elapsed Time, Sorgunun çalıştığı süre içerisinde geçirdiği süreyi göstermektedir. Aşağıda paralel çalışma ve tek CPU üzerindeki çalışma farkını görebilirsiniz.

Görsel – 2

SELECT  TOP(25)
p.name AS [SP Name],
qs.total_logical_reads AS [TotalLogicalReads],
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
qs.execution_count AS 'execution_count',
qs.total_elapsed_time AS 'total_elapsed_time',
qs.total_elapsed_time/qs.execution_count AS 'avg_elapsed_time',
qs.cached_time AS 'cached_time'
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs 
ON p.[object_id] = qs.[object_id]
WHERE
qs.database_id = DB_ID()
ORDER BY
qs.total_logical_reads DESC;

Yukarıdaki Script sayesiyle, Memory pressure görüyorsam ve darboğaz yaşıyorsam. Bu sorunu yaşatan Cache’deki store procedureler nelerdir dediğimde bana logical read değerine göre sorunu tespit edebilmemi sağlayacaktır.

SELECT TOP(10) qs.execution_count AS [Execution Count],
(qs.total_logical_reads)/1000.0 AS [Total Logical Reads in ms],
(qs.total_logical_reads/qs.execution_count)/1000.0 AS [Avg Logical Reads in ms],
(qs.total_worker_time)/1000.0 AS [Total Worker Time in ms],
(qs.total_worker_time/qs.execution_count)/1000.0 AS [Avg Worker Time in ms],
(qs.total_elapsed_time)/1000.0 AS [Total Elapsed Time in ms],
(qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg Elapsed Time in ms],
qs.creation_time AS [Creation Time]
,t.text AS [Complete Query Text], qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE t.dbid = DB_ID()
ORDER BY (qs.total_logical_reads/qs.execution_count) DESC;

Yukarıdaki sorgu ile, CPU’da en fazla bekleyen sorguların tespitini yapabilir ve çözüm noktasında ilerleme kaydedilebilirir.
Tüm bu sciptler sadece teşhis için birer adımdır. Problemi çözmek sizin göstereceğiniz çaba ve ilgi ile çözülecektir.
Bir sonraki makalede görüşmek üzere.