Q:Multithreaded ADO.Net Connections Are Non Pooled?

[Marauderz]

Hi guys, have a little weird scenario here.

I have a windows Service that's serving requets using multiple threads
created by the .Net threadpool. Within each thread they will create their
own Connection object and go off and do their thing, an insert, a Select and
the connection is closed.

The SQL Server which the service communicates with lies on a different
physical machine.

Now this service is basically hammered.. about 20 hits per second. And when
I run TCPView I see that a LOT of ports have been opened by my service to
connect to the SQL Server machine, wasn't connection pooling supposed to use
only one?

I decided to run some test myself and wrote a seperate test program that
just updates the database using threads created by the thread pool.
According to the performance monitor The connections that were created by
the threads all seem to fall under the non pooled thread category.

Does anyone have an explanation why the threads aren't pooled? And what I
should do to err.. correct this situation?

Thanks

 

[jabailo]

Hi guys, have a little weird scenario here.

I have a windows Service that's serving requets using multiple threads
created by the .Net threadpool. Within each thread they will create their
own Connection object and go off and do their thing, an insert, a Select and
the connection is closed.

The SQL Server which the service communicates with lies on a different
physical machine.

Now this service is basically hammered.. about 20 hits per second. And when
I run TCPView I see that a LOT of ports have been opened by my service to
connect to the SQL Server machine, wasn't connection pooling supposed to use
only one?

This is how I understand connection pooling.

If a connection is made, and pooling is set to True ( which it is by
default ), that connection has a certain latency.

The next time a new connection is made -- if the connection string
matches the previous, that 'pooled' connection, which is still there, is
used.

The number of pooled connections is unlimited based on demand, unless
you restrict it with the minimum or maximum pool size attribute in your
connection string.

 

[William \(Bill\) Vaughn]

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

 

[William \(Bill\) Vaughn]

Oops... pressed Send too soon.
First, the connection pool is designed to pool connections using:
a.. The same ConnectionString
b.. The same credentials. If you're using SSPI, each connection must be opened "by" the same login.
c.. The same transaction context
d.. The same transaction "enlistment" state
e.. The same MARS state (2.0 only)
f.. The same process/app domain. I expect this is where you might be having problems.
Ok, let's assume that all of the above are the same. The connection pool will only share "idle" connections--those that have been opened, used and (most importantly close) BEFORE another request comes in. If there is no pooled connection available, another is added to the pool. If the aforementioned factors change, a new pool is created based on the settings used. I also suspect you might be overloading the server. The profiler and perfmon can monitor the connections and pools (as I describe in my ADO.NET books).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

 

[Marauderz]

Thanks for the answers guys.

Now that I have a better understanding on how the connections are being
pooled I might be able to find out the main cause of the problem. As for the
process/app domain differences, does running on a thread created by
QueueUserWorkItem means that a new app domain/process gets created? I don't
think so right?

Thanks again.

----------
Oops... pressed Send too soon.
First, the connection pool is designed to pool connections using:
The same ConnectionString
The same credentials. If you're using SSPI, each connection must be
opened "by" the same login.
The same transaction context
The same transaction "enlistment" state
The same MARS state (2.0 only)
The same process/app domain. I expect this is where you might be having
problems.
Ok, let's assume that all of the above are the same. The connection pool
will only share "idle" connections--those that have been opened, used and
(most importantly close) BEFORE another request comes in. If there is no
pooled connection available, another is added to the pool. If the
aforementioned factors change, a new pool is created based on the settings
used. I also suspect you might be overloading the server. The profiler and
perfmon can monitor the connections and pools (as I describe in my ADO.NET
books).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

 

[William \(Bill\) Vaughn]

You can tell by picking up the ProcessID as you execute... if the PID is
different you get a new pool.
Simple threading from a single process should share the same pool, but
remember you can't share connections across threads until 2.0 and then you
still have to serialize (MARS).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

 

[Pablo Castro [MS]]

Just to confirm: code running as a result of calling QueueUserWorkItem will
run in threads from the CLR thread-pool and will be in the same
process/appdomain as the caller, so it will effectively share the same
connection pool.

To minimize pool fragmentation you need to make sure all the thing Bill
pointed below are the same (connection string, NT identity, etc.)

If you post a lot of work-items, one thing you can do is to use a
ThreadStatic varaible (a TLS slot in Win32 terms) to store a connection
per-thread; that way you don't need to open/close connections (assuming that
all threads hit always the same database with the same identity), you simply
check the thread-static variable; if it's empty, open a new one, otherwise
use the one that's there; since it's thread-static, no synchronization is
needed because only your thread is using it. You need to make absolutely
sure that you don't hand out the connection reference to any other component
that may re-use it in another thread.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Marauderz]

All the parameters do seem to be the same, and as I mentioned the strange
thing is, using Perfmon I can see that under .Net Data the "# Pooled And Non
Pooled Connections" have raised considerably during heavy load, up to oh...
800... that's not good right?

 

[William \(Bill\) Vaughn]

Right. That's not good.
It means you're leaking connections. Check out the postings here and in
articles on my website about managing the connection pool.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

 

[John Bailo]

Do these same effects occur if I call the same web method, which inserts
a record, several times asynchronously?

Or does aspnet_wp.exe effectively act as the *same* connection pool?

Will another web service, running on that web server, share the same
connection pool?

 

[John Bailo]

Right. That's not good.
It means you're leaking connections. Check out the postings here and in
articles on my website about managing the connection pool.

I read your article ( great analogy ) but I'm still not sure what you
mean by "leaking" connections. For example, you mention .Close()

Well, I thought the point of the pool is that even when I do a .Close()
the pipe to the database remains in memory so the next time an .Open()
occurs with the same connection string that is used rather than open a
new one ( unless there are none available ).

The big problem for me is ( and this has been documented many times )
that if a database server goes down, there's no way to "cleanup" the
pool explicitly and so each and every open connection has to be /tried/
before a good one can be found.

 

[William \(Bill\) Vaughn]

This (serious oversight) has been fixed in the 2.0 Framework... if that's
any consolation.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________