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
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

Group: General Forum Members
Points: 874 Visits: 615
Comments posted to this topic are about the item Using Server Side Traces for Dynamic Performance Evaluation

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

ryan.hart
ryan.hart
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 75
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
TheSQLGuru
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16732 Visits: 8597
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

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
david.murden
david.murden
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 141
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
G33kKahuna
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 313
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
JacekO
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

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

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Tommy Bollhofer
Tommy Bollhofer
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

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

Tommy

Follow @sqlscribe
G33kKahuna
G33kKahuna
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 313

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
JacekO
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

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

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Tommy Bollhofer
Tommy Bollhofer
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

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

Tommy

Follow @sqlscribe
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