asked on

SQL Server 2005 Profiler Trace

Would running Profiler trace on our SQL 2005 Server affect either the SQL Server Service and the SQLAgent?

If not, could it have negative impact on the database performance in any other way?

BrandonGalderisi

Running a profiler trace (either server side or client side via profiler) will effect performance. That is why it is important to not do it more than necessary.

Som Tripathi

This is recommended that you should run the SQL Profiler client in a different host. The reason is that SQL Profiler client itself would eat some CPU and memory resources.

Definitely it has impact on overall SQL Server performance. If you have to run it for the same server for a long time, run a Server-side trace. For few hours, this is ok to use SQL Server Profiler client.

Below can be used for reference - (A clear detail about profiler by Brad)
http://www.sql-server-performance.com/tips/sql_server_profiler_tips_p1.aspx

YZlat

ASKER

How can I diagnoze what's causing high CPU usage without significantly affecting the server performance?

BrandonGalderisi

Profiler won't SIGNIFICANTLY affect performance. Most estimates say 5-10%.

YZlat

ASKER

so if I run a profiler trace from my client computer, will it be a problem?

Also which events should I include in my trace to analyze CPU usage?

Som Tripathi

It will be better if you run from different host.

Please read the below article which is far better than our one-liner answers -
http://www.sql-server-performance.com/tips/sql_server_profiler_tips_p4.aspx

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

YZlat

ASKER

Brandon, I am concerned because on database server Processor time is at 100% and I am worried that something might happen if I run Profiler trace from my client machine.

BrandonGalderisi

is it sqlservr.exe using the CPU?