1

Possible Duplicate:
Find out what queries are causing the biggest amount of network traffic

We are getting over 90 Mbps from SQL Server to each web servers on port 1433.

  • We are using connection pooling.
  • We have CLR Integration enabled.
  • We are running CDC.
  • We have snapshot transactions turned on.
  • We are using SQL Server 2008 R2 SP1

But when we get SQL Profiler on the case and remove all the Login and Logout events we are seeing low activity.

Any recommendation of getting great visibility on what's going over the wire?

Or are the Login and Logout events very chatty over the network?

Community
  • 1
Robert
  • 95
  • 1
  • 7
  • 3
    The problem was already studied in some previous questions. Please look at them: Find out what queries are causing the biggest amount of network traffic and Analysing SQL Server Network I/O. Please let us know if they provide you with enough information. – Marian Nov 14 '12 at 11:48
  • 1
    I don't think is best as an answer yet - and i agree with Marian.. But a few thoughts.. 1.) Do those logins/logouts make sense to you? 2.) Are you using connection pooling? This will cut down on excessive logins/logouts 3.) If you are - are you getting good connection pool reuse? Seeing not much activity other than login/logout makes me suspect connection pooling may be at least a small part or side issue for you.. And short of those answers linked above, there isn't much to add. – Mike Walsh Nov 14 '12 at 15:56
  • After some more tweaking of the SQL Profiler we were able to discover the issue. – Robert Nov 14 '12 at 19:20

1 Answers1

1

We used Wireshark and SQL Profiler and were able to uncover an SP returning 700,000 records repetitively.

As far as we were able to tell then login and logout were part of connection pooling.

Robert
  • 95
  • 1
  • 7