Find How long SP ran - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find How long SP ran

Author

Topic

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-01-27 : 21:31:01

Hey Guys,

I need to find how long a Stored Procedure ran last 5 times it was executed in sql 2005? The SP had logs but we had truncated the log earlier and need this now... Any help is appreciated.

Thanks

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-27 : 22:10:37

you can go to executin plan and see, for how long your sp ran.
but normally when you run a SP inside the mgt studio it shows the execution time underneath the bar.

Avantha Siriwardana

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-01-27 : 22:33:11

What about the time it ran a month back?

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-27 : 22:39:23

you mean you wanna see the execution plan for a given date and time? isn't it??

Avantha Siriwardana

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-27 : 22:40:57

[code]SELECT OBJECT_NAME(sys.dm_exec_sql_text.objectid),
sys.dm_exec_query_stats.*
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text
(sys.dm_exec_query_stats.sql_handle)
WHERE sys.dm_exec_sql_text.dbid = db_id()
AND OBJECT_NAME(sys.dm_exec_sql_text.objectid) = 'yoursp' [/code]

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-27 : 22:53:41

sodeeps code does not give detials when it ran a month back??
isn't it? :)

Avantha Siriwardana

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-01-27 : 22:59:52

yeah it did not...

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-27 : 23:08:57

im not sure whether you will be able to get exact detils for a given date and time..but give me somtime and i will try to come up with a solution. ok

Avantha Siriwardana

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-01-27 : 23:10:33

thanks a lot Avantha.

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-28 : 00:55:13

no prob
:)

Avantha Siriwardana

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-28 : 01:10:43

It is not possible to see how long a stored procedure ran in the past unless you had a trace running.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog