December 29, 2012 at 8:05 am
I'm trying to determine the best allocation of memory on a virtual(hyperV) 2008 Server that has sql server 2008r2 installed with a default instance and two named instances. I'm fairly new to both virtual servers and named instances. I have read that I should utilize the min memory and leave the max memory alone. currently the server has 12 gb's of memory, which I'm sure needs to be increased. I've currently got 3.5 gb's as the min memory for all three instances which only leaves me 1.5 gb's for the OS which is probably way to low. I'm about to change the min memory so that more memory is allocated to the OS(4 gb's) and the rest for the instances(i.e. default and two named). I'm going to have 4.5 gb's for the OS and 2.5 gb's each for the default and two named instances.
December 29, 2012 at 9:35 am
ericwenger1 (12/29/2012)
I'm trying to determine the best allocation of memory on a virtual(hyperV) 2008 Server that has sql server 2008r2 installed with a default instance and two named instances. I'm fairly new to both virtual servers and named instances. I have read that I should utilize the min memory and leave the max memory alone. currently the server has 12 gb's of memory, which I'm sure needs to be increased. I've currently got 3.5 gb's as the min memory for all three instances which only leaves me 1.5 gb's for the OS which is probably way to low. I'm about to change the min memory so that more memory is allocated to the OS(4 gb's) and the rest for the instances(i.e. default and two named). I'm going to have 4.5 gb's for the OS and 2.5 gb's each for the default and two named instances.
Where did you read that? With servers hosting multiple instances you'll typially want to set both min and max. On servers hosting just one instance the recommendation is typically to only set max memory. I have not seen anyone comment on leaving max memory at it's default in any x64 scenario.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 29, 2012 at 2:40 pm
Thanks for your prompt reply. I'm absolutely confident that you know more than I about this matter.
Here is the link that I used:
Given the fact that I have essentially 12 Gb's of Memory on this Virtual server, what would you recommend carving up the memory?
I really appreciate you assisting me on this. My experience revolves around Servers with just a default instance and very little need for ajusting the sql server memory.
Thanks in Advance!
December 29, 2012 at 4:32 pm
ericwenger1 (12/29/2012)
Thanks for your prompt reply. I'm absolutely confident that you know more than I about this matter.Here is the link that I used:
Given the fact that I have essentially 12 Gb's of Memory on this Virtual server, what would you recommend carving up the memory?
I really appreciate you assisting me on this. My experience revolves around Servers with just a default instance and very little need for ajusting the sql server memory.
Thanks in Advance!
Not a problem. The article does mention that max memory should not be left at its default, which is good.
Not knowing anything about the total size of the databases you'll be hosting in each instance or what types of things those databases will do that require non-buffer-pool memory, let alone the expected workload requiring buffer-pool-memory it's impossible for me to recommend anything specific in the way of settings. If you're not sure, slice it up evenly. In any event you should monitor the size of each buffer pool and the available megabytes at the OS level until you get a handle on what each requires and where you can rebalance the settings towards or away from any one instance. The article contains very fair guidelines in terms of how you should configure each instance, but again, nothing specific for good reason. I will reiterate these items though:
- definitely set min memory and max memory in each instance
- the total of max memory across all instances should not exceed the total memory of the server. I would start conservatively by leaving ~3GB (i.e. total max memory should be 9GB across all three instances). This is just an initial conservative starting point without knowing anything about the databases or the expected workload. Basically what this says is that 3GB will always be left for non-buffer-pool memory across all three instances as well as non-SQL Server services running on the OS. Monitor the OS'es 'Available Megabytes' perfmon counter and if you get low then back down max memory on one or more instances to allow more for the OS.
- min memory should not be set the same as max memory
Note that the min memory and max memory settings only affect buffer pool allocations in SQL 2008 R2.
Read this entire book, start with Chapter 4. Buy it from Amazon or download the free ebook: Troubleshooting SQL Server: A Guide for the Accidental DBA By Jonathan Kehayias and Ted Krueger.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 29, 2012 at 7:08 pm
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply