SQL Server Process and Memory Usage

[w2jin]

Hi All

Some my SQL Server are experience high memory usage.

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

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

Any help will be appreciated.

Thanks
Willie

[Mike Epprecht \(SQL MVP\)]

Hi

What do you term high memory usage?

http://support.microsoft.com/default...b;en-us;321363

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.n et

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

<w2***@hotmail. com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

Hi All

Some my SQL Server are experience high memory usage.

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

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

Any help will be appreciated.

Thanks
Willie

[Erland Sommarskog]

(w2***@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, es****@sommarsk og.se

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