SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Server Side Traces for Dynamic Performance Evaluation


Using Server Side Traces for Dynamic Performance Evaluation

Author
Message
JacekO
JacekO
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2593 Visits: 617
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.

JacekO
JacekO
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2593 Visits: 617
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... Smile


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.

Tommy Bollhofer
Tommy Bollhofer
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7000 Visits: 3359
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 Smile

Best -

Tommy

Follow @sqlscribe
G33kKahuna
G33kKahuna
SSChasing Mays
SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)

Group: General Forum Members
Points: 640 Visits: 313

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
Tommy Bollhofer
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7000 Visits: 3359
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

Follow @sqlscribe
JacekO
JacekO
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2593 Visits: 617
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.

Douglas Osborne-456728
Douglas Osborne-456728
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 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
JacekO
JacekO
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2593 Visits: 617
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.

G33kKahuna
G33kKahuna
SSChasing Mays
SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)

Group: General Forum Members
Points: 640 Visits: 313

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 ... Hehe

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.
ChiragNS
ChiragNS
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11269 Visits: 1865
nice article jacek!

"Keep Trying"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search