-1

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

10
  • How big are your databases on each instance and do you have a Max Memory value set for either instance?
    – MDMarra
    Nov 28, 2012 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, 2012 at 20:06
  • Can you post a screenshot of the memory consumption of the actual processes?
    – MDMarra
    Nov 28, 2012 at 21:27
  • I've added second screenshot with process list.
    – John
    Nov 29, 2012 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, 2012 at 8:51

1 Answer 1

0

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!

2
  • Min mem: 8 Max mem: 2147483647 Buffer Pool MB: 17.73 Plan Cache MB: 2.97 PLE: 3 MGP: 0
    – John
    Nov 29, 2012 at 8:44
  • I tried process Explorer, but it seems to be showing the same amount of used RAM.
    – John
    Nov 29, 2012 at 9:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .