We are Virtualizing the SQL Servers in W2k8 by using HyperV. I have 7 Virtual Servers and assigned 4GB Memory for each VM at the moment. But main Server has 32GB Memory so that it shows 32 GB in Maximum Server Memory when I log into any VM machine.So it look like SQL Server I have installed on each VM is using Memory from the Main Server, not from the local VM machine I setup with 4GB? Normally, I set the Max Memory for all of my production server (*Total Memory - 2GB for Operating System*). Do I still need to apply for VM as well? Assign Max memory to 3 GB?
Also that might be completely diffient question - according from Microsfot and BOL, it said creating the Multiple TempDB will get a better performance of the system. Let say I have 4 processors with 4 core so that I should create 16 TempDB to get better performance?
asked Dec 07, 2010 at 09:20 AM in Default
Excellent points from Thomas +1!
Leo - you are right in your assumption about setting the Max for each server. In that respect, you treat the VMs as "real" servers and make sure not to starve the OS, but give as much as possible to SQL Server.
I am not too hot on HyperV, so why each VM is "seeing" the 32GB is a mystery to me (someone else care to explain?) - are you sure that this is right? Do you maybe have a configuration error there somehow? If you set the guest VMs to use 8GB it should limit to that - have you accidentally activated something like thin-provisioning or some such "cheat" function that is messing up the numbers?
As to multiple TempDB files. I have heard a lot of different opinions on that, one that stuck in my mind was (this came from Kim Tripp in one of her workshops IIRC) - most people/companies don't really need to worry about splitting tempdb up, it is a premature optimisation that is not needed in most cases. The idea of splitting them is to increase performance on systems that create and destroy temp. tables/objects at a very high rate. This can cause contention, so splitting the tempdb files and placing them on separate storage is advantageous.I am not saying don't do it, or do it. As Thomas says, test, test, test - we don't know enough about your system to give hard recommendations. However, after talking with Kim about this, I was lead to believe that if you need that sort of performance tuning, you already have MS or SQLSkills or someone like that giving you a hand anyway.
answered Dec 07, 2010 at 11:25 AM
(1) Server memory. If you're worried about the memory consumed by each SQL Server instance, you can always configure them individually to use what you like.
will tell SQL Server to only use at most 1GB RAM.
(2) Multiple TempDB improving performance. "It depends." Is your server limited / restricted by the throughput to the TempDB? If so, then it may be an idea. There are no hard & fast rules. Try it. Run a typical load, then tweak the number of TempDB devices, and have another go. The only thing to be aware of is that the TempDB should (ideally) start off at a size that's not going to need to expand; also, keep the sizes of the TempDB devices in step - ie set them to have the same initial size, and the same growth rate.
As always, though - before making this sort of configuration change, take a performance benchmark / baseline with a typical load, then make the change, and rerun to see if the change has made the right sort of difference.I'm sure @WilliamD will be along in a while with various other helpful tips...
answered Dec 07, 2010 at 10:44 AM