In this video
will try to understand
what exactly is ADO.NET connection pooling
and will also iterate
through some important points around connection pooling
so when we use
ADO.NET in our
C# application or VB.NET application
first thing is we create a connection object, we open it
then we do all the operations like
creating the command object getting
data set, data reader or probably
just doing execute non query
whatever it is and after that we
we close the connections object
now this creation of the connection object and opening of the connection object
is quite intensive
in other words
when you say open the connection object
it opens up socket
some kind of hand shacking happens
The connection string is parsed if the format is proper or not
The authentication mechanism is
is executed
It’s a window authentication we will try to authenticate from the windows ADS
and lot of other series of steps will happen
before this connection object gets connected to the
SQL Server database or to the RDBMS
Once the connection object is opened
you will do the operation and you will close it
this creation of the connection object is quite intensive
and you would like to avoid this
whole thing again and again right
Open of the connection object
do all the series of steps
do the operation
close the object
but when I say close
don’t take this connection object and go for garbage collection
don’t remove this object from memory
cash it so that
next time when somebody says connection.open
you would like take from the pool
rather than going and executing all of these
intensive steps
As soon as you open the connection object it goes through all those series of steps
opens up the socket does hand shacking
creates a connection object
executes the SQL commands whatever it is
after that rather than going
to the garbage collection
you would like to go and put this object into a connection pool
Connection pooling means
once the connection object is opened
rather than going and recreating the object again and again
what ADO.NET does is, it actually takes up the connection object and puts into a
thing called as pooler
In this pooler
the object will be catched and later if somebody goes and says connection that open
It will not go through the series of steps but it will rather take from pool and start executing your SQL
Let’s try to demonstrate connection pooling
If I have to open up a connection I need to have
a connection string
Create a connection string here
At the left hand side I have a nice database here is called as my custdb
Take the connection string
from this my custdb here
so let me right click and
I will take up this connection string
let me just press enter and indent it
In the connection string itself it has specified something called as pooling equal to
I will go and pass it over here
point number 1 to remember
By default
connection pooling
is enabled
Connection pooling is enabled
by default in ADO.NET
In order to demonstrate that
I will
run this code this opening and connection of a code of the code inside a big for loop
and I will create a for loop here
and this for loop runs
1000 times
and
For loop just opens up a connection
I will not do any operation at this moment
and will close a connection
The connection pooling is enabled by default
This oconnection.open
should not create a new connection object, it should
take the same connection object from the pool
Once this connection object opens
I will just put a thread.sleep here
so that I can see the output
and I will
do a simple console.writeline to
just see that how many
connections opened
As soon as you do a control F5
it will start executing it will wait for
100 milliseconds over here again
Open up the new connection
Even if this for loop is running 1000 times
I should not see to many connection object is just getting created
it should use the same connection object from the pool again and again
rather than recreating it
In order to see the internal of connection pooling
we need to use
performance counters
In case you are new to performance counters
To go to the best practices video section and see the first
video which talks about what is
performance counters and how to use perfmon
In this video I will not talk about
how to use performance counters and how to use perfmon
We are to going use performance counters
What is happening internally
Let me invoke
the perfmon tool
and
Remove all the counters for now
and start running this applications
He has started opening lots of connections
and
add
.NET
SQL
client performance counter
I have something called as.NET data provider for SQL Server
Add the counters of this. why? because I am using
the SQL connection object which is related to SQL Server
and I will go and use this
console application
2 which is running.
Click on add here
You can see lots of values
Lots of things over here on the screen
and I will explain you step by step
but the first thing you will see here is
look at the last two things
At the side this running and
we have lots of performance counters
which is monitoring this application
Close this
because not need at this moment
We have collected some data lets try to understand this data
look at last two entry over here
SoftConnectsPerSecond
and SoftDisconnects per seconds
What exactly is this SoftConnects and what exactly is this Hardconnects
When your application connects to the database
When it actually connects to the database that is called as a Hardconnect
When you do a connection.close and if you actually disconnects from the actual database
it is called as a HardDisconnect
If this connection object is
created from the pool
it is called as a SoftConnect
and if the connection object is return back to the pool after you do a close
it is called as SoftDisconnect
If you see lot of SoftConnects and SoftDisconnects
that means pooling is happening
If you see lot of HardConnects and HardDisconnects that means pooling is not happening you are actually hitting the SQL Server
If you go back to our data
the perfmon has captured
It is saying SoftConnectsPerSecond is 9 and SoftDisconnectsPerSecond is 9
It is actually taking the object from the pool
NumberOfActiveConnection is 1 Active
means basically as a for loop was running right we had only one connection opened at given moment of a time
You can see the numberOfActiveConnection is 1
and NumberOfPooledConnection is 1
It was using object from the pool
and there is no non-pooled connections
We have no objects which were not used from the pool
objects were used from the
it is saying NumberOf ActiveConnectionPool is 1
I can see numeberOfActiveConnection pool is 1
Lot of SoftConnects and disconnects are happening that means connection pooling is enabled
so the first thing remember is
connection pooling is enabled
by default in ADO.NET
Now disable the connection pooling and
If we disable the connection pooling
We should see lot of HardConnects and
HardDisconnects happening
In order to
disable connection pooling you have to say pooling is equal to
false
This will disable connection pooling
I will run this application
Go back to our perfmon tool
The data will not get refresh automatically you have to remove and again add the counters
let me go ahead and add counters
In case you are new to performance counter and perfmon
please go ahead and see the video which we have
in our best practices section
which help you to understand what exactly is performance counters
Take this SQL Server let me take console application
Add it here
lot of HardsConnects and Hard Disconnects are happening
The ADO.NET.open command is actually connecting to SQL Server
Second thing is there is no Activeconnection Pools it is zero you can see here Number of ActiveConnectionPool is equal to 0
NumberOfNonPooledConnection is equal to 1
Things are happening, not happening from the pool but it is non pool
It will created from scratch
The first point to remember is
by default
connection pooling is enable and in case you want to disable it have to say pooling is equal to false
This is a very important point to remember
because the first point is happening by default
You never have to worry about connection pooling
The second important point to remember
Every unique connection string
will have a connection pool
I have this string connection
string 1 here
and let me go and create a connectionstring
I have the first connectionstring and I have a one more connectionstring here called as connection string 1
Remove pooling equal to false I want the pooling to be enabled right
now
both of these connection string are absolutely identical
If I say connectionstring.open this or if I say connectionstring.open this
it will take the connection object from the pool
The second connection string make it
localhost
Both of these connection strings are connecting with the same computer they are connecting to the same database
One has the actual PC name and other one has localhost
I will again open one more connection here
oConnection1
and for this I will use
connection string 1
And afterwards I will close it
so oConnection one.close
I will do a console.readline here
ADO.NET will go and
create one pool
If I start my perfmon and if I start
monitoring I should see that there are
two connection pools enabled
I have both of this connection
I have done a console.readline
Remove all the counters again as I have told that
perfmon does not do a auto refresh, we have to go and add the counters again
I will say here
.NET data provider for SQL Server
I want to measure connection
pooling with respect to
console application
NumberOfActiveConnection are 2 why because I have said
obj collection 1. Open and objconnection dot open you can see there are 2 active connection
there are 2 pool there are 2
pool connections
so the second point to remember is
for every unique connection string pool will be created
If you have the same connection string
then the same pool will be used
let say
if
the same connection string was used here
I will use connection string here
oConnection as well as oConnection1
is pointing towards same connection string
now let me go ahead and
again remove all the
counters let me add it
Go to .NET
SQL Server here go to console application add
NumberOfActiveConnection are 2
that is right NumberOfActiveconnection not ConnectionPool NumberOfActive connection are 2
The active connection pool is only 1
If the connection string changes with slightest value
even though you are connecting to same database even though you are connecting to the same computer
it will created new connection
This what we have to remember as developers
Try to use the same connection string across your project
so that connection pooling is use to it’s maximum
this is the second point to remember
The first point to remember was
connection pooling is enabled by default
the second point to remember is
for every unique connection string a pool will be created