SELECT top 10
ProcedureName = o.name,
[Procedure] = t.text,
ExecutionCount = s.execution_count,
AvgExecutionTime = isnull( s.total_elapsed_time / s.execution_count, 0 ),
AvgWorkerTime = s.total_worker_time / s.execution_count,
TotalWorkerTime = s.total_worker_time,
MaxLogicalReads = s.max_logical_reads,
LastLogicalReads = s.last_logical_reads,
MaxLogicalWrites = s.max_logical_writes,
LastLogicalWrites = s.last_logical_writes,
CreationDateTime = s.creation_time,
MaxPhysicalReads = s.max_Physical_reads,
LastPhysicalReads = s.last_Physical_reads,
CallsPerSecond = isnull( s.execution_count / datediff( second, s.creation_time, getdate()), 0 )
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
INNER JOIN SYS.objects o on t.objectid = o.object_id
ORDER BY
AvgExecutionTime DESC
-- select * from sys.databases
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
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 statement_text,
st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
2 comments:
HI it is more optamized version to check the Long running queries
CREATE PROCEDURE [dbo].[UP_procProcessTimes]
AS
SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName , Hostname
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame, Hostname
SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,max(cp.usecounts) Execution_count
,sum(qs.total_worker_time) total_cpu_time
,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0) avg_cpu_time
FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)
order by sum(qs.total_worker_time) desc
SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
,OBJECT_NAME(objectid,st.dbid) StoredProcedure
,max(cp.usecounts) execution_count
,sum(qs.total_elapsed_time) total_elapsed_time
,sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)
order by sum(qs.total_elapsed_time) desc
I got good results from this query but I also want to show execution plan so that i can identify the problem. so i have updated the query a little look at this example
SELECT top 10
ProcedureName = o.name,
[Procedure] = t.text,
ExecutionCount = s.execution_count,
AvgExecutionTime = isnull( s.total_elapsed_time / s.execution_count, 0 ),
AvgWorkerTime = s.total_worker_time / s.execution_count,
TotalWorkerTime = s.total_worker_time,
MaxLogicalReads = s.max_logical_reads,
LastLogicalReads = s.last_logical_reads,
MaxLogicalWrites = s.max_logical_writes,
LastLogicalWrites = s.last_logical_writes,
CreationDateTime = s.creation_time,
MaxPhysicalReads = s.max_Physical_reads,
LastPhysicalReads = s.last_Physical_reads,
CallsPerSecond = isnull( s.execution_count / datediff( second, s.creation_time, getdate()), 0 ) ,
sdeqp.query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
INNER JOIN SYS.objects o on t.objectid = o.object_id
CROSS apply sys.dm_exec_query_plan(s.plan_handle) sdeqp
ORDER BY
AvgExecutionTime DESC
Post a Comment