Using SQL Server 2008 version 10.0.1600.22, I am seeing a problem when I collect information using Profiler. I intended to use Profiler to collect trace information on a application based on a Sharepoint Web Part module. When I turn on the Profiler trace, it breaks the application. After further testing, it looks like, as the Profiler trace starts, there are a few EventClass: 16, Attention. Which I think is a client-interrupt request or broken client connection.
So, it looks like Profiler is breaking client connections. Has anyone else seen this problem?
I haven't seen this before. Profiler should only read events from the server, not interfere with client connections. Have you tried just a server side trace to see if it might be reproduced there?
answered Nov 21, 2009 at 08:39 PM
Steve Jones - Editor ♦♦
You should not be using Profiler to monitor your server. The Profiler GUI is primarily for browsing data after it's been collected, not for active monitoring. You should set up a server side trace using the trace events and output the results to a file. You can then use Profiler to browse the data from the file after it's completed. If you're unsure how to do this, set up the trace you'd like to do using the Profiler GUI and then use the Export function to save it as a trace definition.
The reason I say this is because a server-side trace and the Profiler actually collect data in totally different ways. The Profiler can cause serious performance problems on the server where server side trace events don't. I've also seen Profiler drop data that the trace events capture.
answered Nov 24, 2009 at 10:05 AM
Grant Fritchey ♦♦
I first noticed the problem with this
Configuation_No1 Server1: SQL Server 2008 database I am tracing Workstation: my computer where I have setup client side trace Server2: location where I am writing the trace to a SQL Server 2008 table. Template: Standard Default
Results: I confirmed that while Profiler is runnning one of my applications has a major slowdown.
Configuration_No2 Server1: SQL Server 2008 database I am tracing Server1: Server Side Tracing Server1: Server Side Saving Trace to Table Template: Standard Default
Results: This configuration also causes a slowdown.
answered Nov 23, 2009 at 04:41 PM
Client connections locking by profiler happens to us too. Note its not a major slow down or anything - it's a complete halt of all connections that Finance application has to the database server. All existing instances freeze, and you can't open a new one.
Happens the instance profiler trace is turned on, and gets fixed as soon as its turned of.
SQL 2008 R2, on Windows Server 2008. Max number of connections set to 0. The application can be used by 50 people across our WAN simultaneously no problem. It's just the profiler that's causing it.
answered May 10, 2013 at 12:27 AM