Two days ago I decided to dive into connection pooling secrets. What a coincidence with Sahil Malik as he wrote blog article about his research same day J.
In addtition to his article I can describe what I tested. As I have job to do I will miss screenshotsL.
We were considering option to pass data to SQL SPs usign connection string and we wanted to know how this affects connection pooling. So I created simple winform application with a button with the following.
1: string connString = @"data source=.\SQLExpress;
2: user id=gu; password= 123;
3: Initial catalog = master;
4: Workstation ID=test_host{0}; 5: Pooling=true;
6: Connect Timeout = 60";
7:
8: for (int i = 0; i < 10; i++)
9: { 10: SqlDataReader dr = null;
11: string cmdText = "SELECT date=getdate()";
12: SqlConnection cn = new SqlConnection(string.Format(connString, 1));
13: SqlCommand cmd = new SqlCommand(cmdText, cn);
14: try
15: { 16: cn.Open();
17: dr = cmd.ExecuteReader();
18: if (dr != null)
19: { 20: while (dr.Read())
21: { 22: Console.WriteLine(dr["date"].ToString());
23: }
24: }
25: }
26: finally
27: { 28: if (dr != null) dr.Close();
29: cn.Close();
30: }
31: }
I used same SQL statement as Sahil to determine number of physical connection to database.
After executed the following code I got only one connection opened. Note that connection string is always same as I pass 1 as parameter in string.Format(connString, 1) on line 12.
Let’s change this to string.Format(connString, i). As expected I ended with 10 opened connections as connection string is different for every SqlConnection object.
Let’s undo change and use connection pooling again. We have for loop with 10 connection objects opened but only one physical connection is opened on SQL side.
Let’s start two instances of our test application and check what happens by clicking on buttons. We executed for loop once in each instance. By executing Sahil’s SQL statement we see two connections although all 20 connection has same connection string.
I opened Activity Monitor from SQL Management Studio and I was able to see the connections – everything was same except Net Address. According the documentation:
Net Address
Assigned unique identifier for the network interface card on each user's workstation. When the user logs in, this identifier is inserted in the Network Address column.
Strang… both instances are on same machine…
So as conclusion the statement from Wikipedia’s Connection Pooling page – “A Connection Pool is a cache of database connections maintained in the database's memory so that the connections can be reused when the database receives future requests for data.”
Is wrong… the pool is only on client side
As I missed the screenshots I will add another example in case I lost you somewhere above J
Connection pooling is pool of connection within instance of application! If you have two instances of same application that creates one connection to SQL Server you will have 2 conenction to the server.
If these 2 instances created 20 connections each (with same connection string within the instance) there will be 2 connections on the server because connection pooling.
If these 2 instances created 20 connections each (different connection string each) there will be 2x20=40 connections on the server because connection pooling is done only on same connection string.