Hi-
We've got an ASP.Net web app that runs off a Microsoft SQL Server
2000 backend. After a few days the SQL server is completely out of
memory and crawls. It looks like there could be some connections that
aren't being closed or something. Is there a good way to figure out
where the problem is. Looking at the current activity in Enterprise
Manager there are a lot of threads sleeping and a few that are
runnable. Any ideas?

Thanks

big DWK (daveGoogle@davewking.com) writes:
> We've got an ASP.Net web app that runs off a Microsoft SQL Server
> 2000 backend. After a few days the SQL server is completely out of
> memory and crawls. It looks like there could be some connections that
> aren't being closed or something. Is there a good way to figure out
> where the problem is. Looking at the current activity in Enterprise
> Manager there are a lot of threads sleeping and a few that are
> runnable. Any ideas?

Note that it's perfectly normal for SQL Server to grab as much memory
as possible, since it uses it for cache. So high memory consumption is
not a sympton of a problem in itself. But if you feel that you have
poor performance, then obviously you have something that needs fixing.

As for connections not being closed, again, this may be a non-issue.
Recall that ADO .Net uses connection pooling, and when the application
closes the connection, the connection hangs around for some 60 seconds.
If you use sp_who2, there is a LastBatch column, if there are idle
processes whose LastBatch is hours or even days ago, then there is
something that should be addressed. This could indeeed be due to failing
to close the connection in the ASP .Net code. You should always close
your commands and connections explicitly, and not rely on garbage
collection.

However, is SQL Server is "crawling" this may be due to problems with
poorly written queries and that sort of thing. Running the SQL Profiler
is a way to track down long-running queries. I often look at these sort
of problems with my own procedure aba_lockinfo, which gathers both lock
information and current statement in a snapshot. Look at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp