SQL Memory Couters After RAM Addition

Apr 22, 2009 at 11:00 PM
I'm wondering if someone can give me a quick analysis of the following behavior.  I installed more RAM into one of our hosts (now at 8GB).  /PAE  is enabled.  SQL 2005 has a max server memory setting of 4GB.  I am now getting the following warning alerts consistently all day long.  

4/21/2009 4:20:00 PM Condition Counter Min Avg Max Hourly Trend
High User-mode Memory Usage - consuming more than 80% of available virtual memory \\TPX-SCRAPER\Process(sqlservr)\Virtual Bytes 1,799,176,192 1,799,176,192 1,799,176,192 -147,456
Target Server memory is more than 500MB's than Total Server Memory \\TPX-SCRAPER\SQLServer:Memory Manager\Target Server Memory (KB) 4,194,304 4,194,304 4,194,304 157,597

Can anyone give me any pointers on how to resolve these alerts?
Apr 28, 2009 at 12:17 AM

Hi egrace,

You can ignore the Virtual Bytes alert because it is normal for SQL Server to consume as much virtual memory as it can (2GBs).

When Target Server memory is significantly greater then Total Server Memory, then it typically means that SQL is running out of memory. Unfortunately, there isn't much you can do about this because of the limitation of the 32-bit address space. In a nutshell, your SQL Server *really* needs to be on 64-bit.

The 32-bit address space is only 2GB by default per process (like SQL). This was great back in the late '90s when we only had 8MBs of physical memory. Every process had 2GBs of virtual memory which was *huge* back then. The problem is that today's servers have far more physical memory than virtual memory. Servers are supposed to have plenty more of virtual memory than physical memory, so everyone should have been on 64-bit 10 years ago. Unfortuantely, 32-bit servers are still the majority of servers in production. Hacks like /3GB have been implemented so that processes can address up to 3GBs versus the standard 2GBs, but many people don't realize is that the virtual memory was stolen from the kernel. I've seen plenty of problems where the kernel is depleted of resources such as PTE and causing the server to hang frequently.

Anyway, go to 64-bit as soon as you can because SQL is being held down by the 32-bit virtual address space.