x

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?

more ▼

asked Nov 20, 2009 at 01:24 PM in Default

avatar image

Clark
23 1 1 3

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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?

more ▼

answered Nov 21, 2009 at 08:39 PM

avatar image

Steve Jones - Editor ♦♦
5.2k 79 93 87

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 24, 2009 at 10:05 AM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 23, 2009 at 04:41 PM

avatar image

Clark
23 1 1 3

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 10, 2013 at 12:27 AM

avatar image

vlad
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2170
x308
x40
x21
x4

asked: Nov 20, 2009 at 01:24 PM

Seen: 3685 times

Last Updated: May 10, 2013 at 12:27 AM

Copyright 2017 Redgate Software. Privacy Policy