469,613 Members | 1,692 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,613 developers. It's quick & easy.

Webservice not releasing SQL connections

I have a webservice written in VB.net that opens a connection to SQL Server
2000 with a connection string. The connection remains open for the life of
the web service so the ADODB.connection is defined as public and used by a
single subroutine, SQLexecute. All recordset definitions are defined in the
individual subroutines and destroyed before exit of the subroutine. I would
expect only one connection to be made to the SQL Server but when I access
the webservice it appears that EVERY read or write to the database creates a
new connection and uses a new local port!

I used TCPview to look at the ports being used and sp_who to look at the SQL
spid's and there are hundreds of connections established after only a couple
of sessions with the web service. I counted the number of reads and writes
to the SQL Server and they are essentially the same number as the number of
open (established) ports to the SQL server. It's as if it creates a new
connection for every read or write and never closes them.

Can anyone shed any light on why these connections are being created and how
to stop this from happening?

Thanks for any direction you can provide.

Steve
Apr 3 '07 #1
6 4101
Hi Steve,

How does your connection string looks like? Does it change for each user
that connects to the database? Or are you using a dedicated account to
connect to SQL Server? Have you tried to open and close the connection with
each call to the database and look at the connections created? And why do you
need to keep a connection open for the lifetime of the web service? This
doesn't correspond to the ADO.NET disconnected architecture, right?

"Steve Ricketts" wrote:
I have a webservice written in VB.net that opens a connection to SQL Server
2000 with a connection string. The connection remains open for the life of
the web service so the ADODB.connection is defined as public and used by a
single subroutine, SQLexecute. All recordset definitions are defined in the
individual subroutines and destroyed before exit of the subroutine. I would
expect only one connection to be made to the SQL Server but when I access
the webservice it appears that EVERY read or write to the database creates a
new connection and uses a new local port!

I used TCPview to look at the ports being used and sp_who to look at the SQL
spid's and there are hundreds of connections established after only a couple
of sessions with the web service. I counted the number of reads and writes
to the SQL Server and they are essentially the same number as the number of
open (established) ports to the SQL server. It's as if it creates a new
connection for every read or write and never closes them.

Can anyone shed any light on why these connections are being created and how
to stop this from happening?

Thanks for any direction you can provide.

Steve
Apr 5 '07 #2
Thanks for the reply! The connection string looks like:

Provider=SQLOLEDB;Data Source=xx.xx.xx.xx; Initial Catalog=MyDB; User ID=sa;
Password=somepassword;

Just one connection string for all users. I haven't tried opening and
closing the connection but I will do so.

This is the run-time environment for a web based training application. Each
page of a training "course" may access the web service 10-30 times per page
and there are thousands of students that could be accessing it concurrently.
It doesn't seem to make sense to open and close the database connection when
everone that makes a request to the web service will want to read or write
into the database. The clients make a request to the service and receive a
reply. No state information is maintained so I couldn't say whether this is
a "disconnected" architecture or not. To the client it is, to the database
it's not.

I'll let you know what the open/close test shows.

Thanks again,

Steve

"Eugen" <Eu***@discussions.microsoft.comwrote in message
news:F7**********************************@microsof t.com...
Hi Steve,

How does your connection string looks like? Does it change for each user
that connects to the database? Or are you using a dedicated account to
connect to SQL Server? Have you tried to open and close the connection
with
each call to the database and look at the connections created? And why do
you
need to keep a connection open for the lifetime of the web service? This
doesn't correspond to the ADO.NET disconnected architecture, right?

"Steve Ricketts" wrote:
>I have a webservice written in VB.net that opens a connection to SQL
Server
2000 with a connection string. The connection remains open for the life
of
the web service so the ADODB.connection is defined as public and used by
a
single subroutine, SQLexecute. All recordset definitions are defined in
the
individual subroutines and destroyed before exit of the subroutine. I
would
expect only one connection to be made to the SQL Server but when I access
the webservice it appears that EVERY read or write to the database
creates a
new connection and uses a new local port!

I used TCPview to look at the ports being used and sp_who to look at the
SQL
spid's and there are hundreds of connections established after only a
couple
of sessions with the web service. I counted the number of reads and
writes
to the SQL Server and they are essentially the same number as the number
of
open (established) ports to the SQL server. It's as if it creates a new
connection for every read or write and never closes them.

Can anyone shed any light on why these connections are being created and
how
to stop this from happening?

Thanks for any direction you can provide.

Steve

Apr 5 '07 #3
Tip 3—Connection Pooling

Setting up the TCP connection between your Web application and SQL Server™
can be an expensive operation. Developers at Microsoft have been able to take
advantage of connection pooling for some time now, allowing them to reuse
connections to the database. Rather than setting up a new TCP connection on
each request, a new connection is set up only when one is not available in
the connection pool. When the connection is closed, it is returned to the
pool where it remains connected to the database, as opposed to completely
tearing down that TCP connection.

Of course you need to watch out for leaking connections. Always close your
connections when you're finished with them. I repeat: no matter what anyone
says about garbage collection within the Microsoft® .NET Framework, always
call Close or Dispose explicitly on your connection when you are finished
with it. Do not trust the common language runtime (CLR) to clean up and close
your connection for you at a predetermined time. The CLR will eventually
destroy the class and force the connection closed, but you have no guarantee
when the garbage collection on the object will actually happen.

To use connection pooling optimally, there are a couple of rules to live by.
First, open the connection, do the work, and then close the connection. It's
okay to open and close the connection multiple times on each request if you
have to (optimally you apply Tip 1) rather than keeping the connection open
and passing it around through different methods. Second, use the same
connection string (and the same thread identity if you're using integrated
authentication). If you don't use the same connection string, for example
customizing the connection string based on the logged-in user, you won't get
the same optimization value provided by connection pooling. And if you use
integrated authentication while impersonating a large set of users, your
pooling will also be much less effective. The .NET CLR data performance
counters can be very useful when attempting to track down any performance
issues that are related to connection pooling.

Whenever your application is connecting to a resource, such as a database,
running in another process, you should optimize by focusing on the time spent
connecting to the resource, the time spent sending or retrieving data, and
the number of round-trips. Optimizing any kind of process hop in your
application is the first place to start to achieve better performance.

The application tier contains the logic that connects to your data layer and
transforms data into meaningful class instances and business processes. For
example, in Community Server, this is where you populate a Forums or Threads
collection, and apply business rules such as permissions; most importantly it
is where the Caching logic is performed.
From http://msdn.microsoft.com/msdnmag/is...erformance/#S4
"Steve Ricketts" wrote:
Thanks for the reply! The connection string looks like:

Provider=SQLOLEDB;Data Source=xx.xx.xx.xx; Initial Catalog=MyDB; User ID=sa;
Password=somepassword;

Just one connection string for all users. I haven't tried opening and
closing the connection but I will do so.

This is the run-time environment for a web based training application. Each
page of a training "course" may access the web service 10-30 times per page
and there are thousands of students that could be accessing it concurrently.
It doesn't seem to make sense to open and close the database connection when
everone that makes a request to the web service will want to read or write
into the database. The clients make a request to the service and receive a
reply. No state information is maintained so I couldn't say whether this is
a "disconnected" architecture or not. To the client it is, to the database
it's not.

I'll let you know what the open/close test shows.

Thanks again,

Steve

"Eugen" <Eu***@discussions.microsoft.comwrote in message
news:F7**********************************@microsof t.com...
Hi Steve,

How does your connection string looks like? Does it change for each user
that connects to the database? Or are you using a dedicated account to
connect to SQL Server? Have you tried to open and close the connection
with
each call to the database and look at the connections created? And why do
you
need to keep a connection open for the lifetime of the web service? This
doesn't correspond to the ADO.NET disconnected architecture, right?

"Steve Ricketts" wrote:
I have a webservice written in VB.net that opens a connection to SQL
Server
2000 with a connection string. The connection remains open for the life
of
the web service so the ADODB.connection is defined as public and used by
a
single subroutine, SQLexecute. All recordset definitions are defined in
the
individual subroutines and destroyed before exit of the subroutine. I
would
expect only one connection to be made to the SQL Server but when I access
the webservice it appears that EVERY read or write to the database
creates a
new connection and uses a new local port!

I used TCPview to look at the ports being used and sp_who to look at the
SQL
spid's and there are hundreds of connections established after only a
couple
of sessions with the web service. I counted the number of reads and
writes
to the SQL Server and they are essentially the same number as the number
of
open (established) ports to the SQL server. It's as if it creates a new
connection for every read or write and never closes them.

Can anyone shed any light on why these connections are being created and
how
to stop this from happening?

Thanks for any direction you can provide.

Steve


Apr 5 '07 #4
Steve, I have the feeling that your web service leak connections and that
actually you are creating a new connection with each call.

Read the article from below. I hope it will help you.

http://msdn2.microsoft.com/en-us/lib...3(SQL.80).aspx

"Eugen" wrote:
Tip 3—Connection Pooling

Setting up the TCP connection between your Web application and SQL Server™
can be an expensive operation. Developers at Microsoft have been able to take
advantage of connection pooling for some time now, allowing them to reuse
connections to the database. Rather than setting up a new TCP connection on
each request, a new connection is set up only when one is not available in
the connection pool. When the connection is closed, it is returned to the
pool where it remains connected to the database, as opposed to completely
tearing down that TCP connection.

Of course you need to watch out for leaking connections. Always close your
connections when you're finished with them. I repeat: no matter what anyone
says about garbage collection within the Microsoft® .NET Framework, always
call Close or Dispose explicitly on your connection when you are finished
with it. Do not trust the common language runtime (CLR) to clean up and close
your connection for you at a predetermined time. The CLR will eventually
destroy the class and force the connection closed, but you have no guarantee
when the garbage collection on the object will actually happen.

To use connection pooling optimally, there are a couple of rules to live by.
First, open the connection, do the work, and then close the connection. It's
okay to open and close the connection multiple times on each request if you
have to (optimally you apply Tip 1) rather than keeping the connection open
and passing it around through different methods. Second, use the same
connection string (and the same thread identity if you're using integrated
authentication). If you don't use the same connection string, for example
customizing the connection string based on the logged-in user, you won't get
the same optimization value provided by connection pooling. And if you use
integrated authentication while impersonating a large set of users, your
pooling will also be much less effective. The .NET CLR data performance
counters can be very useful when attempting to track down any performance
issues that are related to connection pooling.

Whenever your application is connecting to a resource, such as a database,
running in another process, you should optimize by focusing on the time spent
connecting to the resource, the time spent sending or retrieving data, and
the number of round-trips. Optimizing any kind of process hop in your
application is the first place to start to achieve better performance.

The application tier contains the logic that connects to your data layer and
transforms data into meaningful class instances and business processes. For
example, in Community Server, this is where you populate a Forums or Threads
collection, and apply business rules such as permissions; most importantly it
is where the Caching logic is performed.
From http://msdn.microsoft.com/msdnmag/is...erformance/#S4
"Steve Ricketts" wrote:
Thanks for the reply! The connection string looks like:

Provider=SQLOLEDB;Data Source=xx.xx.xx.xx; Initial Catalog=MyDB; User ID=sa;
Password=somepassword;

Just one connection string for all users. I haven't tried opening and
closing the connection but I will do so.

This is the run-time environment for a web based training application. Each
page of a training "course" may access the web service 10-30 times per page
and there are thousands of students that could be accessing it concurrently.
It doesn't seem to make sense to open and close the database connection when
everone that makes a request to the web service will want to read or write
into the database. The clients make a request to the service and receive a
reply. No state information is maintained so I couldn't say whether this is
a "disconnected" architecture or not. To the client it is, to the database
it's not.

I'll let you know what the open/close test shows.

Thanks again,

Steve

"Eugen" <Eu***@discussions.microsoft.comwrote in message
news:F7**********************************@microsof t.com...
Hi Steve,
>
How does your connection string looks like? Does it change for each user
that connects to the database? Or are you using a dedicated account to
connect to SQL Server? Have you tried to open and close the connection
with
each call to the database and look at the connections created? And why do
you
need to keep a connection open for the lifetime of the web service? This
doesn't correspond to the ADO.NET disconnected architecture, right?
>
"Steve Ricketts" wrote:
>
>I have a webservice written in VB.net that opens a connection to SQL
>Server
>2000 with a connection string. The connection remains open for the life
>of
>the web service so the ADODB.connection is defined as public and used by
>a
>single subroutine, SQLexecute. All recordset definitions are defined in
>the
>individual subroutines and destroyed before exit of the subroutine. I
>would
>expect only one connection to be made to the SQL Server but when I access
>the webservice it appears that EVERY read or write to the database
>creates a
>new connection and uses a new local port!
>>
>I used TCPview to look at the ports being used and sp_who to look at the
>SQL
>spid's and there are hundreds of connections established after only a
>couple
>of sessions with the web service. I counted the number of reads and
>writes
>to the SQL Server and they are essentially the same number as the number
>of
>open (established) ports to the SQL server. It's as if it creates a new
>connection for every read or write and never closes them.
>>
>Can anyone shed any light on why these connections are being created and
>how
>to stop this from happening?
>>
>Thanks for any direction you can provide.
>>
>Steve
>>
>>
>>
Apr 5 '07 #5
I suspect you're right... not sure why it happens, but that's what it seems
to be doing. I'll take a look at the article in more detail, it looks
interesting.

Many thanks,

Steve

"Eugen" <Eu***@discussions.microsoft.comwrote in message
news:7E**********************************@microsof t.com...
Steve, I have the feeling that your web service leak connections and that
actually you are creating a new connection with each call.

Read the article from below. I hope it will help you.

http://msdn2.microsoft.com/en-us/lib...3(SQL.80).aspx

"Eugen" wrote:
>Tip 3-Connection Pooling

Setting up the TCP connection between your Web application and SQL
ServerT
can be an expensive operation. Developers at Microsoft have been able to
take
advantage of connection pooling for some time now, allowing them to reuse
connections to the database. Rather than setting up a new TCP connection
on
each request, a new connection is set up only when one is not available
in
the connection pool. When the connection is closed, it is returned to the
pool where it remains connected to the database, as opposed to completely
tearing down that TCP connection.

Of course you need to watch out for leaking connections. Always close
your
connections when you're finished with them. I repeat: no matter what
anyone
says about garbage collection within the Microsoft .NET Framework,
always
call Close or Dispose explicitly on your connection when you are finished
with it. Do not trust the common language runtime (CLR) to clean up and
close
your connection for you at a predetermined time. The CLR will eventually
destroy the class and force the connection closed, but you have no
guarantee
when the garbage collection on the object will actually happen.

To use connection pooling optimally, there are a couple of rules to live
by.
First, open the connection, do the work, and then close the connection.
It's
okay to open and close the connection multiple times on each request if
you
have to (optimally you apply Tip 1) rather than keeping the connection
open
and passing it around through different methods. Second, use the same
connection string (and the same thread identity if you're using
integrated
authentication). If you don't use the same connection string, for example
customizing the connection string based on the logged-in user, you won't
get
the same optimization value provided by connection pooling. And if you
use
integrated authentication while impersonating a large set of users, your
pooling will also be much less effective. The .NET CLR data performance
counters can be very useful when attempting to track down any performance
issues that are related to connection pooling.

Whenever your application is connecting to a resource, such as a
database,
running in another process, you should optimize by focusing on the time
spent
connecting to the resource, the time spent sending or retrieving data,
and
the number of round-trips. Optimizing any kind of process hop in your
application is the first place to start to achieve better performance.

The application tier contains the logic that connects to your data layer
and
transforms data into meaningful class instances and business processes.
For
example, in Community Server, this is where you populate a Forums or
Threads
collection, and apply business rules such as permissions; most
importantly it
is where the Caching logic is performed.
From http://msdn.microsoft.com/msdnmag/is...erformance/#S4
"Steve Ricketts" wrote:
Thanks for the reply! The connection string looks like:

Provider=SQLOLEDB;Data Source=xx.xx.xx.xx; Initial Catalog=MyDB; User
ID=sa;
Password=somepassword;

Just one connection string for all users. I haven't tried opening and
closing the connection but I will do so.

This is the run-time environment for a web based training application.
Each
page of a training "course" may access the web service 10-30 times per
page
and there are thousands of students that could be accessing it
concurrently.
It doesn't seem to make sense to open and close the database connection
when
everone that makes a request to the web service will want to read or
write
into the database. The clients make a request to the service and
receive a
reply. No state information is maintained so I couldn't say whether
this is
a "disconnected" architecture or not. To the client it is, to the
database
it's not.

I'll let you know what the open/close test shows.

Thanks again,

Steve

"Eugen" <Eu***@discussions.microsoft.comwrote in message
news:F7**********************************@microsof t.com...
Hi Steve,

How does your connection string looks like? Does it change for each
user
that connects to the database? Or are you using a dedicated account
to
connect to SQL Server? Have you tried to open and close the
connection
with
each call to the database and look at the connections created? And
why do
you
need to keep a connection open for the lifetime of the web service?
This
doesn't correspond to the ADO.NET disconnected architecture, right?

"Steve Ricketts" wrote:

I have a webservice written in VB.net that opens a connection to SQL
Server
2000 with a connection string. The connection remains open for the
life
of
the web service so the ADODB.connection is defined as public and
used by
a
single subroutine, SQLexecute. All recordset definitions are
defined in
the
individual subroutines and destroyed before exit of the subroutine.
I
would
expect only one connection to be made to the SQL Server but when I
access
the webservice it appears that EVERY read or write to the database
creates a
new connection and uses a new local port!

I used TCPview to look at the ports being used and sp_who to look at
the
SQL
spid's and there are hundreds of connections established after only
a
couple
of sessions with the web service. I counted the number of reads and
writes
to the SQL Server and they are essentially the same number as the
number
of
open (established) ports to the SQL server. It's as if it creates a
new
connection for every read or write and never closes them.

Can anyone shed any light on why these connections are being created
and
how
to stop this from happening?

Thanks for any direction you can provide.

Steve


Apr 5 '07 #6
Hi Steve
I had a couple of suggestions. Hope you find these helpful.
-You mentioned Adodb, recordset..in your post. How come you are not
using ADO.NET? Using Com objects from a dot net application will give
you poor performance.
-Even if the connection object is declared public, why would you think
that would be reused by all invocations of the web methods? Each
invocation would create a new instance of the public member. You need
to make the member static. The singleton design pattern meets this
requirement perfectly.
Having mentioned this, as Eugen has suggested above, you should be
reopening a connection for each request rather than trying to keep the
same connection alive.

Latish

Apr 7 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Tommy | last post: by
5 posts views Thread by Nikolay Petrov | last post: by
3 posts views Thread by Johan Johansson (Sweden) | last post: by
8 posts views Thread by kenneth fleckenstein nielsen | last post: by
reply views Thread by perschrotti | last post: by
1 post views Thread by Goose14 | last post: by
2 posts views Thread by =?Utf-8?B?TGFycnlLdXBlcm1hbg==?= | last post: by
7 posts views Thread by Amirallia | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.