Log Sql server stored procedure start and end time

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开发者_JAVA技巧 any system table, or maybe other item, where I can get this information from?

---

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;

---

Have a look at DM Objects - Sys.dm exec query stats

or from http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=159011:

... if you are running on sql 2008 or higher. .....

SELECT 

COALESCE(DB_NAME(t.[dbid]),'Unknown') AS [DB Name],
ecp.objtype AS [Object Type],
t.[text] AS [Adhoc Batch or Object Call],
SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
,qs.[last_execution_time] AS [Last Exec Time]
,qs.[creation_time] AS [Creation Time]

FROM sys.dm_exec_query_stats AS qs
    JOIN sys.dm_exec_cached_plans ecp 
            ON qs.plan_handle = ecp.plan_handle
            CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
where
    ecp.objtype = 'Proc'

order by [Last Exec Time] desc

---

You could do it manually by creating your own system log table then inserting the new row at the start of the stored procedure and then updating it at the end example:

INSERT INTO systemLog (proc,start) VALUES ('My Proc',GETDATE())
SELECT @logid = SCOPE_IDENTITY()

--Do stuff here

UPDATE systemLog SET enddate = GETDATE() WHERE logid = @logid

Advantage of this is if a stored proc fails it wont update the system log enddate