Connection Pooling and SPID

Posted on 2010-04-09
Medium Priority
Last Modified: 2012-06-27
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?


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.
Question by:shekhar_shashi
  • 2
  • 2
LVL 51

Accepted Solution

Ted Bouskill earned 2000 total points
ID: 30347751
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.

Author Comment

ID: 30350716
Just double confirming this. So each client machine will have their own connection pool stored on the client machine memory. Is this true?
LVL 51

Expert Comment

by:Ted Bouskill
ID: 30355556
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.

Author Closing Comment

ID: 31760922

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question