SQL Profiler and Trace

As part of SQL Response V2 we’ll be collecting all sorts of data that allows the user to see what is happening on SQL instances monitored by the product. However, obviously, we have a delicate balancing act to perform – we want to maximise the information we obtain whilst minimising the impact on the monitored machine.

As most DBAs know, running a SQL Trace on a box will provide a great deal of information about what’s happening on the server – what statements were executing immediately before and after the alert for example. The downside of course is the impact – a few percent on CPU at least, not good on a production server.

Does this stop you using SQL Trace? Have you ever found it useful, even vital, analysing server problems or do you use other sources?

This entry was posted in Uncategorized and tagged . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

6 Comments

  1. jonathan allen
    Posted January 26, 2010 at 5:05 pm | Permalink

    Yes, its been crucial to diagnosing issues (mainly connectivity from the most recent ones that I remember) previously but its only turned on for a short duration so as to avoid the overhead you mention.

    I wouldnt say its a last resort but its effect on a busy server is known and its used with caution.

    • Ben Rees
      Posted January 26, 2010 at 6:00 pm | Permalink

      Cheers – thanks for the info.

      Ben

  2. Kev Riley
    Posted January 27, 2010 at 11:10 am | Permalink

    Running trace is a must when diagnosing issues, but as you’ve said a heavy trace can have an impact. And often you want to have captured the issue as it happens, rather than waiting for it to happen again. Catch-22!!

    What people seem to forget (or perhaps don’t even understand) is that there is a default trace running all the time, with little or no impact, so if v2 could have a ‘light’ trace option, that might be useful.

    Also one issue I’ve had with running the trace option in early versions of v1, is that it saturated my network connection, as I was only connected to the hosting provider on a 4Mb line, so with that in mind the amount of data as well as the CPU impact needs to be considered.

    • Ben Rees
      Posted January 28, 2010 at 10:51 am | Permalink

      Thanks for the comments – have you actively used the Trace option in v1? We’ve implemented something there to try and get round the Catch-22 issue that you mention – have you found this useful?

  3. Posted January 29, 2010 at 2:30 am | Permalink

    As we know the best way to start the SQL Trace is the server side mode and not using the SQL Profiler.

    This is not the kind of feature that I’m looking for in third party tools, in my opinion third party tools should fill in the blanks.

    If the idea is to provide features similar to SQL Server native features, to replace native feature for non-native features, you need to provide a very…very…powerfull tool to be worth to use it.

    • Ben Rees
      Posted January 29, 2010 at 12:46 pm | Permalink

      Alexa

      Thanks for your comments – do you use v1 of Response at all? If so – have you used the Trace functionality in that?

      Regards

      Ben

One Trackback

  1. [...] This post was mentioned on Twitter by Future Of Monitoring, Laerte Junior. Laerte Junior said: RT @futmon: Have you ever found #SQL Trace vital in analysing #sqlserver problems? http://bit.ly/5d4iKW [...]

Post a Comment

Required fields are marked *

Add an Image

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>