If you are new to SQL Server you may not know about connection pooling, a topic I have been returning to quite a bit lately. Pooling may be turned on and off in the database connection string. However, if the connection string is not properly formatted it can end up in an unknown state.
There is nothing you have to do to turn on connection pooling in your connection string because it is enabled by default. You do have to include the clause Pooling=false; in your connection string if you want to disable it.
You can also specify pool size properties if you use connection pooling by including phrases for min and max connection pool size parameters. The default is min pool size=0; max pool size=100; . If you choose to specify either min or max pool sizes you must also include pooling=true; in your connection string. Otherwise ado.net does not enable connection pooling.
In my experience it is best to not specify the min or max size. Specifying those values will keep the pool open with at least the min number of connections, even if they are not needed, until the applications ends. Since an app pool is created for every unique connection string, you can end up with app pools hanging around for a long time for databases that are not every active.
If you do not specify the min number of connections on an app pool, when no connections are used from the pool for a given period of time the pool is closed along with the associated TCP ports, returning them to be used for another active database connection pool. This means that when a new connection to the now defunct pool is needed, a new pool must be established.
If you have a large number of databases on a database server, then you can experience a shortage of TCP ports to connect to the server by keeping pools open. If you have a number of servers, and maybe even a large number of databases, your client application may be starved for connection pools if you specify a min pool size.
Why do I take the time to write about connection pooling once again? Because if you don’t get it right your application performance will be degraded a large amount. I recently put together a connection stress application and found that by using connection pooling I was able to execute 10 times as many queries, using a new connection for each query, than I could without connection pooling. Moreover, after the test was executed, I had 100 times more TCP ports abandoned after my non-pooled test than I did after my pooled test.
Get your pooling right…it can make all the difference in the world for the performance of your application.
Cheers,
Ben