2020-10-26

TOP 50 Query Statement Order By Total Logical Reads

select top(50)
object_schema_name(ps.object_id,ps.database_id) + '.' +  object_name(ps.object_id,ps.database_id)  as SpName,
replace(replace((substring(t.text, qs.statement_start_offset/2, (case when  qs.statement_end_offset = -1 then len(t.text) * 2 else  qs.statement_end_offset end - qs.statement_start_offset)/2)),char(10),' '), char(13),' ') as QueryStatement,
db_name(t.[dbid]) as DatabaseName,
qs.total_elapsed_time  as TotalElapsedTime,
qs.total_logical_reads as TotalLogicalReads,
qs.min_logical_reads as MinLogicalReads,
qs.total_logical_reads/qs.execution_count as AvgLogicalReads,
qs.max_logical_reads as MaxLogicalReads,  
qs.min_worker_time as MinWorkerTime,
qs.total_worker_time/qs.execution_count as AvgWorkerTime,
qs.max_worker_time as MaxWorkerTime,
qs.min_elapsed_time as MinElapsedTime,
qs.total_elapsed_time/qs.execution_count as AvgElapsedTime,
qs.max_elapsed_time as MaxElapsedTime,
qs.execution_count as ExecutionCount
,(case when convert(nvarchar(max), qp.query_plan) LIKE N'%<MissingIndexes>%' then 1 else 0 end) as HasMissingIndex
,qp.query_plan as QueryPlan
,qs.creation_time as QueryPlanCreationTime
,qs.last_execution_time as LastExecutionTime
from sys.dm_exec_query_stats as qs with (nolock)
left join  sys.dm_exec_procedure_stats ps with (nolock)  on qs.sql_handle=ps.sql_handle
cross apply sys.dm_exec_sql_text(qs.plan_handle) as t
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
order by qs.total_logical_reads desc

.::YASAL UYARI::.

©2004-2023 Mehmet GÜZEL, www.mehmetguzel.net

Site içeriği kaynak gösterilmek koşuluyla yayınlanabilir. Yazılan yazı ve yorumlar sadece yazı ve yorum sahiplerini bağlar.