How to catch stored procedure execution time?

Hello, everyone:
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYTThe most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||Pootle:

Thanks for reply. The problem is I am not allowed to change stored procedures and use profiler. I am going to setup a group of queries or stored procedures by which insert the execution time to a record table.

ZYT

The most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||The problem is I am not allowed to change stored procedures and use profiler.Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.

As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.

-PatP|||Below batch query might help you to determine the performance in secounds

declare @.startproc datetime
declare @.endproc datetime
declare @.time integer

select @.startproc = getdate()

exec <stored procedure>
select @.endproc = getdate()

select @.time = DATEDIFF(second, @.startproc, @.endproc)

print str(@.time)|||Pat:

Thanks for reply. You are right, I got sticky stats. The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.

ZYT

Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.

As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.

-PatP|||The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!

Do you use source control? If so you could write a script to parse the files and retro fit execution logging information. Verify everything on your test server. Devs don't need to lift a finger.

I agree with Pat though - you are not being given enough latitude to perform your task as things stand.|||I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!Yeah, what poots said!

As long as you run the profiler on a different machine, the only additional load you place on the SQL Server is the transmission of the profiler data. This is negligable (always less than 2 percent, normally much less than 1 percent in terms of performance of the SQL Server).

The only exception to this rule is if your SQL Server is severly "network bound" so that the NIC is flooded. If that is the case, the SQL processing will nearly halt immediately because the profiler will also flood the NIC. This is easy to check for using either Task Manager or Performance Monitor, and you'll find out nearly instantly when you turn the Profiler on if you forget!

-PatP|||Hi, Pootle:

Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company. So I cannot argue about that. I got the paper you recommend and want to know if someone has experience to catch execution time by this paper.

Thank

ZYT

I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||No experience but Scenario 1in the paper describes exactly this.|||poor planning

As part of all development I make sure that every sproc contains code, outside of any transaction, to log the length of the sproc to a table...

Logging the sproc call from code would not give you a true length due to other resources

I find that log so useful in so many ways

What developers suck at coding, what developers aren't coding, and when it goes to prod, what sprocs need to be tuned...but I've already noticed that in dev, to the point where I don't need to do the logging|||Hi, Pootle:

Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company.

Well that's pure bull sheet

Blame it on profiler

How about blame it on the guy who set it up? What did he do, set it up let it run forever and fill up the disk...puuuleeeeze|||Pootle:

By the paper you recommended, I do catch the execution time of stored procedures for the given database. The execution time is output to a .trc file (SQL profiler-trace data file). So there is another question for you. Is it possible to save a .trc file to be a table by T-SQL, and how? I can use profiler to open .trc file and save as a table, but someone prefer to do that automatically.

Thanks

ZYT

No experience but Scenario 1in the paper describes exactly this.|||Hi

Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it

HTH|||Pootle:

I got it.

SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\test.trc', default)

can work. or refer http://support.microsoft.com/kb/270599.

Thanks everyone. This is a good post.

ZYT

Hi

Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it

HTH