<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Jacek Osuchowski
»
Using Server Side Traces for Dynamic...
23 posts, Page 1 of 3
1
2
3
»
»»
Using Server Side Traces for Dynamic Performance Evaluation
Rate Topic
Display Mode
Topic Options
Author
Message
JacekO
JacekO
Posted Monday, November 30, 2009 8:30 PM
SSC-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
ryan.hart
ryan.hart
Posted Tuesday, December 01, 2009 7:37 AM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, May 05, 2013 1:28 AM
Points: 30,
Visits: 74
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
TheSQLGuru
TheSQLGuru
Posted Tuesday, December 01, 2009 7:37 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 3,678,
Visits: 5,177
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
david.murden
david.murden
Posted Tuesday, December 01, 2009 8:05 AM
Valued 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
G33kKahuna
G33kKahuna
Posted Tuesday, December 01, 2009 9:05 AM
Valued Member
Group: General Forum Members
Last Login: Friday, May 31, 2013 7:22 AM
Points: 68,
Visits: 276
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
JacekO
JacekO
Posted Tuesday, December 01, 2009 10:02 AM
SSC-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
Tommy Bollhofer
Tommy Bollhofer
Posted Tuesday, December 01, 2009 12:27 PM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 1,155,
Visits: 3,166
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
G33kKahuna
G33kKahuna
Posted Tuesday, December 01, 2009 12:51 PM
Valued Member
Group: General Forum Members
Last Login: Friday, May 31, 2013 7:22 AM
Points: 68,
Visits: 276
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
JacekO
JacekO
Posted Tuesday, December 01, 2009 1:19 PM
SSC-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
Tommy Bollhofer
Tommy Bollhofer
Posted Tuesday, December 01, 2009 3:14 PM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 1,155,
Visits: 3,166
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 »
23 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
