question

Troy_2012 avatar image
Troy_2012 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
sql-server-2008performanceprofiler
template.jpg (55.4 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.