Connection Pooling is a techinique used by ADO.Net to optimize database connections. To explain this you must know how an application works. For instance, an ASP Page will have a connection string in the starting of the page, which will have the details for connecting to the backend/database like SQL Server instance name, user credentails etc.
For example SERVER=SQLServer\InstanceName, portnumber; DATABASE=Pubs; UID=sa; Pwd=password
When a web page is executed, the application web server will parse through the connection string & will establish a connection to the database server. Everytime a new connection with a specific string is created, it is placed in the connection pool, which is maintained by the application server. Once the application close the connection, it remains in the application pool as a valid connection to the database server.ie., the number of connections in database server will remain the same even after the web/asp page execution. Next time if another web page requests for a new connection with the same string, it will be taken from the connection pool. This will improve the performance , by saving the time taking to establish a new connecion. The user do not require to wait for a new database connection to be established.
Connection Pooling can be tested by creating a simple asp page, as below.
1) Create an asp page with with the below sample connection string
Set cn = Server.CreateObject(“ADODB.Connection”)
cn.Open “DRIVER={SQL Server} ; SERVER=SQLServer\InstanceName, portnumber; DATABASE=Pubs; UID=sa; PWD=password; Pooling=true;”
Set cmd = Server.CreateObject(“ADODB.Command”)
Note here that the Pooling is set to true, whch enables application connection pooling.
2) If you execute the above created web page, you can see a new connection is opened to SQLServer. This can be confirmed by running sp_who command or select * from master..sysprocesses
3) Now close the web page & run the sp_who command in sql, you can see the same number of connections. This implies connection pooling is enabled & working.
4) Go ahead & restart the iis using iisreset command in the command prompt. Now run sp_who, you can see that the connection from pool is now released.
You can also mention the Connection Timeout in the string. For instance if you set Connection Timeout = 5 secs, if a connection from the pool is not established with in 5 seconds, a new connection will be open increasing the number of connections to the database server.
How to Monitor Connection Pooling
Connection Pooling can be monitored by the below options
1) Check the number is connections in sql server using sp_who
2) In Perfromance counter use, SQL Server General Statistics -> User Connections, to find the number of active connections & monitor the rate of increase.
3) In Performance counter use, .NET Data Provider for SqlServer -> all counters related to Connection Pooling.
For instance, you can see in the above screen shot, there are total 8 user connections in sql server. Out of which 3 are general/defaultdomain, here I executed 3 queries from “Management Studio”.The remaining 5 came from an application with the name “Test Web Application”. These five are Pooled connections, by configuring Pooling=true, in the connection string.
Also note that we have 2 Active Connection Pool groups & 2 Active Connection Pools here.
