Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    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

  2. #2
    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.

  3. #3
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,970
    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

  5. #5
    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)

  6. #6
    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

  7. #7
    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.

  8. #8
    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

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,970
    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

  10. #10
    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

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    No experience but Scenario 1in the paper describes exactly this.

  12. #12
    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.

  13. #13
    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.

  14. #14
    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.

  15. #15
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •