Sql-server – How to find what is using the RAM in the server

memory usagesql serversql-server-2008-r2windows-server-2003-r2

I am dealing with 32bit Windows 2003 server which was once a physical box, but now is virtualized in vmware. There are two SQL Server instances running. One is 2000 Standard, another is 2008 R2 Express. The machine supposedly has 3GB of RAM allocated to it, but SQL server instances appear to be using very little amount of RAM. If I combine all the processes I get about 500MB of used RAM. But it looks like there isn't much available physical memory too. So the question is: how can I find what's using the RAM?

enter image description here
enter image description here

Best Answer

For the SQL 2008 instance, try running this code:

-- server wide info
select 
-- sp_configure
    (select value_in_use from sys.configurations where configuration_id = 1543) as 'Min Mem',
    (select value_in_use from sys.configurations where configuration_id = 1544) as 'Max Mem',
-- buffer pool & plan cache
    (select cast(count(*) * 8.0 / 1024.0 as numeric(10,2)) from sys.dm_os_buffer_descriptors) as 'Buffer Pool MB', 
    (select cast(sum(cast(size_in_bytes as bigint)) / 1024.0 / 1024.0 as numeric(10,2)) from sys.dm_exec_cached_plans) as 'Plan Cache MB',
-- perfmon
    (select cntr_value from sys.dm_os_performance_counters where object_name like '%Buffer Manager%' and counter_name = 'Page Life Expectancy') as 'PLE',
    (select cntr_value from sys.dm_os_performance_counters where object_name like '%Memory Manager%' and counter_name = 'Memory Grants Pending') as 'MGP'
go

This will tell you your Max and Min settings for SQL Server memory, and it will tell you how much is being used for your Buffer Pool and Plan Cache. Page Life Expectancy is how long data is staying in memory, the longer the better. Memory Grants Pending is how many queries are stuck waiting on memory, the lower the better.

On Task Manager, click on the Processes tab and click the checkbox for 'Show processes from all users'. Then click on the Memory column to sort by memory usage. See if there are any other programs gobbling up your memory.

Download the SysInternals tools and run Process Explorer, procexp.exe. This will give more detailed information than Task Manager.

Since it's a virtual machine, get with your VM Admin and check the following: Reservations, Limits, and Ballooning. You want Reservation to equal the memory you think you have set for the server. You want Limit and Ballooning to be zero.

Happy Hunting!

Related Topic