We are working on Stored procedure execution count task and need your help.
We are getting stored procedure execution count but not getting the inner query/stored procedure execution count ,which are called within SP.
for that we are using below two query.
SELECT DB_NAME(st.dbid) DBName
,( DB_NAME(st.dbid) + '_' + OBJECT_NAME(st.objectid,dbid) ) AS [Group]
FROM sys.dm_exec_cached_plans cp
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 cp.plan_handle, DB_NAME(st.dbid),
andSELECT (db_name(database_id) + '_' + object_name(object_id,database_id)) AS [Group]
, ps.execution_count AS [Execution Count]
, ps.execution_count / DATEDIFF(Second, cached_time, GETDATE()) AS [Calls/Second]
, ps.total_worker_time / ps.execution_count AS [AvgWorkerTime]
, ps.total_worker_time AS [TotalWorkerTime]
, ps.total_elapsed_time / ps.execution_count AS [AvgElapsedTime]
, DATEDIFF(Minute, cached_time, GETDATE()) AS [Age in Cache]
, LTRIM(Left(CONVERT(VARCHAR(2), GETDATE(), 108), 2)) AS HourOfDay
, Getdate() As InsertDate
, cached_time AS creation_time
, [sql_handle] AS query_hash
sys.dm_exec_procedure_stats as ps
ps.execution_count <> 0 and
DATEDIFF(Second, ps.cached_time, GETDATE()) <> 0
and (db_name(database_id) + '_' + object_name(object_id,database_id)) IS NOT NULL
object_name(object_id,database_id)IS NOT NULL
ORDER BY ps.execution_count DESC OPTION (RECOMPILE)
Both query return main SP details.
anybody help me to find all SP execution count details call direct or within other SP.
Thanks Ranveer Katiyar
Thank you for your question.
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
Thanks for helping make community forums a great place.
I test using first query and I can found execution count for inner stored procedure call.
Can you provide example how to reproduce your issue?
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
In Our case both query given same output but we need two different query.
First Query to Return all main SP call information and no inner SP or query calls.
And Second to get all the main SP calls with inner SP calls also. Basically we will create a job that run every hours and we can get the difference in execution count so that we can have count in a hour for SP.
Thanks Ranveer Katiyar