Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
How big are your databases on each instance and do you have a Max Memory value set for either instance? –  MDMarra Nov 28 '12 at 19:52
    
Database running on 2000 Standard is almost 12GB. It is limited to 2048MB of RAM. Task manager shows it using less than 250MB of RAM at peek. Database running on 2008 R2 seems to be only about 64MB. It has no limit on RAM. Task manager shows it using less than 45MB of RAM at peek. Both databases are used heavily during the day. –  John Nov 28 '12 at 20:06
    
Can you post a screenshot of the memory consumption of the actual processes? –  MDMarra Nov 28 '12 at 21:27
    
I've added second screenshot with process list. –  John Nov 29 '12 at 8:44
    
Also I have restarted 2008 R2 at night, so it managed to take a bit more memory ~90MB, because 2000 was using a bit less when idle (you can see that in the screenshot). –  John Nov 29 '12 at 8:51
show 5 more comments

1 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!

share|improve this answer
    
Min mem: 8 Max mem: 2147483647 Buffer Pool MB: 17.73 Plan Cache MB: 2.97 PLE: 3 MGP: 0 –  John Nov 29 '12 at 8:44
    
I tried process Explorer, but it seems to be showing the same amount of used RAM. –  John Nov 29 '12 at 9:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.