how to correctly finding memory usage.

i have a box running very slow. it has 16 gb of ram, running
a SQL 2005 Enterprise Edition (64-bit), build 9.00.3161.00, SP2 on
a win2003 R2 enterprise x64 with SP2.
during the non-busy hours, Task Manager shows 4-50 KB ram available. what
is the right way to free up the memory? is there a way to see what are the
resources not released? (like top or ps -ef |grep....)
tried the neat Activity Monitor, nothing shows where the 15 gb are used.
thanks.You need to ensure that the max memory option of sql server is set to leave
memory for the OS and any other apps that may run on the server. With 16GB
total I would recommend setting it to 14GB and see how that goes. You can
set this via sp_configure or SSMS.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:FD5F89D0-9942-4BB1-A08C-D87516983F85@.microsoft.com...
>i have a box running very slow. it has 16 gb of ram, running
> a SQL 2005 Enterprise Edition (64-bit), build 9.00.3161.00, SP2 on
> a win2003 R2 enterprise x64 with SP2.
> during the non-busy hours, Task Manager shows 4-50 KB ram available.
> what
> is the right way to free up the memory? is there a way to see what are
> the
> resources not released? (like top or ps -ef |grep....)
> tried the neat Activity Monitor, nothing shows where the 15 gb are used.
> thanks.|||setting the max memory is great.
how will i know what will be occupying the 14 GB?|||That 14GB will be used by the buffer pool of the instance. For the
distribution of the buffer pool buffers, I find DBCC MEMORYSTATUS handy.
Linchi
"light_wt" wrote:
> setting the max memory is great.
> how will i know what will be occupying the 14 GB?
>|||wow...
thank you so much Linchi. that command is so cool.
i've g* and played with it a bit. it pulls very useful info.
just wondering here, if i want to pin point to see if certain BI things
(such as: cubes, proc, partition, aggregation, and etc) is the casue of the
memory issue. will there be a way to zoom in/ narrow down the issue?
afterall, the box is a sql05 for data mining...
Thanks.|||The memory will all be used by SQL Servers buffer pool. To see how that is
broken down have a look at the sys.dm_os_buffer_descriptors DMV in
BooksOnLine.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:790A960F-9625-447C-A09E-A1818101F1FD@.microsoft.com...
> setting the max memory is great.
> how will i know what will be occupying the 14 GB?
>