SQLTeam.com | Weblogs | Forums

Does MSSQL have a Connection Pool setting?


#1

We have a reporting app that intermittently gives this connection error:

“cannot get a connection, pool error Timeout waiting for
idle object”

Are there any config settings in SQL 2014 related to connection pools?
Thanks, Jack


#2

If i'm not wrong, connecting pooling is from app side, not from the server side. There should be no setting on the server side for it.


#3

In the connection string, there is a setting to define the maximum number of connections. The details will vary depending on how you are connecting (ODBC, etc.). Another possibility, however remote, is that you are running out of worker threads. There is a setting on the SQL Server side that you can use:[code]exec sp_configure 'max worker threads' -- (Advanced option, Restart Required)

--- (1024 is the maximum recommended for 32-bit SQL Server, 2048 for 64-bit SQL Server.)
--- Zero auto-configures the number of max worker threads depending on the number of processors, using the formula (256+( -4) * 8) for 32-bit SQL Server and twice that for 64-bit SQL Server.
--- The system must be restarted in order for the new setting to take effect.[/code]Check BOL before you decide to play with this.


#4

Thanks Stephen Hendricks. This instance is 64 bit and max worker threads is set to zero, so the 2048 limit is in effect. I doubt we are exceeding that number but if a connection is failing due to max worker threads I assume an entry to MSSQL logs would be written, correct? I didn't see anything related to connection issues.


#5

I don't know the answer to that, but I doubt it. My assumption would be that the connection would fail at the client end, and thus never actually "present" to the server end, and thus the server would not be in a position to detect / log an error.

A long, long time ago ... when ASP first came out, we had severe problems if we failed to close a database connection as the core engine was not robust in detecting that a connection / session / etc. had "finished". I wonder if that might be the case for you? as you say "2048 limit is in effect. I doubt we are exceeding that number" and it sounds very reminiscent of the problem we had, way back then - but my expectation is that, these days, the core engine will better detect that a connection is no longer needed and close / re-use it.


#6

I offered the work thread notion only as a possibility. The connection pool limit, IIRC, is 100 by default. If you are running out there, I'd look for code that was not closing a connection or raise the limit through the connection string to see if that is the culprit. If it is, find and fix that.


#7

It's a long shot, since this rarely gets changed from the default of zero, but...sp_configure 'user connections' You might try to see if this configuration has been modified. It won't be but at least you can cross it off of the list....:relieved: