InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 19:17:24 GMT20http://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote][b]G33kKahuna (12/3/2009)[/b][hr][quote]Your arrogance is unbecoming. Perhaps we should take this offline[/quote]Jacek's article was debate worthy. I believe he and I don't see eye to eye on the evaluation approach .... he did clarify his position as well.You jumped in the middle from nowhere with a pitbull attitude. You lacked skills to support your own argument or counter mine, not to mention lack of comprehension. Like I said before you offered, I would be more than happy to debate you offline but after your response from yesterday; it would be a waste of time .... cheers[/quote]That was not my intention and if I insulted you in anyway, please accept my apologies. I found the article insightful and was merely trying to support the author for his contribution. Feel free to PM me offline.Updated this post to include an example; using the information below we were able to hone in on Procedure_C; closer inspection of the execution plan in the server side trace revealed a missing index; i.e.Object Name # of Executions Avg_Reads Avg_Writes Avg_CPU Avg_Duration Max_Duration Procedure_A 3002 6830 0 234 1 3Procedure_B 2925 882 0 26 0 3Procedure_C 1835 10201 0 246 2 51Object Name &lt; 1 1 - 2 Seconds 3 - 5 Seconds 5 + SecondsProcedure_B 2234 512 134 0Procedure_A 2285 510 118 0Procedure_C 1032 418 235 150Thu, 03 Dec 2009 17:49:20 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote]Your arrogance is unbecoming. Perhaps we should take this offline[/quote]Jacek's article was debate worthy. I believe he and I don't see eye to eye on the evaluation approach .... he did clarify his position as well.You jumped in the middle from nowhere with a pitbull attitude. You lacked skills to support your own argument or counter mine, not to mention lack of comprehension. Like I said before you offered, I would be more than happy to debate you offline but after your response from yesterday; it would be a waste of time .... cheersThu, 03 Dec 2009 16:45:45 GMTG33kKahunahttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote][b]G33kKahuna (12/2/2009)[/b][hr][quote]G33kKahuna - Is it your position that server side trace information offers no value in troubleshooting performance issues? What “fantasy world” are you living in?[/quote]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 articlesJacekO, I would have calibrated SQL Wait & Waiting stats for autogrows but you seem to prefer your approach ... fair enough and peace.[/quote]G33kKahuna - We are talking in circles...I merely supported the analysis of server side trace data as a tool to troubleshoot performance issues; not the end all be all. Your arrogance is unbecoming. Perhaps we should take this offline.Thu, 03 Dec 2009 08:32:45 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspxnice article jacek!Thu, 03 Dec 2009 04:34:28 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote]G33kKahuna - Is it your position that server side trace information offers no value in troubleshooting performance issues? What “fantasy world” are you living in?[/quote]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 articlesJacekO, I would have calibrated SQL Wait & Waiting stats for autogrows but you seem to prefer your approach ... fair enough and peace.Wed, 02 Dec 2009 22:22:01 GMTG33kKahunahttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspxTo stop the trace use sp_trace_setstatus @traceID, 0To delete the trace definition use sp_trace_setstatus @traceID, 2The @traceID is the ID you got when you run the code provided in the acticle. Wed, 02 Dec 2009 11:58:59 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspxJacek,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,DougWed, 02 Dec 2009 11:51:46 GMTDouglas Osborne-456728http://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote][b]G33kKahuna (12/2/2009)[/b][hr][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 ...[/quote]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.Wed, 02 Dec 2009 09:24:40 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote][b]G33kKahuna (12/2/2009)[/b][hr][quote]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.[/quote]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)[quote]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...[/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 ...[/quote]G33kKahuna - Is it your position that server side trace information offers no value in troubleshooting performance issues? What “fantasy world” are you living in?Wed, 02 Dec 2009 09:22:04 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote]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.[/quote]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)[quote]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...[/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 ...Wed, 02 Dec 2009 09:05:48 GMTG33kKahunahttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote][b]JacekO (12/2/2009)[/b][hr]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...[/quote]NP, it is unfortunate. Thanks again for the article! Keep up the good work :)Best -Wed, 02 Dec 2009 07:41:03 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote][b]TheSQLGuru (12/1/2009)[/b][hr]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... :)[/quote]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.Wed, 02 Dec 2009 07:31:55 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspxTommy,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...Wed, 02 Dec 2009 07:23:10 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote][b]G33kKahuna (12/1/2009)[/b][hr][quote]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 ;-)[/quote]Yes it is ... 1. If you are using sp_executesql to send in dynamic queries, this breakdown will be mostly false positive2. 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[/quote]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.Tue, 01 Dec 2009 15:14:13 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspxG33kKahuna,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.Tue, 01 Dec 2009 13:19:20 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote]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 ;-)[/quote]Yes it is ... 1. If you are using sp_executesql to send in dynamic queries, this breakdown will be mostly false positive2. 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 moreTue, 01 Dec 2009 12:51:38 GMTG33kKahunahttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote][b]david.murden (12/1/2009)[/b][hr]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[/quote]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 ;-)Tue, 01 Dec 2009 12:27:22 GMTTommy Bollhoferhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspx[quote][b]david.murden (12/1/2009)[/b][hr]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[/quote]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.Tue, 01 Dec 2009 10:02:22 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspxJacekO,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 signatureCheers ...Tue, 01 Dec 2009 09:05:21 GMTG33kKahunahttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspxHi 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()?cheersTue, 01 Dec 2009 08:05:32 GMTdavid.murdenhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspxWell 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... :)Tue, 01 Dec 2009 07:37:51 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspxThanks 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...Tue, 01 Dec 2009 07:37:18 GMTryan.harthttp://www.sqlservercentral.com/Forums/Topic826458-1494-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Performance+Tuning/68419/">Using Server Side Traces for Dynamic Performance Evaluation</A>[/B]Mon, 30 Nov 2009 20:30:55 GMTJacekO