> 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
>
>

"Marauderz" <marauderz@nospam.com> wrote in message
news:ujjeeqYgFHA.2180@TK2MSFTNGP15.phx.gbl...
> 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
>

"Marauderz" <marauderz@nospam.com> wrote in message news:ujjeeqYgFHA.2180@TK2MSFTNGP15.phx.gbl...
> 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
>
>

"Marauderz" <marauderz@nospam.com> wrote in message
news:ujjeeqYgFHA.2180@TK2MSFTNGP15.phx.gbl...
> 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
>
>

"Marauderz" <marauderz@nospam.com> wrote in message
news:O18hZ6cgFHA.3936@tk2msftngp13.phx.gbl...
> 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.
>
> ----------
> "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
> news:u9epA0agFHA.2424@TK2MSFTNGP09.phx.gbl...
> 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.
> __________________________________
>
> "Marauderz" <marauderz@nospam.com> wrote in message
> news:ujjeeqYgFHA.2180@TK2MSFTNGP15.phx.gbl...
>> 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
>>
>>
>

"William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
news:u0IotZkgFHA.3220@TK2MSFTNGP10.phx.gbl...
> 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.
> __________________________________
>
> "Marauderz" <marauderz@nospam.com> wrote in message
> news:O18hZ6cgFHA.3936@tk2msftngp13.phx.gbl...
>> 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.
>>
>> ----------
>> "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
>> news:u9epA0agFHA.2424@TK2MSFTNGP09.phx.gbl...
>> 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.
>> __________________________________
>>
>> "Marauderz" <marauderz@nospam.com> wrote in message
>> news:ujjeeqYgFHA.2180@TK2MSFTNGP15.phx.gbl...
>>> 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
>>>
>>>
>>
>
>

"Pablo Castro [MS]" <pablo***@online.microsoft.com> wrote in message
news:OWlHp$mgFHA.3088@TK2MSFTNGP10.phx.gbl...
> 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" <marauderz@nospam.com> wrote in message
news:eOucU0pgFHA.576@TK2MSFTNGP15.phx.gbl...
> 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?
>
>
> "Pablo Castro [MS]" <pablo***@online.microsoft.com> wrote in message
> news:OWlHp$mgFHA.3088@TK2MSFTNGP10.phx.gbl...
>> 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.
>>
>>
>
>

"John Bailo" <jaba***@texeme.com> wrote in message
news:42CDB909.4060405@texeme.com...
> William (Bill) Vaughn wrote:
>> 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.

> 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.
>