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 Monday, November 30, 2009 8:30 PM
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
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.
Post #826458
Posted Tuesday, December 1, 2009 7:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 10:10 AM
Points: 30, 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...
Post #826676
Posted Tuesday, December 1, 2009 7:37 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 4,463, Visits: 6,391
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 at GMail
Post #826678
Posted Tuesday, December 1, 2009 8:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 27, 2011 1:29 AM
Points: 54, 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
Post #826707
Posted Tuesday, December 1, 2009 9:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 28, 2014 5:00 PM
Points: 68, Visits: 279
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 ...
Post #826768
Posted Tuesday, December 1, 2009 10:02 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
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.
Post #826833
Posted Tuesday, December 1, 2009 12:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:38 AM
Points: 1,157, Visits: 3,270
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

Post #826928
Posted Tuesday, December 1, 2009 12:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 28, 2014 5:00 PM
Points: 68, Visits: 279

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
Post #826958
Posted Tuesday, December 1, 2009 1:19 PM
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,

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.
Post #826989
Posted Tuesday, December 1, 2009 3:14 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:38 AM
Points: 1,157, Visits: 3,270
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

Post #827082
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse