asked
Identifying performance issues using SQL Server Profiler 2008 r2
Dear All I read A great article to how identify and eliminate SQL Server performance issues in Stress test
http://vyaskn.tripod.com/analyzing_profiler_output.htm The good thing in the article described a trace template that captures the following event classes: ![alt text][1] [1]: /storage/temp/756-template.jpg 1-Are there an extra events need to capture . 2-The template for SQL server 2000 , when I import the template I could n't find it in template list when I create new trace, Is there antother way to do that ? can I convert the templte so it will work with sql server 2008,How about the SP do I need to change it to work with sql server 2008. 3-Any new ida to Identify performance issues? Thanks in advance
That's a pretty heavy trace. I wouldn't suggest running that in production or it might impact performance all on it's own. This is especially true since you're capturing the statement completion events. These things can be extremely resource intensive. I would only use them if I absolutely had to in order to troubleshoot a very specific problem. Also, that article suggests just running all this through the Profiler GUI. That is a bad idea. The profiler GUI has a different buffering mechanism than a server-side trace and it can seriously hurt performance of the system you're running it against. Since you're working in 2008 or better, instead of capturing all that data using trace events, set up the same thing using extended events. They have much less impact on the system. Plus, if you do want to capture something like the statement level events, you can put filters on extended events that actually reduce performance impact. [Here's the books online introduction to extended events.][1] For more ideas on performance tuning, I suggest picking up a [copy of my book.][2] [1]:
http://msdn.microsoft.com/en-us/library/bb630354(v=SQL.105).aspx [2]:
http://www.amazon.com/Server-2012-Query-Performance-Tuning/dp/1430242035/ref=sr_1_1?ie=UTF8&qid=1364727568&sr=8-1&keywords=fritchey
14 People are following this question.
