question

Clark avatar image
Clark asked

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-2008performanceprofilertracetrace-events
10 |1200 characters needed characters left characters exceeded

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

Steve Jones - Editor avatar image
Steve Jones - Editor answered

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 characters needed characters left characters exceeded

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

Clark avatar image
Clark answered

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 characters needed characters left characters exceeded

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

Grant Fritchey avatar image
Grant Fritchey answered

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 characters needed characters left characters exceeded

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

vlad avatar image
vlad answered
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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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