Post reply

monitoring stored procedures

  • April 19, 2005 at 3:36 am

    #165272

    Hi Guys,

    Im pretty new to SS 2000 admininstration.  I was wondering if anyone could lead me in the right direction. One of the departments are bringing in some new stored procedures for validation that will be more resource intensive. These are currently on a test box that simulates the production env. I need to monitor the new stored procs to see daily how many times they are being called and how long they take to execute. Can anybody guide me on how to go about monitoring these new stored procs? I need to monitor these using minimal resources as they are quite vital

    Thanks in advance ,

    Martin

     

  • April 19, 2005 at 4:55 am

    #553390

    one word 'Profiler'.

    Set up a trace to monitor SQL:Batch Completed and enable it only for the DB that you want to monitor.

    As for how many times the queries are being ran, that depends on whether you're passing parameters into them or not.  If not then a simple SP count trace will be perfect for you, however, if you are using parameters the first trace will allow you to save the results to a table which you can then query using the PATINDEX function to strip out all of the extra information allowing you to do a count with a group by.

    Give us a shout if you need any further information, god knows I don't always explain things clearly enough

  • April 19, 2005 at 5:17 am

    #553394

    I was thinking of using the profiler alright but was wondering if there was another method that was less intensive on resources? Thanks for the feedback!

    M

     

  • April 19, 2005 at 5:20 am

    #553395

    You can limit profiler so that it only captures information on SP's that run longer than a set duration, then again that doesn't really help if you want to identify how many are being run too.

  • April 19, 2005 at 5:24 am

    #553396

    Grand, well il give that a shot and see how it goes, but if you come up with some other suggestions on how I can monitor them, i'd appreciate if you could let me know.

    Thanks again,

    Martin

     

  • April 20, 2005 at 6:24 am

    #553746

    I came across a post somewhere else (http://www.sqlservercentral.com/columnists/RDyess/obtainingqueryexecutionplansthroughsqlprofilertrac.asp)

    that talks about tracing procedures without using profiler.

    It gets a bit complicated though, but may be worth trying if the performance impact of profiler is too much for you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply