Post reply

real time and historical monitoring of problematic procedures and sql

  • January 9, 2009 at 6:54 am

    #155108

    I've recently inherited some sql servers (2005) that I have to maintain. My background is mostly Sybase and mysql but as SQL Server's great-great-grandfather is Sybase I'm finding that I can get around and make sense of stuff pretty easily. (I'm still getting used to using a gui as they pry the command line utility from my cold fingers.)

    One of the problems I'm having is getting a handle on performance of procedures over time. In the Sybase world we used two products: Monitor Server and Historical Server which captured details on execution and stored them for whatever time period we needed. I could then grab data for proc performance (for example), throw it into an excel spreadsheet, and graph to my hearts content. From this I could determine who was degrading over time and correlate performance to other activities in the server and database.

    I know that I can generate traces (is that the right word?) of stored procs and stuff that data into tables from which I can generate statistics on point-in-time performance. Is is reasonable to run the monitor tracing all the time on sql server? Alternatively, is there a SQL Server add-on or third party application which will let me capture procedure execution statistics over time and store them for later review while at the same time not putting undue stress on SQL Server.

    Thanks

  • January 9, 2009 at 9:03 am

    #924283

    Suggest you start by looking at the various Dynamic Management Views

    DMVs in Books On Line - here is a good starting point:

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/cf893ecb-0bf6-4cbf-ac00-8a1099e405b1.htm

    Oh you have posted this question in a 2000 forum when you say you are using 2005. So if you require further assistance, may I suggest posting to a 2005 forum..

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • January 9, 2009 at 9:34 am

    #924326

    Moving to a 2005 forum.

    You can run a trace all the time, I'd schedule or set up a server side trace, not with profiler to reduce the load. however you want to watch what you trace. The more stuff you grab, the more a load it is.

    I'd recommend you grab what you need, perhaps minimal columns of data and just the events that will let you determine what is happening on the server. You can use Profiler to play around with columns and events and get an idea of what will help you, or match what you get from other products and then generate files of data.

  • January 9, 2009 at 10:06 am

    #924358

    There are also several third party products that do real-time and historical monitoring.

    http://letmegooglethatforyou.com/?q=SQL+Server+Performance+Monitoring+tool

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • January 9, 2009 at 11:24 am

    #924435

    thanks for all the good info.

    sorry for the post in the wrong forum.

    off to read the suggestions.

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

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