Using Server Side Traces for Dynamic Performance Evaluation

  • JacekO

    SSCertifiable

    Points: 6347

    Comments posted to this topic are about the item Using Server Side Traces for Dynamic Performance Evaluation

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • ryan.hart

    SSC-Addicted

    Points: 462

    Thanks for posting this info. I also have some situations where several sprocs access the same tables for different purposes. It'll be nice to get specific performance change details system wide after changing one sproc, index, etc...

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Well done.

    I have been doing this type of analysis for a decade now, but have never done the execution histogram like you have done with the case statements. I like that.

    It would have been nice if you had covered some of the ways to 'normalize' the executions by replacing sproc parameter values. Perhaps the next article... πŸ™‚

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • david.murden

    Old Hand

    Points: 334

    Hi Jacek0,

    I might be being a cabbage but i have never seen :: used as a prefix to a join or anywhere else in sql actually. I had a quick search around and can't find anything about its usage.

    Can you tell me what the :: does as a prefix to the built-in function fn_trace_gettable()?

    cheers

  • G33kKahuna

    SSCommitted

    Points: 1726

    JacekO,

    No offense, but you are wasting too much time on worthless reporting. If I were you, I would just use ClearTrace to read the trace results. It analyzes based on SQL signature

    Cheers ...

  • JacekO

    SSCertifiable

    Points: 6347

    david.murden (12/1/2009)


    Hi Jacek0,

    I might be being a cabbage but i have never seen :: used as a prefix to a join or anywhere else in sql actually. I had a quick search around and can't find anything about its usage.

    Can you tell me what the :: does as a prefix to the built-in function fn_trace_gettable()?

    cheers

    It is not an issue with SQL 2005 and up but SQL 2000 required the usage of :: when calling system table valued UDFs. The reason you can not find anthing about :: is because as far as I know no search engine is going to treat the :: as a valid string so it is not going to return any results.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    david.murden (12/1/2009)


    Hi Jacek0,

    I might be being a cabbage but i have never seen :: used as a prefix to a join or anywhere else in sql actually. I had a quick search around and can't find anything about its usage.

    Can you tell me what the :: does as a prefix to the built-in function fn_trace_gettable()?

    cheers

    ClearTrace doesn't do it all....grouping the procedures into logical buckets 1-2 seconds, 3-4 seconds, etc. isn't worthless reporting for performance tuning πŸ˜‰

  • G33kKahuna

    SSCommitted

    Points: 1726

    ClearTrace doesn't do it all....grouping the procedures into logical buckets 1-2 seconds, 3-4 seconds, etc. isn't worthless reporting for performance tuning πŸ˜‰

    Yes it is ...

    1. If you are using sp_executesql to send in dynamic queries, this breakdown will be mostly false positive

    2. If the usage of a SP is higher, ClearTrace will bubble it up based on signature and the rest of the breakdown is just fluff ... worst case scenario just use DMV stats to do the query usage break down.

    3. 1-2 sec, 3-4 sec breakdown is not gonna tell you any story related to performance tuning ... there is more to performance tuning that these breakdowns ...

    Again, i'm mentioning freebies. Ofcourse there are commercial tools that can do more

  • JacekO

    SSCertifiable

    Points: 6347

    G33kKahuna,

    Thanks for reading the article and I am sorry you did not find it useful.

    If the tools you use always give you what you want and you never have to use pure T-SQL to get more info you might consider yourself one of the few lucky ones. As far as the tools are concerned I never found one that gave me all I needed, so from time to time I have to write a routine or two.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    G33kKahuna (12/1/2009)


    ClearTrace doesn't do it all....grouping the procedures into logical buckets 1-2 seconds, 3-4 seconds, etc. isn't worthless reporting for performance tuning πŸ˜‰

    Yes it is ...

    1. If you are using sp_executesql to send in dynamic queries, this breakdown will be mostly false positive

    2. If the usage of a SP is higher, ClearTrace will bubble it up based on signature and the rest of the breakdown is just fluff ... worst case scenario just use DMV stats to do the query usage break down.

    3. 1-2 sec, 3-4 sec breakdown is not gonna tell you any story related to performance tuning ... there is more to performance tuning that these breakdowns ...

    Again, i'm mentioning freebies. Ofcourse there are commercial tools that can do more

    On the contrary, in combination w/ avg reads, avg writes, and avg CPU this information can tell a very good story. The point being that ClearTrace like any commercial product has limitations. JacekO was kind enough to share some alternatives w/ the rest of the SQL Server community.

  • JacekO

    SSCertifiable

    Points: 6347

    Tommy,

    Thanks for stepping in in defence of the article. I am very pleased someone found it useful.

    Fortunately this is an open forum so everyone can express their opinions. It happens that some of the members will have a disagreement over some topics. I participated in some heated discussions here myself as well and observed other topics when the discussion went nasty. Unfortunately when someone's mind is made - it is very hard to 'unmake' it, sometimes it is just better to drop the subject...

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • JacekO

    SSCertifiable

    Points: 6347

    TheSQLGuru (12/1/2009)


    Well done.

    I have been doing this type of analysis for a decade now, but have never done the execution histogram like you have done with the case statements. I like that.

    It would have been nice if you had covered some of the ways to 'normalize' the executions by replacing sproc parameter values. Perhaps the next article... πŸ™‚

    Thanks for reading and I am pleased you found some new ideas in the article. I have been trying for a while to figure out what your second paragraph meant, but have to confess I can not figure it out. Do you mind elaborating a bit more...

    Thanks.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    JacekO (12/2/2009)


    Tommy,

    Thanks for stepping in in defence of the article. I am very pleased someone found it useful.

    Fortunately this is an open forum so everyone can express their opinions. It happens that some of the members will have a disagreement over some topics. I participated in some heated discussions here myself as well and observed other topics when the discussion went nasty. Unfortunately when someone's mind is made - it is very hard to 'unmake' it, sometimes it is just better to drop the subject...

    NP, it is unfortunate. Thanks again for the article! Keep up the good work πŸ™‚

    Best -

  • G33kKahuna

    SSCommitted

    Points: 1726

    On the contrary, in combination w/ avg reads, avg writes, and avg CPU this information can tell a very good story. The point being that ClearTrace like any commercial product has limitations. JacekO was kind enough to share some alternatives w/ the rest of the SQL Server community.

    Avg read, write and CPU is such a liner read in the world of performance issues. Unless you are in a fantasy world, it tells you no stories. You can have low avg CPU, reads and writes but have an incredible performance bottleneck that is CXPACKET signal or PAGEIOLATCH_XX induced. Either none of these readings will catch it or catch a false positive. We can take this topic offline if you want to.

    The point being ... the article title and build up is misleading. Its offers up very little about performance, not to mention the computation is psuedo-dynamic (or as dynamic as the traces)

    Tommy,

    Thanks for stepping in in defence of the article. I am very pleased someone found it useful.

    Fortunately this is an open forum so everyone can express their opinions. It happens that some of the members will have a disagreement over some topics. I participated in some heated discussions here myself as well and observed other topics when the discussion went nasty. Unfortunately when someone's mind is made - it is very hard to 'unmake' it, sometimes it is just better to drop the subject...

    Jacek, Giving you a hard time was not my intention. When you post an article with a title "Using Server Side Traces for Dynamic Performance Evaluation" but offer up very little in the world of performance evaluation; you got to expect challenges. Sorry you feel chastised ...

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    G33kKahuna (12/2/2009)


    On the contrary, in combination w/ avg reads, avg writes, and avg CPU this information can tell a very good story. The point being that ClearTrace like any commercial product has limitations. JacekO was kind enough to share some alternatives w/ the rest of the SQL Server community.

    Avg read, write and CPU is such a liner read in the world of performance issues. Unless you are in a fantasy world, it tells you no stories. You can have low avg CPU, reads and writes but have an incredible performance bottleneck that is CXPACKET signal or PAGEIOLATCH_XX induced. Either none of these readings will catch it or catch a false positive. We can take this topic offline if you want to.

    The point being ... the article title and build up is misleading. Its offers up very little about performance, not to mention the computation is psuedo-dynamic (or as dynamic as the traces)

    Tommy,

    Thanks for stepping in in defence of the article. I am very pleased someone found it useful.

    Fortunately this is an open forum so everyone can express their opinions. It happens that some of the members will have a disagreement over some topics. I participated in some heated discussions here myself as well and observed other topics when the discussion went nasty. Unfortunately when someone's mind is made - it is very hard to 'unmake' it, sometimes it is just better to drop the subject...

    Jacek, Giving you a hard time was not my intention. When you post an article with a title "Using Server Side Traces for Dynamic Performance Evaluation" but offer up very little in the world of performance evaluation; you got to expect challenges. Sorry you feel chastised ...

    G33kKahuna - Is it your position that server side trace information offers no value in troubleshooting performance issues? What β€œfantasy world” are you living in?

Viewing 15 posts - 1 through 15 (of 23 total)

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