Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
4  
yes. It depends how and where you run it. Done the right way can be as little as a few percent extra load. –  Mitch Wheat May 5 '12 at 5:57
3  
Extended events can be a more lightweight option. –  Martin Smith May 5 '12 at 8:41
add comment

migrated from stackoverflow.com May 5 '12 at 13:30

This question came from our site for professional and enthusiast programmers.

5 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
+1 even a very short profiler trace, depending on many factors, can cripple a server. –  Aaron Bertrand May 6 '12 at 1:05
add comment

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.

share|improve this answer
    
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 '12 at 10:03
add comment

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

share|improve this answer
    
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 '12 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. –  Aaron Bertrand May 6 '12 at 1:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.