connection pooling with SQL Server

[Mark]

Where is the connection pooling done when using ADO.NET and SQL Server? On
the SQL Server or where the .NET run-time is being executed? I'm assuming
on SQL Server as this would minimize the number of connections needed in the
pool.

Thanks in advance.

Mark

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

"Pooling" means that a connection is kept open after you close it and is
reuser later, therefore both ends are involved. In the client it;s handle by
the provider and in the server by the SQL server, even as I thiink that all
of the pooling features are handled in the client, the server all it sees is
a open connection, it has no ideas if the connection is being used by the
app or just kept by the data provider.

Cheers,

 

[©tefan ©imek]

The connection pooling is performed just inside your application, by keeping
your disposed connections open and reattached the next time you request a
new connection with the same connection string.

As for the server, I can't imagine know how do you expect it to perform
connection pooling. The server usually only accepts connections and as such
is the passive element.

HTH,
Stefan

 

[Nicholas Paldino [.NET/C# MVP]]

Stefan,

Actually, this isn't correct. If you do a trace on SQL server in an
environment that uses connection pooling, you will notice that there is a
stored procedure that is called when the connection is retreived from the
pool named sp_reset_connection.

Hope this helps.

 

[James Curran]

I haven't looked into this, but it sounds like the client pooling code
is just calling sp_reset_connection before it hand a active connection out
to another processes (with the server still completely in to dark about
what's going on).

--
Truth,
James Curran
Home: www.noveltheory.com Work: www.njtheater.com
Blog: www.honestillusion.com Day Job: www.partsearch.com
(note new day job!)

 

[Nicholas Paldino [.NET/C# MVP]]

That's the point though, if the SP is called on the server, then that
definitely alerts the server to something, no? =) It just resets the
session properties on the server end.

 

[James Curran]

That's the point though, if the SP is called on the server, then that
definitely alerts the server to something, no? =) It just resets the
session properties on the server end.

"Something", yes, but what? How would it know the different between a
connection being passed from one process to another, and a single process
doing multiple actions, and just calling that SP between them?

--
Truth,
James Curran
Home: www.noveltheory.com Work: www.njtheater.com
Blog: www.honestillusion.com Day Job: www.partsearch.com
(note new day job!)