Geek Answers Handbook

Start and end time of the Sql server stored procedure

I rebooted SQL Server and I want to do some optimization.

To this end, I would like to get some statistics about the start and end times of the stored procedures being called.

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

+4
source share
3 answers
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;
+3
source

See DM Objects - SQL-Sys.dm Query Statistics

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

... if you are running 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
+2
source

You can do this manually by creating your own syslog table and then inserting a new line at the beginning of the stored procedure and then updating it at the end of the 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

The advantage of this is that if the stored process failed, it will not update the enddate syslog

+1
source

More articles:


All Articles