13

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?

2
  • 5
    yes. It depends how and where you run it. Done the right way can be as little as a few percent extra load. May 5, 2012 at 5:57
  • 3
    Extended events can be a more lightweight option. May 5, 2012 at 8:41

5 Answers 5

11

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.

1
  • +1 even a very short profiler trace, depending on many factors, can cripple a server. May 6, 2012 at 1:05
4

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.

3

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

3
  • My server and database are installed on same machine. Now if i start the sql profiler through "Microsoft SQL management studio -> Tools-> SQL profiler" then how much degradation of performance can i expect and it based on what factors?
    – V K
    May 5, 2012 at 10:04
  • It is based on how busy your server is, how many events and how many columns your trace consists of, and your filtering. Filtering can affect in both directions - in one way it can reduce the number of rows that are returned to the UI (the part that really makes profiler hurt more than server-side trace), but on the other hand if you have a poorly chosen filter (e.g. textdata LIKE '%foo%') it can have the opposite effect. There are far too many variables to provide some magic expectation, you're just going to have to try it. You really should be using a server-side trace, though. May 6, 2012 at 1:04
  • Try this link if you aren't able to access the original.
    – Marc Lopez
    Jun 27, 2018 at 12:58
3

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.

0

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.

1
  • My server and database are installed on same machine. Now if i start the sql profiler through "Microsoft SQL management studio -> Tools-> SQL profiler" then how much degradation of performance can i expect and it based on what factors?
    – V K
    May 5, 2012 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.