29. January 2010 12:16
Hi, my name is Satalaj. Here, I'm going to explain what is connection pool with solid proof.
Connection pool as name suggest its a pool of Established connections.
When we are closing the connection in finally or in try catch block that doesn't mean we are closing Established connection with your db server.
Closing connection means notifying the application about that connection is free for future request.
Here, I will tell my connection string to use min pool size = 1 and max pool size = 2.
As soon as pool gets initialized it will establish only one connection with SQL server db.
You can use below query to test how many connections are established with your MS SQL db server. I established the connection with asptest DB.
select db_name(dbid) as DataBaseName , count(dbid) as
NoOFConnections , loginame as LoginName
from sys.sysprocesses where dbid > 0 and db_name(dbid) = 'apitest'
group by dbid,loginame
Here is screen shot of Established connection.
I fired a command netstat -b from my command prompt and found that WindowsApplication1.VShost.exe with process ID 6140
Established one connection with MS SQL server IP 192.168.1.1.28 on port 1433.
My application is running and it has processed his task and I have closed the connection. However, you can see the connection is Established
and it will stay established, still the life of application. If I colse the application, I will not see any connection with my DB APITEST.
TCP smartmirror:2096 192.168.1.28:ms-sql-s ESTABLISHED 6140
[WindowsApplication1.vshost.exe]
In my application I have one established connection. What will happen If I get 2 requests simultaniouesly?
Ans. The application will establish another connection to DB server as my Max pool size is 2 and that new connection will serve the request.
What if I get 3 or 4 simultaneous requests?
If there is no free connection available in connection pool the application will throw an error
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
What happens when I set Min pool size = 10 in my connection string ?
The application will establish 10 connection with DB.
You can see it by using query above and at client / server side, you can execute netstat -a command to know
which are the ports participates in this communication. You can see how my application is talking to sql server using ports ranging from 3979 to 3988.
TCP smartmirror:3979 192.168.1.28:ms-sql-s ESTABLISHED
TCP smartmirror:3980 192.168.1.28:ms-sql-s ESTABLISHED
TCP smartmirror:3981 192.168.1.28:ms-sql-s ESTABLISHED
TCP smartmirror:3982 192.168.1.28:ms-sql-s ESTABLISHED
TCP smartmirror:3983 192.168.1.28:ms-sql-s ESTABLISHED
TCP smartmirror:3984 192.168.1.28:ms-sql-s ESTABLISHED
TCP smartmirror:3985 192.168.1.28:ms-sql-s ESTABLISHED
TCP smartmirror:3986 192.168.1.28:ms-sql-s ESTABLISHED
TCP smartmirror:3987 192.168.1.28:ms-sql-s ESTABLISHED
TCP smartmirror:3988 192.168.1.28:ms-sql-s ESTABLISHED
It proves that I have 10 Established connections with SQL server and my application.
when I tell the connection object to close the connection, It only notifies the application about that "free connection", which can be used to serve next request.
When I tell connection object to open a connection I get a free connection form connection pool.
To know more about, how clients talks to the server refer http://revenmerchantservices.com/post/2010/01/25/client-server-communication.aspx
You can have multiple pools in applications. Of Course, there is a limit as there is limits on available ports ranging from 0 to 65535.
Its best practice not to have connection string opened in infinite time to serve the request. If you let the connection opened for infinite loop
your application will not be able to serve other requests. you can try it yourself by setting min and max pool size of your connection string.
By default min pool size is 0 and max pool size is 100.
Conclusion: I told connection object to open a connection with SQL DB server means I'm telling connection object to setup actual connection with DB server?
No, I'm telling the connection object to get the already established free connection from connection pool.
I hope you understood the location of Connection Pool and its importance.
Satalaj.