Monitoring Connection Pooling

[Antuan Kinnard]

Hi All,

Question #1:
I am attempting to write a connection pool on an SQL Server that doesn't
seem to work, according to Profiler and Performance Monitor using 'User
Connections' counter(not the buggy '.NET CLR Data' counter). I would
like for there to be 10 connections established at the beginning(hence
Min Pool Size=10), however that doesn't seem to be happening. When
using Profiler and Performance Monitor, it shows that I only get 1
connection for every time I open a connection. I thought that the first
time I open the connection, it should open 10 connections. Can someone
explain why it's not creating those 10 connections the first time I open
the connection using the following connection string?

Connection string: "Database=myDatabase;Server=myServer;Max Pool
Size=100;Min Pool Size=10;Integrated Security=False"

Question #2:
Everytime I close one of these connections, it opens another one. Why
is that?

 

[William \(Bill\) Vaughn]

I'm a bit confused. Are you trying to write your own Connection pooling
mechanism or simply write an application that uses the existing SqlClient
connection pooling mechanism?
If the latter, the Min pool size does not open 10 (n) connections when you
open the first connection. The mechanism opens connections as they are
needed but does not close the DB connection until more than 10 are opened.
That is, when you try to open a connection through the connection pool and
a connection in the pool is idle, it is reused otherwise another connection
is opened and maintained in the pool. This means that in a normal situation,
you close connections as you finish using them and they're returned to the
pool (and remain open). If your demand for connections does not exceed the
availability of existing pooled idle connections, the number of pooled
connections does not grow (and it shouldn't). Many heavily used ASP systems
don't use more than a half-dozen connections.

Your connection string indicates Integrated Security=False but does not
provide a UID/PWD. Without Integrated Security, you have to provide user
credentials--if these change, you get a new pool for each connection string.
If this is what you're doing, I can see how pooling is essentially crippled.

Are you creating an ASP application or a client/server WinForms app or a
middle-tier component?

You might benefit from reading my article on connection pooling. See
http://www.betav.com/sql_server_magazine.htm.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Angel Saenz-Badillos[MS]]

Please be aware that the Max Pool Size and Min Pool Size options only work
for the SqlClient and the Oracle managed provider, the Oledb and Odbc
providers are dependant on the native pooling implemented by the provider
that they wrap.

If you are using SqlClient to connect to sql server and you open a
connection with a Min Pool Size you should see the following.
In your thread a connection opens and is returned to you.
In a background thread Min Pool Size-1 connections are opened (takes a few
seconds depending on the Min pool size)
During regular use at least Min Pool Size connections are open at all times.
When not in use connections in the pool will be recycled after random 4-8
minutes of inactivity, if after recycling a connection we fail to meet Min
Pool Size another connection will be opened in a background thread and added
to the pool.

Please let me know if this is not the behavior that you are experiencing.
Thank you,

 

[Antuan Kinnard]

I apologize for the confusion. Firstly, I deleted the user id and
password properties from the connection string before posting to this
group, so let's assume that I am connecting just fine.

Also, I am creating the pool by solely using the connection string. I
am not writing the mechanism by hand.

In any case, now I'm confused. I've been reading that if you set the
'Min Pool Size' property in the connection string, it would create those
number of connections the first time it opens a connection using that
connection string. For example, in the article located at
http://techrepublic.com.com/5100-6329-5034285.html, setting the 'Min
Pool Size' to 5 will cause there to be 5 connections created the first
time you connect to the server. When other requests come along for a
connection, they'll receive one of these until the limit of 5 is hit.
After that, it will create more connections until the 'Max Pool Size'
limit is hit. Is this right, or am I not interpreting this correctly?

Let me know if something is comfusing.

 

[Antuan Kinnard]

I'm using SqlClient.

Please be aware that the Max Pool Size and Min Pool Size options only work
for the SqlClient and the Oracle managed provider, the Oledb and Odbc
providers are dependant on the native pooling implemented by the provider
that they wrap.

If you are using SqlClient to connect to sql server and you open a
connection with a Min Pool Size you should see the following.
In your thread a connection opens and is returned to you.
In a background thread Min Pool Size-1 connections are opened (takes a few
seconds depending on the Min pool size)
During regular use at least Min Pool Size connections are open at all times.
When not in use connections in the pool will be recycled after random 4-8
minutes of inactivity, if after recycling a connection we fail to meet Min
Pool Size another connection will be opened in a background thread and added
to the pool.

Please let me know if this is not the behavior that you are experiencing.
Thank you,

 

[Antuan Kinnard]

I never see my 'Min Pool Size' number of connections created in
Profiler. I left it open for a half hour and 5 connections are never
created. Although I'm using 'Min Pool Size=5', if I only open one
connection in my app, I will only see one connection created through
Profiler and Performance Monitor. I've seen examples of this scenario
(in books and on the internet), and they'll show that there are 5
connections created after only one call to SqlConnectin.Open.

 

[William \(Bill\) Vaughn]

I was wrong. My initial tests (done some time ago) showed the behavior I
described but as Angel says, all 5 connections are created immediately. I
know this because I just tested it and a single Open creates 5 connections.
Use the Performance Monitor SQL Server General Statistics: User Connections
counter or sp_who to verify this. I also saw all 5 connections get created
in the Profiler.
I don't know what's going wrong with your rig. Are you using SQL Server
2000?

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Angel Saenz-Badillos[MS]]

Antuan,
With SqlClient you should defintelly be seeing Min Pool size connections
created by a background thread. You should be able to verify these as Audit
Logins with the profiler, as AWAITING COMMAND with SP_WHO among other ways.

Please post some more information about the version of the framework and of
Sql Server you are using, an SP_WHO or profiler trace may also help.
One correction to my previous post though, I posted that the connections are
recycled when inactive for a certain ammount of time, this is not documented
and therefore not guaranteed, this behavior may change between releases as
we work in performance and stability, sorry for the confusion.

Thanks,

 

[Antuan Kinnard]

I'm using the .NET framework version 1.1. On the same machine, I'm
running SQL Server 2000. It's actually the personal edition
installation of SQL Server from the Enterprise Edition CDs. I don't
think that would be a problem because this should work all the way down
to MSDE, at least to some extent.

Although for this app I'm using version 1.1, I do have the .NET
framework version 1.0 installed on that machine.