Script to find out Stored Procedures which are Using most resources


While doing the stored procedure performance tuning, you can use sys.dm_exec_procedure_stats DMV to get resource intensive procedures.

You can use the below script for it.

SELECT DB_NAME(database_id) AS DatabaseName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME] 
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS Average_CPU
      ,total_elapsed_time / execution_count AS Average_Elapsed_Time
      ,total_logical_reads / execution_count AS Average_Logical_Reads
      ,total_logical_writes / execution_count AS Average_Logical_Writes
      ,total_physical_reads  / execution_count AS Average_Physical_Reads
FROM sys.dm_exec_procedure_stats 
where database_id <> 32767
ORDER BY Average_Logical_Reads DESC
Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s