SQL Optimization Techniques- Finding Heavy Stored Procedures in Sql Server

Following query will help you finding the most heavy stored procedures in your sql database. This query will tell you Number of Execution Count, AVG CPU time to execute the respective SP and Total CPU Time of the respective SP. 

Run the following query in your sql server query window:

SELECT DB_NAME(dest.dbid) AS [Database name]

        ,OBJECT_NAME(dest.objectid, dest.dbid) AS [Stored procedure name]

        ,[Execution Count]

        ,[Avg CPU time (ms)]

        ,[Total CPU time (ms)] [Avg Elapsed time (ms)]

        ,[Creation time]

        ,[Last Execution time] [Total Elapsed time (ms)]

        ,[Plan Count]

        ,getdate() AS [Recorded date]

        ,sql_handle

        ,dest.TEXT AS 'Text'

FROM (

        SELECT TOP 100 sql_handle

               ,COUNT(DISTINCT plan_handle) AS [Plan Count]

               ,MAX(execution_count) AS [Execution Count]

               ,SUM(total_worker_time / execution_count) / 1000 AS [Avg CPU time (ms)]

               ,SUM(total_elapsed_time / execution_count) / 1000 AS [Avg Elapsed time (ms)]

               ,MAX(creation_time) AS [Creation time]

               ,MAX(last_execution_time) AS [Last Execution time]

               ,SUM(total_worker_time / 1000) AS [Total CPU time (ms)]

               ,SUM(total_elapsed_time / 1000) AS [Total Elapsed time (ms)]

        FROM sys.dm_exec_query_stats

        GROUP BY sql_handle

        ORDER BY SUM(total_worker_time / execution_count) / 1000 DESC

        ) deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

WHERE dest.dbid = DB_ID();