Connection Pooling and SPID

We use SQL authentication from a .Net 3.5 WinForms application to connect to SQL 2008 database. I assume ADO.Net uses connection pooling by default. We are using standard ADO.Net to connect to the database.

Lets assume the pool size is 100 (the default). In a hypothetical scenario, 100 users connect to the database using the same application. Then the 100th user disconnects thereby releasing the connection to the pool. Now another user connects to the database. Will these two users share SPID?

I tried this on my machine and using a database locally. Apparently two instances of the same application never share SPID. They did not appear to reuse even the connection pool.

I also read in another article that connection pools are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity. Each user will have their own process. So each user will use their own connection pool. So doesnt that defeat the purpose of connection pooling? Am I missing something?

http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx

We are using local temporary tables in sql server code and so I am a bit concerned two connections will try to use the same temporary table and cause inconsistent results and serious issues.
LVL 3
shekhar_shashiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ted BouskillSenior Software DeveloperCommented:
I think this is a duplicate question.

Connection pooling only occurs on the client NOT the server.  That limit has NOTHING to do with the number of requests or transactions the server can process.

Establishing a SQL connection is time consuming.  Microsoft recommends that SQL connections open late/close early so as your client application is opening/closing connections it can use it's own local connection pool to save time.  The benefit of connection pooling is purely for the client.

Temporary tables occur in the scope of each client's transactions and have nothing to do with connection pooling.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shekhar_shashiAuthor Commented:
Just double confirming this. So each client machine will have their own connection pool stored on the client machine memory. Is this true?
0
Ted BouskillSenior Software DeveloperCommented:
Yes.  The resources used in the connection pool are quite small and unless you are writing a lot multithreading in your clients they will rarely use more than 10 connections concurrently.
0
shekhar_shashiAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.