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%'
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.