x

Setting Max Memory in SQL and Creating Multiple TempDB

Hi,
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?
more ▼

asked Dec 07 '10 at 09:20 AM in Default

Leo gravatar image

Leo
1.6k 51 56 58

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Dec 07 '10 at 11:25 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

Said you would be along shortly... ;-)
Dec 07 '10 at 11:46 AM ThomasRushton ♦

@WilliamD Is it at all possible that the problem is not that VM is "seeing 32GB" but rather the original max server memory (MB) setting propagated to all VMs, that is all? For example, out of the box installation of SQL Server 2005 results in this value for server memory setting: 2147483647 MB. This is surely more MBs than any existing server has, the number is simply the maximum value that can fit into 4 bytes (int), and it is there to simply allow SQl Server to use as much memory as the system has even at the cost of starving OS. In other words, the step outlined in Thomas' answer is simply a necessary step, which must be completed.

I heard that if tempdb is split then it only makes sense if each piece has its own spindle. Otherwise, it does not really make sense to split it due to contention. Since I am not a DBA, I cannot swear that this assumption is correct though :(
Dec 07 '10 at 12:30 PM Oleg

@Oleg - you are right about the max setting being stupid by default. The OP mentions that he normally sets this to a sensible maximum, but when he logs onto each VM (before setting the max), each VM is taking 32GB in windows (is how I understand it)

The spindle split is not necessarily needed. The contention issue can be "logical" as opposed to physical (again as I understand it), this has to do with the actual activity in tempdb. Take a look at [Paul Randal's explanation][1], heed the PS at the end.

[1]: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx
Dec 07 '10 at 12:45 PM WilliamD
Thanks. +1 for you both. I will have a look as Will said to look at VM setting and get back to you shortly.
Dec 08 '10 at 04:36 AM Leo
Oleg - you are absolutely correct. Just check my local dev machine it has only 4GB but in SQL Max Memory, it shows 2147483647MB. That will surely more MBs then any existing server has as you said. Thanks.
Dec 08 '10 at 04:41 AM Leo
(comments are locked)
10|1200 characters needed characters left

(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.

exec sp_configure 'max server memory (MB)', 1024
reconfigure with override

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...
more ▼

answered Dec 07 '10 at 10:44 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.4k 14 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x673
x242
x49

asked: Dec 07 '10 at 09:20 AM

Seen: 2291 times

Last Updated: Dec 07 '10 at 09:20 AM