connection pooling
i've been seeing a number of posts lately about ado.net connection pooling in msforums and i noticed that some are rather confused about how they work. i myself was also confused year ago about the concept until i read more about it and examined the ado.net code using reflector.
i only want to list some facts which i found along the way which can still be wrong so please comment if you think they are wrong.
- connection pool is there to eliminate the overhead of creating ado.net connections, they are created in the initial Open() invocation
- connection pool are maintained just like a hashtable with the connection string as key
- ado.net connection objects are just wrappers to unmanaged connection which is what is maintained in the pool
- after an ado.net connection is closed or disposed, the wrapped unmanaged connection object is returned to the pool
- connection pool is managed by the ado.net provider and not by sql server
- sql server maintains a limit in its concurrent connections and these are the ones used by the connection pool. if the connection pool maxcount is greater than this, further opened connections which can be available in the pool will need wait.
i was surprised though upon seeing that you can specify a connection pool timeout in the forum which i initially believed is not possible upon searching google and finding this
article. just skimmed through it but normally, you wouldn't alter how the framework behaves and the default behavior is sufficient.
[EDIT] Load Balance Timeout is kind of new to me but i dunno if the article above is bogus. Found an interesting blog post by Sahil related to this
here. i personally did not know about the sp_who2 that he said in his example until today.