If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > How to catch stored procedure execution time?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2003
Posts: 223
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.

ZYT
Reply With Quote
  #2 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2003
Posts: 223
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

Quote:
Originally Posted by pootle flump
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.
Reply With Quote
  #4 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,820
Quote:
Originally Posted by yitongzhang
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Feb 2007
Location: Chennai, TN
Posts: 68
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)
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Mar 2003
Posts: 223
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


Quote:
Originally Posted by Pat Phelan
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
Reply With Quote
  #7 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Quote:
Originally Posted by yitongzhang
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.
Reply With Quote
  #8 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
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...sql_server.htm
Reply With Quote
  #9 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,820
Quote:
Originally Posted by pootle flump
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Mar 2003
Posts: 223
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

Quote:
Originally Posted by pootle flump
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...sql_server.htm
Reply With Quote
  #11 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
No experience but Scenario 1in the paper describes exactly this.
Reply With Quote
  #12 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
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
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #13 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
Quote:
Originally Posted by yitongzhang
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
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Mar 2003
Posts: 223
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


Quote:
Originally Posted by pootle flump
No experience but Scenario 1in the paper describes exactly this.
Reply With Quote
  #15 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On