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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Mark" <fi******@idonotlikejunkmail.umn.edu> wrote in message
news:eM**************@TK2MSFTNGP11.phx.gbl...

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

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

"Mark" <fi******@idonotlikejunkmail.umn.edu> wrote in message
news:eM**************@TK2MSFTNGP11.phx.gbl...

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

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

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"©tefan ©imek" <si********@kascomp.blah.sk> wrote in message
news:u0**************@TK2MSFTNGP12.phx.gbl...

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

"Mark" <fi******@idonotlikejunkmail.umn.edu> wrote in message
news:eM**************@TK2MSFTNGP11.phx.gbl...

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

[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]" <mv*@spam.guard.caspershouse.com> wrote in
message news:%2****************@tk2msftngp13.phx.gbl...

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.

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

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"James Curran" <Ja*********@mvps.org> wrote in message
news:ON**************@TK2MSFTNGP12.phx.gbl...

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]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:%2****************@tk2msftngp13.phx.gbl...

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.

[James Curran]

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:u6**************@TK2MSFTNGP09.phx.gbl...

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