Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Using Server Side Traces for Dynamic Performance Evaluation Expand / Collapse
Author
Message
Posted Wednesday, December 2, 2009 7:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
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...


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #827450
Posted Wednesday, December 2, 2009 7:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
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.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #827455
Posted Wednesday, December 2, 2009 7:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:39 AM
Points: 1,157, Visits: 3,259
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 -


Tommy

Post #827470
Posted Wednesday, December 2, 2009 9:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 31, 2013 8:04 PM
Points: 68, Visits: 278

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 ...
Post #827589
Posted Wednesday, December 2, 2009 9:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:39 AM
Points: 1,157, Visits: 3,259
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?


Tommy

Post #827610
Posted Wednesday, December 2, 2009 9:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
G33kKahuna (12/2/2009)
[quote]
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 ...


I did not feel chastised at all. I might be a bit disappointed you did not find anything useful in this article.

As I mentioned before - everyone is entitled to their own opinions and each opinion is as valuable as any other.
I can put it this way - your job is to express your opinion - my job is to accept it or ignore it. No hard feelings...

As far as the dynamic part is concerned I think you might have taken the wrong definition of the word 'dynamic'. The dynamic refers to the dynamics of the various SPs have on each other rather then about the on the spot performance measurements of the system. If you need the 'right now' (dynamic) view into the system then the DMV you mentioned are probably just fine.

For example one lesson I learned using the method described in the article is how the autogrowth of the log and data files can negatively impact the performance of SPs in extreme cases.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #827612
Posted Wednesday, December 2, 2009 11:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
Jacek,

I see that you SELECT the TraceID at the end of your Procedure - how do you turn the trace off with that technique? Does it run until C: is full?

Just Wondering,
Doug
Post #827712
Posted Wednesday, December 2, 2009 11:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
To stop the trace use sp_trace_setstatus @traceID, 0
To delete the trace definition use sp_trace_setstatus @traceID, 2

The @traceID is the ID you got when you run the code provided in the acticle.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #827715
Posted Wednesday, December 2, 2009 10:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 31, 2013 8:04 PM
Points: 68, Visits: 278

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


Tommy,

Which part of my response said server side trace is bad? infact the wait types I mentioned can be captured only on the server side ...

Sounds like you are suggesting that avg read, write and cpu are the only server side performance indicators .... looks like you got a lot to catchup on SQL Server performance tuning bud ...

Look up Andew Kelly, Paul Randel, Jimmy May and Linchi Shea articles

JacekO, I would have calibrated SQL Wait & Waiting stats for autogrows but you seem to prefer your approach ... fair enough and peace.
Post #827938
Posted Thursday, December 3, 2009 4:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 2,366, Visits: 1,845
nice article jacek!

"Keep Trying"
Post #828047
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse