November 30, 2009 at 8:30 pm
Comments posted to this topic are about the item Using Server Side Traces for Dynamic Performance Evaluation
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
December 1, 2009 at 7:37 am
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...
December 1, 2009 at 7:37 am
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 on googles mail service
December 1, 2009 at 8:05 am
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
December 1, 2009 at 9:05 am
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 ...
December 1, 2009 at 10:02 am
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.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
December 1, 2009 at 12:27 pm
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 @sqlscribeDecember 1, 2009 at 12:51 pm
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
December 1, 2009 at 1:19 pm
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.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
December 1, 2009 at 3:14 pm
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 @sqlscribeDecember 2, 2009 at 7:23 am
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...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
December 2, 2009 at 7:31 am
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.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
December 2, 2009 at 7:41 am
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
Follow @sqlscribeDecember 2, 2009 at 9:05 am
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 ...
December 2, 2009 at 9:22 am
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 @sqlscribeViewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply