Looking for cause of lots of tcp connections to sql server port 1433 in time_wait state

1

Trying to determine why I'm seeing several thousand connections to SQL Server port 1433 via netstat in the time_wait state. I know that tcp/ip connections in the time_wait state signify connections that were closed, and waiting a configurable period of time before releasing the connections in case there are delayed packets that haven't yet arrived on a socket.

Here's some background. Running SQL Server 2008 R2. I have a C++ application using ATL/OLE DB to connect to the SQL Server Database. The C++ application is making dozens of requests per second to SQL Server, but it always uses the same connection.

Looking at SQL Server Profiler and monitoring Login and Logoff events for my application, I can see periodically lots of rapid fire logins and logoffs sporadically every once in a while with my application as the Application Name. Yet setting breakpoints in my code where the connection to a database occurs, that code to connect to the DB is never executed more than once at startup. If I shut my application down completely, these login/logoff events stop.

I'm really perplexed.. I know I can adjust the tcp time_wait settings in the registry and lower the number of seconds the ports are in the time_wait state, but I'm really trying to understand why these seemingly random login and logoff events are occurring. The login and logoff events are matching up with the number of connections shown with netstat in the time_wait state..

Any suggestions at all would be greatly appreciated.

2

Disclaimer: never used this ATL/OLE DB, nor C++ in a while but a stab in the dark here. Could the issue be this ATL/OLE DB connection technology doing its own thing under the hood to keep connections alive?

Have you looked into using connection technologies other than ATL/OLE DB and see what happens? such as GitHub - microsoft/VCSamples: Samples for VC++

3

That was my thought too, although I didn't think that default settings could cause that.

I had a look at the Connection Properties and the ones that seem worth checking included:

"Connection Lifetime" / "Load Balance Timeout" allows drop/recreate I think (to allow load-balancing to re-balance I think)

Maybe also "Connection Reset"

Seems a longshot, but check "Pooling" is not false

4

Thanks for the suggestions so far. In SQL Profiler the logins are showing that they are non pooled.. but I thought that with ATL/OLE DB that connection pooling was the default unless connection pooling was explicitly turned off via the Connection String?
And I'm definitely not explicitly turning off Connection Pooling in the Connection String.

Where are you looking at the Connection Properties?

5

https://www.connectionstrings.com/kb/

6

No surprise ... but that site says "Connection pooling is enabled by default" :frowning:

You only get Login / Logoff, no actual SQL statements? (If SQL Statements then feels to me more likely that your APP is squirting a query at SQL, if no SQL and just Login/Logoff then feels more like some "Transport layer" chatter. I expect you've already covered all that though ...)