Posted by Erland Sommarskog on 06/18/05 12:07

(w2jin@hotmail.com) writes:
> Some my SQL Server are experience high memory usage.

That's perfectly normal.

> 1. How can I detect which process which process cause the big memory
> usage and not released?

You can't. The question is not even relevant, as it is the result of
a collective effort.

> 2. Which sql server components in this memory, and what are their usage
> distribution?

When SQL Server reads data from disk, it saves the data in the cache,
because memory access is much less expensive than disk access. Thus,
the strategy is keep as much as possible in cache.

You can constraint how much memory SQL Server can use, but only do this
on machines where SQL Server really competes with other applications.
(A developer workstation is such an example.)

--
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]