Our method is a TCP listening transaction processor. Incoming connections are designated a thread to handle connection along with a DB connection to utilize.

Instead of pricey approach of creating new DB connection for every incoming client connection, we conserve a pool of database connections.

The database connection pool fairly configurable: min / max dimensions, growth rates, etc.

Some particulars:

  • Platform is Home windows 2003 / 2008 R2
  • DB is SQL Server 2005 / 2008 R2
  • Connection technique is ODBC
  • Programming language is C++

Finally, the question:

Because the service might be running for many several weeks with no restart, there is a real chance that a few of the database connections within the pool become invalid. I wish to have as quick a means as you possibly can to check the validity of the given connection before setting it for an incoming connection.

Presently, I actually do this by performing the easy SQL statement "Choose 123", however I have discovered that it has significant negative performance impacts when parallel execution plans are utilized.

Very briefly in code, what I am doing is:

// ... at some point we decide pool needs another connection...

// Set up database connection
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLAllocHandle(SQL_HANDLE_DBC, env, &conn);
SQLDriverConnect(conn, 0, in_str, in_len, out_str, DIM(out_str), &out_len, SQL_DRIVER_NOPROMPT);

// 'conn' is placed in DB connection pool

// ... some time later a new client connection comes in ...

// Execute simple statement to test if 'conn' is still OK
SQLAllocHandle(SQL_HANDLE_STMT, conn, &stmt);
SQLExecDirect(stmt, (SQLCHAR*)"SELECT 1;", SQL_NTS);

// If 'conn' is OK, give it to incoming connection;
// if not, get another connection from pool

Cheers,
Dork

Well the state strategy is SQLGetConnectAttr( SQL_ATTR_CONNECTION_DEAD )