Setting Max Memory in SQL and Creating Multiple TempDB

Question

question

Leo asked Dec 7, '10

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?

sql-server-2008 sql performance tempdb

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-12-07T11:25:38Z

WilliamD answered Dec 7, '10

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.

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Dec 07, 2010 at 11:46 AM

Said you would be along shortly... ;-)

0 ·

Oleg commented · Dec 07, 2010 at 12:30 PM

@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 :(

0 ·

WilliamD commented · Dec 07, 2010 at 12:45 PM

@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

0 ·

Leo commented · Dec 08, 2010 at 04:36 AM

Thanks. +1 for you both. I will have a look as Will said to look at VM setting and get back to you shortly.

0 ·

Leo commented · Dec 08, 2010 at 04:41 AM

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.

0 ·

Show more comments

Answer 2 · 2010-12-07T10:44:07Z

ThomasRushton answered Dec 7, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question