Why don't processes go away?

  • Thread starter Thread starter Bruce Sandell
  • Start date Start date
B

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
 
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
 
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
 
Back
Top