Does SQL profiler affect server performance?

Question

I am facing a problem where the sql server 2008 fails for some high load. I need to find that load case and need to optimize the code so that it can handle the load. I have found on the internet that SQL profiler can be used to track the database interactions which can later be analyzed to find the exact point where the problem occurs. I have also found as to how we can start the sql profiler. But now my question is that by starting the sql profiler am i going to affect the server performance?

Answer 1

SQL Server Profiler is a GUI that utilizes SQL Server Trace through the client-side. Due to this nature, yes you can potentially see a mild to severe performance impact depending on the environment.

SQL Server Profiler is suited mainly for a quick glimpse at what is happening on the server (provided the database server can handle the extra latency). It is not intended to be run for long periods of time. For longer traces, use a server-side trace or Extended Events.

Answer 2

Yes, SQL Server Profiler does affect performance.

When you trace using the GUI, all events are synchronously processed and filtered, so factors such as server load, network latency, which events you collect, and even where the collected events are stored, all contribute to overhead.

As other answers have suggested, using a server-side trace instead of the GUI is preferred. However, this method is deprecated, and Extended Events should be used instead. (Note: even using Extended Events, which are more lightweight than a trace, it's still possible to add an undesirable amount of load to the server -- see the list in my second paragraph to think of how to minimize overhead when setting up an event session.)

In the case of a heavily-loaded server, you need to be very careful about the information being collected, as the measurement process itself may affect the problem being investigated.

Answer 3

If you use server side tracing, the impact on performance can be minimized.

Answer 4

Everything that the previous posters have said is correct. Profiler is an application that, at one point, used DMO and now uses SMO to capture trace events. This adds an enormous amount of overhead compared to simply using a server side trace.

SMO is an API that adds an additional "tier" to the tracing process along with a client application, Profiler. A server side trace will read trace events directly from a transaction queue, the resulting overhead of which is negligible.

I've been able to run server side traces in SQL Server environments that process 10,000+ transactions per second. Using Profiler in those same environments will bring the database instance to its knees. In any production environment, if you need to trace database activity always use a server side trace. Profiler will kill the database instance.

Answer 5

SQL Server Profiler works in two steps: first you tell it to log all the queries. Then you run some typical transactions (or just let it log against production). This creates a "test load". Then you do the actual profiling (analysis) using the test load.

The logging isn't very expensive, especially compared to the cost of the queries themselves.