Posted by Erland Sommarskog on 11/04/05 00:31

Dave (daveg.01@gmail.com) writes:
> I was trying to find the bottle neck on our server while we were
> running a couple very large updates.
>
> I ran the following query and noticed there were a couple sleeping
> spid's that were really eating up the memory. One had a value of
> 16000.
>
> I guess my question is was this query still holding on to that memory
> even though the query had finished? Why didn't SQL Server clean it up?

Books Online says about memusage:

Number of pages in the procedure cache that are currently allocated to
this process. A negative number indicates that the process is freeing
memory allocated by another process.

Since the process might be running those queries/stored procedures again,
it make sense to keep the query plans.

And 16000 pages is only 128 K, which is not a very lot.

I think you should look elsewhere for improvements. For instance, see
if you can improve the query plan on those large updates. Or break into
smaller chunks.





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

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

[Back to original message]