C# – SQLConnection pool size and active connection count in C#

After a little bit of research along with help from some of the answers here:

SELECT 1

• H2
• MySQL
• Microsoft SQL Server (according to NimChimpsky)
• PostgreSQL
• SQLite
• Hive

SELECT 1 FROM DUAL

• Oracle

SELECT 1 FROM any_existing_table WHERE 1=0

or

SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS

or

CALL NOW()

• HSQLDB (tested with version 1.8.0.10)

  Note: I tried using a WHERE 1=0 clause on the second query, but it didn't work as a value for Apache Commons DBCP's validationQuery, since the query doesn't return any rows

VALUES 1 or SELECT 1 FROM SYSIBM.SYSDUMMY1

• Apache Derby (via daiscog)

SELECT 1 FROM SYSIBM.SYSDUMMY1

• DB2

select count(*) from systables

• Informix

In general, the concern about "exhausting system resources" applies to both the application server and the database server. The more database connections you allow, the more concurrent sessions are running on the application server(s), the more RAM the application server(s) VM requires, the more demand is placed on CPUs on application servers and database servers, etc. If the queue of backlogged work gets too big, you may find yourself spending more time swapping processes on and off the CPU and scheduling tasks than in doing useful work. A maximum size on the connection pool allows you to handle an avalanche of traffic or an unexpected performance bottleneck slightly more gracefully by quickly erroring out rather than letting users time out waiting for replies that will never come.

Databases do, in general, have the ability to limit the number of connections they support. Oracle has PROCESSES and SESSIONS parameters, for example, and supports multiple connection architectures (dedicated server and shared server) to let you trade off performance against resource consumption to increase the number of concurrent connections the database can support.