Diagnosing Memory Problems?

I've noticed high memory consumption on some of our workstations that
eventually cause unacceptable performance problems. Through Task
Manager, I see that SQL Server (sqlsrvr.exe) is consuming the bulk of
this memory.
We've found that restarting SQL Server (using netstop/netstart)
dramatically reduces memory usage but it gradually creeps back to an
unacceptable level and becomes a problem (high pages/sec thrashing). We
are setting up a system that will periodically restart SQL Server. This
is a stopgap solution.
I'd like to identify what in SQL Server is using this memory and why.
Ideally, I would redesign my applications so that this wouldn't happen.
I suspect some kind of design flaw or usage error and I want to identify
it. Adding physical RAM isn't an option since this is running on a 70
workstation farm.
I don't want to simply limit SQL Server's memory allotment since this
leads to increased disk use which is the main problem.
I am aware of three tools to diagnose this:
- Performance Monitor
- Enterprise Manager "Current Activity"
- SQL Server Profiler
Which is the best tool to do this and how would I approach this scenario?
Thank You!SQL Server and most other high-end database products are
essentially disk caching engines (that provide
transactions and management etc)
so under most circumstances, its suppose to use all
available memory for most effective disk caching
it will also release memory to other apps if the available
system memory falls below 4-6MB (i do wish MS would
increase this setting to 16-32MB)
however, if you are not using it as a dedicated DB server,
use Ent Manager to restrict the max memory to some desired
amount, 64-128M is good for a development system.
don't trying to figure out which queries are using memory,
unless you want to reduce the amount of data.
>--Original Message--
>I've noticed high memory consumption on some of our
workstations that
>eventually cause unacceptable performance problems.
Through Task
>Manager, I see that SQL Server (sqlsrvr.exe) is consuming
the bulk of
>this memory.
>We've found that restarting SQL Server (using
netstop/netstart)
>dramatically reduces memory usage but it gradually creeps
back to an
>unacceptable level and becomes a problem (high pages/sec
thrashing). We
>are setting up a system that will periodically restart
SQL Server. This
>is a stopgap solution.
>I'd like to identify what in SQL Server is using this
memory and why.
>Ideally, I would redesign my applications so that this
wouldn't happen.
>I suspect some kind of design flaw or usage error and I
want to identify
>it. Adding physical RAM isn't an option since this is
running on a 70
>workstation farm.
>I don't want to simply limit SQL Server's memory
allotment since this
>leads to increased disk use which is the main problem.
>I am aware of three tools to diagnose this:
>- Performance Monitor
>- Enterprise Manager "Current Activity"
>- SQL Server Profiler
>Which is the best tool to do this and how would I
approach this scenario?
>Thank You!
>.
>|||Excellent insight. Thank you!
joe chang wrote:
> SQL Server and most other high-end database products are
> essentially disk caching engines (that provide
> transactions and management etc)
> so under most circumstances, its suppose to use all
> available memory for most effective disk caching
> it will also release memory to other apps if the available
> system memory falls below 4-6MB (i do wish MS would
> increase this setting to 16-32MB)
> however, if you are not using it as a dedicated DB server,
> use Ent Manager to restrict the max memory to some desired
> amount, 64-128M is good for a development system.
> don't trying to figure out which queries are using memory,
> unless you want to reduce the amount of data.|||I am consistantly getting Physical Memory Usage High errors. My Buffer Cache is running at 99% and the Server Memory is at 87%. Any thoughts on what's causing this?