July 20, 2012 at 9:02 am
I've been given a VMWare Virtual Machine with the following:
Windows Server 2008 R2 64bit
8GB Ram
I have installed SQL Server 2008 R2 Enterprise 64bit.
What is the best practise relating to memory settings on the platforms mentioned above. i.e. AWE Settings and Lock Pages in Memory.
I believe SQL Server only uses 2GB Ram out of the box or is this just 32bit platforms.
This article mentions
I tend to start out by reserving 1-2 GB RAM for the OS, and then an additional 1GB for each 4 GB of RAM installed from 4-16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. I then monitor the Memory\Available Mbytes counter over time to determine peak memory usage is for the system. Memory in excess of what's required to support this peak memory usage can be added to the 'max server memory' option.
Based on the 8GB Ram I have got, this seems to indicate that I should set Min Memory to 1-2GB and Max Memory to 2GB(1GB for each 4 GB of RAM installed from 4-16 GB)
This seems low and a pointless exercise in messing with the memory settings. I guess memory settings are only really useful with anything above 8GB Ram.
July 20, 2012 at 9:11 am
thunderousity (7/20/2012)
I've been given a VMWare Virtual Machine with the following:Windows Server 2008 R2 64bit
8GB Ram
I have installed SQL Server 2008 R2 Enterprise 64bit.
What is the best practise relating to memory settings on the platforms mentioned above. i.e. AWE Settings and Lock Pages in Memory.
I believe SQL Server only uses 2GB Ram out of the box or is this just 32bit platforms.
The AWE setting is not applicable on 64 bit systems so ignore it. What you may want to do is set the local policy "lock pages in memory" for the SQL Server service account. This policy allows the service to use the AWE APIs to manage memory.
thunderousity (7/20/2012)
This article mentionsI tend to start out by reserving 1-2 GB RAM for the OS, and then an additional 1GB for each 4 GB of RAM installed from 4-16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. I then monitor the Memory\Available Mbytes counter over time to determine peak memory usage is for the system. Memory in excess of what's required to support this peak memory usage can be added to the 'max server memory' option.
Based on the 8GB Ram I have got, this seems to indicate that I should set Min Memory to 1-2GB and Max Memory to 2GB(1GB for each 4 GB of RAM installed from 4-16 GB)
This seems low and a pointless exercise in messing with the memory settings. I guess memory settings are only really useful with anything above 8GB Ram.
That article is a generalisation, and its probably a valid starting point. Microsoft recommend that you only implement the LPIM local policy if you see errors in the log related to memory being paged out.
On a 64 bit system definitely set the max memory, especially if LPIM has been applied.
On a virtual machine the LPIM setting can affect the VM balloon driver, most people advise against setting this on a virtual machine but it all depends on your setup and what other VMs are sharing your host resources.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 20, 2012 at 9:15 am
Hi mate,
2GB is definitely low!
As this is 64-bit Windows & SQL you can ignore AWE.
With 8GB I'd be looking at setting max memory to 5-6GB if there's no other applications running on the server.
Min memory would be fine at 1 or 2 GB but if there's nothing else on there I wouldn't worry about it.
Cheers
July 20, 2012 at 9:23 am
thunderousity (7/20/2012)
I tend to start out by reserving 1-2 GB RAM for the OS, and then an additional 1GB for each 4 GB of RAM installed from 4-16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. I then monitor the Memory\Available Mbytes counter over time to determine peak memory usage is for the system. Memory in excess of what's required to support this peak memory usage can be added to the 'max server memory' option.Based on the 8GB Ram I have got, this seems to indicate that I should set Min Memory to 1-2GB and Max Memory to 2GB(1GB for each 4 GB of RAM installed from 4-16 GB)
Err, no. Other way around.
The 1-2 GB of memory is what you reserve for the OS, not what you set min server memory to.
Using that formula (which is a pretty good starting point usually), you'd reserve 1-2 GB for the OS, then another 1 (which is the 1GB for each 4GB between 4 and 16), leaving you with a figure of 2-3 GB reserved for the OS. That means, you'd set max server memory = total memory (8GB) - amount reserved for the OS (let's say 2GB), giving a figure for max server memory of 6GB.
Then monitor as the article suggests.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2012 at 2:49 am
Ah, Yes I've read the original quote again and what you say makes more sense GilaMonster.
I was reading it incorrectly.
Thanks I'll set it and monitor it.
Will 64bit SQL Server max out at 2GB out of the box or is this just 32bit?
July 23, 2012 at 3:58 am
thunderousity (7/23/2012)
Ah, Yes I've read the original quote again and what you say makes more sense GilaMonster.I was reading it incorrectly.
Thanks I'll set it and monitor it.
Will 64bit SQL Server max out at 2GB out of the box or is this just 32bit?
only applies to 32 bit, hence why you need to set the max mem on 64 bit
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply