Log Sql server stored procedure start and end time

Question

I have overloaded SQL Server, and I want to make some optimization.

For that purpose I'd like to get some statistics about the start and the finish time of the called stored procedures.

Is there any system table, or maybe other item, where I can get this information from?

Answer 1

SELECT 
    [procedure] = QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
        + '.' + QUOTENAME(OBJECT_NAME([object_id])),
    last_execution_time,
    avg_execution_time = CONVERT(DECIMAL(30,2), total_worker_time * 1.0 / execution_count),
    max_worker_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
ORDER BY avg_execution_time DESC;