May 30, 2008 at 7:32 am
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 support, 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,
Doug
May 30, 2008 at 12:16 pm
As far as the amount of memory SQL 2005 Standard can use - it is limited to the OS limit. I would have to guess you are using the standard edition OS which has a 4gb memory limit in the 32 bit version.
http://msdn.microsoft.com/en-us/library/aa366778(VS.85).aspx
So, unless the OS can handle more memory, increasing it will not help. If the OS can, remember that anything over 4gb on a 32 bit OS is going to use AWE. AWE memory access is much slower than accessing physical memory and SQL Server cannot use AWE memory for all types of memory operations, so it may not help you at all. In fact, if memory serves me, the -g switch will not use the AWE memory which would mean increasing this number will put more pressure on some operations that cannot be helped with more memory.
You would be better off starting on the preparations to move to 64 bit. Other than some licensing costs (which are not much different) the hardware costs are now the same.
May 30, 2008 at 12:44 pm
Read Slava Oks's WebLog more details on this...
http://blogs.msdn.com/slavao/default.aspx
I don't think licencing cost is more for 64 bit but you need to spend money on HW...
If you don't want to put the money at all...
Just add the -g512 as startup parameters...eventhough you may encounter this issue after adding the g switch...all you need to do is restart the sql to resolve...
MohammedU
Microsoft SQL Server MVP
May 30, 2008 at 12:59 pm
How does someone determine what is the best value for the -g option? Is it strictly trial and error?
I was hoping Microsoft or someone could give me recommendations on what the best/proper settings should be.
May 30, 2008 at 1:07 pm
Yes you are right it trial and error basis..
Some time you may get the error even if you set it to highier value....
This memory is used by com objects, exdended procs, linked server calls etc...
There is no best values...:)
MohammedU
Microsoft SQL Server MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply