How to check which stored procedure is taking maximum time in sql server

Question

I want to know what are various methods by which I can monitor which of my stored procedure's and SQL queries are taking more time on various components(CPU cycle, scan time etc.) than already set threshold value.

I want it to be logged as well. Whenever any user uses my site and calling some procedure, I want to make a log of all procedures crossing my threshold.

Is it possible to do it with SQL queries or procedures. Do we have some procedures for this. Any SQL tools or any external tool, can be paid(with trial) or free. I want to try them on my database.

Answer 1

From the winning answer to a recent SO question, this will give you the top 50 most used procs and the statements in the procs. You can change the TOP 50 to TOP 1 or any other number you'd like to see.

SELECT TOP 50 *
FROM ( SELECT COALESCE(OBJECT_NAME(s2.objectid), 'Ad-Hoc') AS ProcName
           ,execution_count
           ,s2.objectid
           ,( SELECT TOP 1 SUBSTRING(s2.text,
                                     statement_start_offset / 2 + 1,
                                     ( ( CASE WHEN statement_end_offset = -1
                                              THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text))
                                                     * 2 )
                                              ELSE statement_end_offset
                                         END ) - statement_start_offset )
                                     / 2 + 1)
            ) AS sql_statement
           ,last_execution_time
        FROM sys.dm_exec_query_stats AS s1
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
     ) x
WHERE sql_statement NOT LIKE 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
    AND OBJECTPROPERTYEX(x.objectid, 'IsProcedure') = 1
    AND EXISTS ( SELECT 1
                    FROM sys.procedures s
                    WHERE s.is_ms_shipped = 0
                        AND s.name = x.ProcName )
ORDER BY execution_count DESC

You could run this periodically to see the changes over time. Put it in a stored procedure and insert the results into a log table. Set up a scheduled job to run that sproc at fixed intervals.