too many connections, slow for remote users

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace

too many connections, slow for remote users

Postby vincentj » Fri Sep 02, 2011 11:40 pm

When linking a database to the repository, SQL Source Control is opening hundreds of TCP connections. I monitored it using TCP View, and during the linking process it opened over 1700 new connections (the green lines are new connections in the last 5 seconds):

Image

This is causing major problems for our remote users, because our VPN limits the number of simultaneous connections as part of its threat management (to prevent DoS attacks). The result is that a database that takes 3 minutes to link inside the office takes 3 hours or more (!!) when connected through the VPN.

Is there anything we can do about this? Thanks.
vincentj
 
Posts: 36
Joined: Thu Feb 17, 2011 10:20 pm

Postby Brian Donahue » Mon Sep 05, 2011 9:55 am

This doesn't sound like the design of SQL Source Control to me. Actually it doesn't make any connections to the network as far as I can recall except maybe for licensing and check for updates -- it's just invokes the source control system (TFS, etc) to request and update files.

If you do netstat -a -b from a command-line, can these sockets be directly associated with ssms.exe?
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby vincentj » Tue Sep 06, 2011 4:04 am

Ah, looking at this more closely has made me notice something.

I ran TCPView and netstat together to compare the results. I saw that one particular port was reported as belonging to SSMS by netstat, but was showing up as a [System Process] according to TCPView.

What's happening is that the port for SSMS is continuously incrementing. When it changes from e.g. 50000 to 50001, then port 50000 gets changed to a [System Process] with the state TIME_WAIT. If I turn the update speed to 1 second, sometimes I see a new port open up for SSMS with the status SYN_SENT which quickly changes to ESTABLISHED. The old port goes from ESTABLISHED to FIN_WAIT1 briefly before changing to TIME_WAIT under [System Process].

All of these connections are to our Subversion server, by the way.

Is it constantly opening and closing connections? (Sorry, I'm not a network guy, I don't know how to interpret this data.)
vincentj
 
Posts: 36
Joined: Thu Feb 17, 2011 10:20 pm

Postby Brian Donahue » Tue Sep 06, 2011 9:30 am

Is there anything in the help for TCPView?
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Wed Sep 07, 2011 11:56 am

Apparently TIME_WAIT connections can be sockets that are closed, but waiting around a few extra seconds to ensure they've got all of the data. I'd say this is pretty typical for HTTP 1.0 web servers, if that's the agreed protocol then you can minimize connections by configuring the client and server to use HTTP 1.1 rather than 1.0.

If the server uses HTTP 1.0, then a dedicated connection is used for each HTTP request. With 1.1, connections are re-used for multiple requests. I would assume in this day and age, though, that all HTTP web servers are using 1.1.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Source Control 2

Who is online

Users browsing this forum: No registered users and 0 guests