Submit a ticketCall us

Have You Auto Renewed? If not, you're missing out.
The SolarWinds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more. Experience all of this with the convenience of Auto Renewal, and never worry about missing any of these great benefits. Learn More.

Home > Success Center > Network Performance Monitor (NPM) > Determine which Stored Procedure is using the most CPU, I/O, or has the longest duration

Determine which Stored Procedure is using the most CPU, I/O, or has the longest duration

Table of contents
Created by Wendell Bazile, last modified by Rodim Suarez on Aug 26, 2016

Views: 17 Votes: 0 Revisions: 4

Overview

Determine which Stored Procedure is using the most CPU, I/O, or has the longest duration.

Environment

All Orion based applications

Steps

Attach to the database via Database Manager or SQL Management Studio and run the following 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

      ,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

 

See also Get the execution count of a Stored Procedure for more information.

 

Last modified

Tags

Classifications

Public