-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

peterh
  • 4,953
  • 13
  • 30
  • 44
John
  • 7
  • 3
  • 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 08: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 08:51
  • Have you used the `/3gb` or `/userva` in your boot.ini or AWE / lock pages in memory with SQL. – Bernie White Nov 29 '12 at 09:14
  • What you see on the 1st screenshot is pagefile usage, its size can be set in Advanced system properties, this is NOT your RAM. Virtual memory is an amount of "RAM"(installed on your system) + "pagefile.sys"(can be set manually). Both, - virtual memory and pagefile are the techniques for memory management in OS which allow apps to see only one type of memory - virtual memory. I recommend reading [this article](http://en.wikipedia.org/wiki/Virtual_memory). – Volodymyr Molodets Nov 29 '12 at 09:21
  • So, if you disable pagefile what I do not recommend to do, the amount of RAM will be equal to Virtual memory size. – Volodymyr Molodets Nov 29 '12 at 09:37
  • I know that pagefile is not RAM and i am looking at "Physical memory" figures in that screenshot. – John Nov 29 '12 at 10:45
  • Des the user account that is running the SQL Server 2000 instance have the `Lock Pages in Memory` priv? – MDMarra Nov 29 '12 at 17:56

1 Answers1

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!

Steven
  • 176
  • 3
  • 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 08:44
  • I tried process Explorer, but it seems to be showing the same amount of used RAM. – John Nov 29 '12 at 09:03