asked Dec 07 '10 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 '10 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 '10 at 10:44 AM