Why isn't SQL Server using the Max Server Memory setting?

Question

I have a SQL Server machine with the following:

• Windows 2003 Enterprise SP2 (32-Bit)
• SQL Server 2005 (32-Bit)
• 12GB RAM
• /PAE switch set in boot.ini
• 6 SQL instances, all with AWE enabled
• No other applications running on the machine.

I have the Max Server Memory usage set at what I believe are acceptable values (about 10GB combined, leaving 2GB for Windows). I am using the following query to show a combined total RAM usage of buffer pool and non-buffer pool. I am also showing the MAX SERVER MEMORY setting. The databases on each of the instances are far bigger than the allocated RAM, even though a couple instances get far more use than others. Shouldn't the actual memory in use always be pretty close to the MAX SERVER MEMORY setting? I am finding a gap that made me curious.

DECLARE @TotalRam as decimal (10,2)

--NON-BUFFER POOL
SET @TotalRam = (SELECT sum(pages_allocated_count)/128.0 [Non-Buffer (MB)]
from sys.dm_os_memory_cache_entries
where pages_allocated_count > 0)

--BUFFER POOL
SET @TotalRam = @TotalRam + (
SELECT CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Buffer Pool (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4 AND database_id <> 32767 )

SELECT @TotalRam as RAMinUSE, value as MaxServerMemory 
FROM sys.configurations
WHERE name like '%max server memory%'

Query results:

I would like to add 12GB more RAM to this machine for the two heavy instances and I want to be sure that it will be used and that I do not have something configured incorrectly. I have verified that the editions of SQL and Windows installed will support 24GB RAM.

Is it normal to see a gap between these values? Perhaps my queries are missing something.

Do you folks see any issues with going from 12GB to 24GB RAM?

Also, Available MBytes remaining in PerfMon shows around 600MB. That number must be based on what's left after the allocation of MAX SERVER MEMORY, right? Otherwise I would expect to see a good bit higher.

Answer 1

The actual memory in use won't increase until you actually load data into memory. It's not going to jump to 10GB combined allocated just because that's what you've set max memory to and because your databases are at least that large. It's a max, not a min (and never mind that min doesn't work that way either), and it isn't going to try to guess - before you've run enough queries - which tables / indexes it should load into buffer pool memory.

So yes, it is normal to see a gap in these values. You can probably get much closer to the max values if you pump the buffer pool by issuing a bunch of SELECT * FROM dbo.BiggestTables; on each instance, but it is usually better to let SQL Server allocate the memory as it deems appropriate based on actual usage by your real application. And remember too that max server memory doesn't just cover the buffer pool.

In short, SQL Server is pretty good at memory management; let it do its thing, and worry about adjusting max memory on your various instances when you have an actual performance issue.