• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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?
0
YZlat
Asked:
YZlat
  • 4
  • 3
  • 2
1 Solution
 
BrandonGalderisiCommented:
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.
0
 
Som TripathiDatabase AdministratorCommented:
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


0
 
YZlatAuthor Commented:
How can I diagnoze what's causing high CPU usage without significantly affecting the server performance?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BrandonGalderisiCommented:
Profiler won't SIGNIFICANTLY affect performance.  Most estimates say 5-10%.  
0
 
YZlatAuthor Commented:
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?
0
 
Som TripathiDatabase AdministratorCommented:
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

0
 
BrandonGalderisiCommented:
Server side traces give you the most inclusive picture.  It's not the profiler application itself that causes the overhead, it's the recording of the events.  Whether that is done from profiler running locally on the SQL server or remotely, won't change that (much).  The downside of using a local client app is that under high load you won't be able to capture 100% of events.  That makes it not as useful.  

I mostly capture SQL Batch Complete and RPC event complete but I have a set of complex queries which, once loaded into SQL Server, allow me to extract data samples from the profiler data.
0
 
YZlatAuthor Commented:
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.
0
 
BrandonGalderisiCommented:
is it sqlservr.exe using the CPU?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now