SQL Server 2005: Stored Procedure call activity statistics & execution time

Is it possible to get statistics like how many times a stored procedure is called such as average execution time and how often that procedure is being called.

Bascially it is possible to get provided that particular stored procedure is active and plan is available in the cache. Using a DMV sys.dm_exec_query_stats that will give the execution count value that can co-relate to how many times that stored procedure has been called since it was compiled last, then plan generation value gives how many times that procedure is recompiled. As stated both of these values are available if that procedure is in cache, run the following query:

 

SELECT

FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

ORDER

BY total_worker_time/execution_count DESC

TOP 10 total_worker_time/execution_count AS [Avg CPU Time], Plan_handle, query_plan, qs.execution_count,qs.creation_time,qs.last_execution_time, (total_worker_time/(execution_count * 1000.0)) as avg_worker_time_in_millisecs