Profiler and broken client connections

Question

question

Clark asked Nov 20, '09

Profiler and broken client connections

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?

sql-server-2008 performance profiler trace trace-events

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-11-21T20:39:02Z

Steve Jones - Editor answered Nov 21, '09

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?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-11-23T16:41:02Z

Clark answered Nov 23, '09

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-11-24T10:05:23Z

Grant Fritchey answered Nov 24, '09

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2013-05-10T00:27:35Z

vlad answered May 10, '13

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question