Why don't processes go away?

[Bruce Sandell]

I have a c# application where I run a query every 10
seconds.

After the query completes, I close the connection object,
close the oledbdatareader object and dispose of the
command object.

When I look at my current activity in the database,
sleeping processes keep accumulating for each of these
queries. Why don't they go away? Should I care? Are
they using up any resources?

This application runs on MSDE (latest version).

Thanks for any help.

Bruce

[Ian]

Hi,

Opening and closing connections is expensive - OLEDB will pool connections
by default to mitigate this.

When you close the connection it gets returned to a connection pool. When
you open another connection it gets taken from the pool, assuming that
you're using an identical connection string. This is much faster than
actually opening a port, communicating with SQL Server and setting up a
brand new connection. On the other hand, if you don't open a connection for
a while then idle connections from the pool will actually get destroyed. At
this point they will disappear from SQL Server.

HTH

Ian

"Bruce Sandell" <br***@conferserv.com> wrote in message
news:11*****************************@phx.gbl...

I have a c# application where I run a query every 10
seconds.

After the query completes, I close the connection object,
close the oledbdatareader object and dispose of the
command object.

When I look at my current activity in the database,
sleeping processes keep accumulating for each of these
queries. Why don't they go away? Should I care? Are
they using up any resources?

This application runs on MSDE (latest version).

Thanks for any help.

Bruce

[Bruce Sandell]

Hi Ian,

Thanks for the info, but I'm still a little confused. If
the connections are being pooled and reused, then why are
there so many processes showing up in SQL Server?
Shouldn't it just keep reusing the same connection? I am
using the same connection string every time. Does it have
to do with it being a web-based application? I have one
person using the application at the moment, and still have
267 processes showing up in the current activity list,
most for the same query.

Thanks,
Bruce

-----Original Message-----
Hi,

Opening and closing connections is expensive - OLEDB will pool connectionsby default to mitigate this.

When you close the connection it gets returned to a connection pool. Whenyou open another connection it gets taken from the pool, assuming thatyou're using an identical connection string. This is much faster thanactually opening a port, communicating with SQL Server and setting up abrand new connection. On the other hand, if you don't open a connection fora while then idle connections from the pool will actually get destroyed. Atthis point they will disappear from SQL Server.

HTH

Ian

"Bruce Sandell" <br***@conferserv.com> wrote in message
news:11*****************************@phx.gbl...

I have a c# application where I run a query every 10
seconds.

After the query completes, I close the connection object, close the oledbdatareader object and dispose of the
command object.

When I look at my current activity in the database,
sleeping processes keep accumulating for each of these
queries. Why don't they go away? Should I care? Are
they using up any resources?

This application runs on MSDE (latest version).

Thanks for any help.

Bruce

.