Weird SQL Server memory errors after upgrading to 64-bit

Question

We've just "upgraded" our production database server from 32-bit to 64-bit. It's running SQL Server 2005 Standard on Windows Server 2003. During the night after the upgrade the server was unavailable for nearly an hour - client requests were timing out. The problem then seemed to fix itself. The only clue I have as to the problem is what's in the SQL server logs:

LazyWriter: warning, no free buffers found.

Memory Manager VM Reserved = 8470288 KB VM Committed = 2167672 KB AWE Allocated = 0 KB Reserved Memory = 1024 KB Reserved Memory In Use = 0 KB

Message Memory node Id = 0 VM Reserved = 8464528 KB VM Committed = 2162000 KB AWE Allocated = 0 KB SinglePage Allocator = 103960 KB MultiPage Allocator = 31832 KB

MEMORYCLERK_SQLGENERAL (Total) VM Reserved = 0 KB VM Committed = 0 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 4352 KB

Then there are many more messages like it starting with MEMORYCLERK.

Does anyone know what is going on? It seems like it's run out of memory and, granted, the server only has 2GB of physical RAM, which isn't very much by today's standards, but surely it shouldn't just completely STOP WORKING? Should I set the maximum memory SQL is allowed to use to 1.6GB or so? Is there something else I can do (OTHER THAN installing more RAM, obviously)?

Answer 1

There's been some sporadic reports of MSSQL allocating enough memory to cause page faulting to disk[1] - which, of course, results in drastically decreased performance.

Though I haven't seen anything official from MS, reports are that setting max memory to somewhere between 512M and 1G less than physical RAM should help.

Enterprise Edition allows you to keep MSSQL pages from being paged out, which should also solve the problem. And, obviously, more RAM will help - but probably may not alleviate it.

[1] There is some debate as to whether MSSQL is trying to allocate too much RAM, the OS is paging it out, or MSSQL is just allocating to the wrong pools. Regardless, max mem should help cases 1 and 2, and SP2 is supposed to solve 3.

Edit: A colleague pointed me to a related KB article with a few hotfixes listed. It references different error messages (are you running SP2?), but the symptoms and behavior seem to fit your situation.