We help IT Professionals succeed at work.

SQL Server Question and RAM usage...

tdmgtech asked
Long story short, I inherited an older sql server.

Its running 2003 Server Entperise, but has SQL 2008 standard installed. At the time of acquisition it had 4gb of ram and 8 SQL instances installed.

It also had terminal services installed on it with people occasionally logging into it to run GIS applications which were dependent on SQL

For the time being, lets ignore all of the bad practices since I can't change this around for the next few months due to work flow.

The default instance would generally grab ~1.5 to ~2gb of ram and all of the others would suffer with whatever dregs they could snag.

I chucked 16gb of ram in the box, made sure /pae settings were set in the boot.ini, configured awe memory settings in sql, etc.

Now the SQL instances will only use ~170mb of ram. Performance is no worse, and maybe a little better, but I'd like SQL to actually USE the ram we've installed.

Note the server is using more ram, but I think a lot of it is going towards terminal sessions.

Its currently using ~9gb of ram, has 10gb of page file in use and has all 8 sql instances using ~150 to ~170mb of ram.

Watch Question

You mentioned there are 8 instances installed.  Did you do the following for each instance?

use master
exec sp_configure 'Max memory setting(MB); 1024
exec reconfigure override

This would give each instance a max memory of 1GB.  You can customize each instance according to the need and availability.


Is independent of any AWE memory configs?  i've right click on the instance, gone to properties / memory and set awe memory to have a min of 1024mb and a max of either 2048mb or 4096mb depending on what instance it is.  Only one is set to 4096mb....
Before I ran the memory settings query above, I ran

EXEC sp_configure;

This listed all of the running config apparently.  My settings from the AWE configuration appear to be in the running config.

Here's what was in my default instance.

name                                minimum                        maximum         config_value            run_value
max server memory (MB)      16                          2147483647      4096                  4096

min server memory (MB)                0                          2147483647      1024                1024

So, it looks like thats not the issue...thanks though