none
Execution Count in Sql Server 2008

    Question

  • Hello Team,

    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
     ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
     ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
     ,max(cp.usecounts) Execution_count
     ,( 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),
       OBJECT_SCHEMA_NAME(objectid,st.dbid), 
       OBJECT_NAME(objectid,st.dbid) 

    and

     SELECT  (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]
      , ps.max_logical_reads
      , ps.max_logical_writes
      , ps.total_physical_reads
      , 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
      , ps.total_elapsed_time 
      , [sql_handle] AS  query_hash
      , ps.plan_handle  
      FROM
      sys.dm_exec_procedure_stats as ps
      WHERE
      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

    Friday, October 18, 2013 12:24 PM

All replies