CODEWITHSUNDEEP
javasql-serverconnectionjdbctemplateapache-commons-dbcp
Alex Ciminian
Alex Ciminian

Reputation: 11508

Sleeping connections in SQL Server

Not being a database administrator (even less of a MS database admin :), I have received complaints that a piece of code I've written leaves "sleeping connections" behind in the database.

My code is Java, and uses Apache Commons DBCP for connection pooling. I also use Spring's JdbcTemplate to manage the connection's state, so not closing the connections is out of the question (since the library is doing that for me).

My main question is, from a DBA's point of view, can these connections cause outages or poor performance?

This question is related, currently the settings were left as they were there (infinite active/idle connections in the pool).

Upvotes: 1

Views: 2797

Answers (2)

Tao
Tao

Reputation: 14006

Really, to answer your question, an idea of the number of these "sleeping" connections would be good. It also matters whether this server's primary purpose is serving your application, or whether your application is one of many. Also relevant is whether there are multiple instances of your app (eg on multiple web servers), or whether it's just the one.

In my experience, there is little to no overhead associated with idle connections on modern hardware, as long as you don't reach into the hundreds. That said, looking at your previous question, allowing the pool to spawn an unbounded number of connections does not sound wise - I'd recommend setting a cap, even if you set it at a hundreds.

I can tell you from at least one painful situation with leaking connection pools, that having a thousand open connections to a single SQL server is expensive, even if they're idle. I seem to recall the server started losing it (failing to accept new connections, simple queries timing out, etc) when nearing the 2,000-connection range (this was SQL 2000 on mid-range hardware a few years ago).

Hope this helps!

Upvotes: 1

James Jithin
James Jithin

Reputation: 10565

Apache DBCP has maxIdle connections settings to 8 and maxActive settings to 8. This means that 8 number of active connections and 8 numbers of idle connections can exist in the pool. DBCP reuses the connections when the call for connection is made. You can set this according to your requirement. You can refer to the document below:

DBCP Configuration - Apache

Upvotes: 1

Related Questions