June 17, 2013 at 3:16 pm
Is there any way to find out how many times a USP was executed and how much time it took to execute? Please advise.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.
June 17, 2013 at 3:30 pm
Times are in microseconds.
SELECT TOP (250) p.NAME AS [SP Name]
,qs.execution_count
,ISNULL(qs.execution_count / DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second]
,qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
,qs.total_worker_time AS [TotalWorkerTime]
,qs.total_elapsed_time
,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
,qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC
OPTION (RECOMPILE);
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
June 17, 2013 at 3:50 pm
J Good (6/17/2013)
Times are in microseconds.
SELECT TOP (250) p.NAME AS [SP Name],qs.execution_count
,ISNULL(qs.execution_count / DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second]
,qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
,qs.total_worker_time AS [TotalWorkerTime]
,qs.total_elapsed_time
,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
,qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC
OPTION (RECOMPILE);
Thanks, J Good.
Is this script good for SQL 2005?
I am getting the error when I run on 2005...but it worked on 2008 and above.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_exec_procedure_stats'.???
SueTons.
Regards,
SQLisAwe5oMe.
June 18, 2013 at 7:10 am
SQLCrazyCertified (6/17/2013)
Thanks, J Good.
Is this script good for SQL 2005?
I am getting the error when I run on 2005...but it worked on 2008 and above.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_exec_procedure_stats'.???
SueTons.
Sorry, but the script does not work in 2005.
I believe this should give similar information in 2005:
--From http://blog.sqlauthority.com/2008/03/22/sql-server-2005-find-highest-most-used-stored-procedure/
--Can sort by a specific DB by adding a where clause
SELECT TOP 50 qt.TEXT AS 'SP Name',
qs.execution_count AS 'Execution Count',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_physical_reads AS 'PhysicalReads',
qs.creation_time 'CreationTime',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply