Thread: increasing memory
05-30-08, 10:13 #1Registered User
- Join Date
- May 2008
Unanswered: increasing memory
I've received conflicting information from Microsoft personnel so thought I'd see what some thoughts here are.
In summary, we upgraded a server from SQL Server 2000 SP4 Standard to SQL Server 2005 SP2 Standard. This servers main purpose is to handle alot of merge replication to anonymous pull subscribers. We have some Transactional replication also occurring. There are 8GB memory on the server.
During the upgrade we ran into memory pressure on MemToLeave. We put the /3GB parameter in boot.ini and -g512 on the startup per Microsoft's suggestions. This got us past the upgrade process.
After the upgrade, we took off the boot.ini setting and the -g512. We enabled AWE and assigned 6GB to SQL Server. Then once in a while when the merge snapshots were running, we'd receive some "system out of memory" errors. I went ahead and put -g512 back on and haven't received the error since.
My question to Microsoft then was if we go to say 16GB of memory on the box and give say 14GB to SQL Server, would it be beneficial to set the -g option to a higher number. That's when I got into a discussion with the Microsoft person that SQL Server 2005 Standard would not use anything above 4GB, which is opposite what the Microsoft site says, others have said, and opposite to what I'm seeing for memory usage with DBCC MEMORYSTATUS showing the 6GB being used We'll be talking to our TAM about our suppport, specifically on Replication topics, as we've had some problems getting knowledgable support on this topic. If anyone knows of support outside of Microsoft on Replication topics, I'd love to hear about it.
Any thoughts on the tweaking of memory related to our environment? I know it may be site-specific and we may have to do some trial and error, but with:
1. Doing heavy merge replication processing on the server(1,500 subscribers).
2. say we get 16GB on the box(server is Windows 2003 SP2 Enterprise)
are there some suggestions on a -g setting to best utilize Buffer Pool and MemToLeave ? Some other things to do? Is there some process/method to help determine how best to define the memory settings? If there a way to see how much BPOOL and/or MemToLeave the system is using at a given moment? DBCC MEMORYSTATUS gives alot of info, but I'll be the first to admit that I don't know what alot of the info there is really telling me. If there some white paper, etc that would help determine what the system is doing memory-related, that'd be great to know.
Thanks for any help,
05-30-08, 10:33 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
A lot of questions, huh?! I'm surprised to hear that MS recommended to set /3GB at the beginning. Without seeing your your errors verbatum, I still remember that those "out of memory" errors are the result of kernel memory being depleted, which prevents the OS from successfully managing the user mode memory (and SQL in particular). I am even more surprised that they didn't couple /3GB with /userva, which only means that they really lowered the bar...The more memory you add to a 32-bit OS, the less capable is the kernel memory manager to virtualize it and present it to the user apps (one of the main reasons to move to 64-bit)...
I still don't think you need to give up on MS. Just keep bugging your TAM that you're dissatisfied with the level of knowledge, and eventually (sooner rather than later) they'll send you the right guy."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
05-30-08, 10:47 #3Registered User
Provided Answers: 13
- Join Date
- Jan 2003
Since you have a box with 6 GB of memory, any reason to stay away from 64-bit SQL Server 2005?
As I recall, the Out of Memory error usually has to do with a problem allocating a contiguous section of memory, rather than actually running out of memory. it has been a few years, so please correct me if I am wrong, the -g parameter tells SQL Server to try to keep that amount of memory free for such large operations. The only time I have seen these Out of Memory errors was with a Sharepoint database server before Sharepoint 2003 SP1.