How to capture CPU and Memory usage for a stored Procedure

• SQL_Buddy99

  Right there with Babe

  Points: 776

  More actions

  January 19, 2015 at 3:32 pm

  #296790

  I have around 100 packages (all [packages run at same time) each package calls a stored Procedure once the Stored proc Execution is completed the package will write the log information into a log table, here how can i capture the CPU and Memory usage for execution of each stored proc.

  Thanks in advance

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  January 19, 2015 at 7:09 pm

  #1771279

  http://www.red-gate.com/community/books/dynamic-management-views

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Bhushan Kulkarni

  SSCrazy

  Points: 2829

  More actions

  January 21, 2015 at 3:52 am

  #1771740

  You can use DMVs - sys.dm_exec_query_stats ,sys.dm_exec_sql_text,sys.dm_exec_cached_plans

  You can use code below.

  SELECT TOP 20 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