March 16, 2011 at 3:27 pm
I'm looking at a situation where we have 3 servers with different SQL services on them. My goal is to consolidate due to licensing issues. The 3 existing servers are as follows:
SSRS - Win2k3 w/ IIS 6, x86, 1CPU, 2GB Memory
SQL/SSIS/Ageng - Win2k3, x86, 2CPU, 3.75GB Memory
SSAS - Win2k3, x86, 1CPU, 2GB Memory
These three servers average under 50% memory usage and under 20% CPU usage. CPU on one server (SQL) occasionally spikes to 85% during maintenance tasks.
I've been given permission to replace all three servers with a single new server.
Win2k8R2, x64, 4CPU, 16GB Memory
Memory allocation ideally would be 8GB SQL, 4GB SSAS, rest to OS/SSRS/SSIS.
I've read different opinions about hosting all services on a single server, and my priorities are performance with cost (licensing) in a close 2nd...and the costs are the item that the budgets are really not liking at the moment. We have 3 of these setups (Dev, Test, Prod).
Questions....
1. Seeing how the CPU and Memory usage on the servers is rather low, will the new server really have any issues with all SQL services on it?
2. Are my memory allocations realistic?
3. How do I limit SSAS to 4GB? I'm guessing LowMemoryLimit 25% and TotalMemoryLimit at 75% but all the different articles are confusing to interpret.
And thanks to anyone who answers. I've been reading these forums for years and I've always found clear answers to my questions until now. 😀
March 16, 2011 at 7:41 pm
Kudikai (3/16/2011)
I've been given permission to replace all three servers with a single new server.Win2k8R2, x64, 4CPU, 16GB Memory
Memory allocation ideally would be 8GB SQL, 4GB SSAS, rest to OS/SSRS/SSIS.
Overall you will probably be fine as long as the storage can keep up with all of that.
I would probably go with a two CPU box (X5680) and spend the extra money on more RAM (16GB isn't much) and storage. (Assuming you aren't using a SAN.) I think that for the difference in cost you could probably easily go with 190+GB of RAM, and the current processors, especially the X5680 and X5690, will be way more than the three old servers put together.
If you haven't seen Glenn Berry's presentation on picking hardware you should. There is a link on this page: http://jasonbrimhall.info/2011/03/15/march-2011-s3olv-meeting-recap/
You could go with VMs to limit memory but that really messes with the licensing.
March 16, 2011 at 9:35 pm
Another thing to think about is that unlike older 1 and 2 CPU servers that are SMP, Symmetric Multi Processing, which shares all memory across all CPUs, almost all current servers are NUMA based, Non-Uniform Memory Access. That means that each NUMA group has it's own memory. Depending on the server configuration a 4 CPU server probably has either 2 of 4 NUMA nodes.
Assuming 2 nodes, that means each pair of CPUs would share 8 GB of memory in your configuration. If there were 4 nodes each CPU would only have 4 GB of memory.
Depending on your CPU if you have 6 cores with HT, Hyper-Threading, that would mean you have 12 processing threads sharing 4GB of memory.
One of the advantages of NUMA is that by setting the processor affinity you can limit the memory available to a process. i.e. If you limit SSAS to logical CPUs in a single NUMA node, they can only use the memory in that node. (At least that is my understanding.)
As far as limiting the SSAS memory my understanding is that if you set the LowMemoryLimit and TotalMemoryLimit settings to more than 100 they are interpreted as bytes so you should be able to set them to 4294967296 to limit it to 4GB.
Again, I would really look at dropping a couple CPUs and really bumping the memory up, and the new server will just fly.
March 17, 2011 at 4:26 am
Are you talking about physical CPUs or cores on a CPU?
From the above I would be getting as much memory as possible and reducing the cores/CPU as well.
What is the disk setup/configuration? I am guessing that you are not running a SAN and will be using internal disks? This is where the biggest bottleneck will be.
Also keep in mind, that with 64bit servers you will need to keep a fair bit of memory for the OS (and other items, AV, backup, etc) to operate efficiently. See:
March 17, 2011 at 4:50 am
What is missing in this calculation is attention to the disk subsystem. I have worked on far too many systems that have been configured with little attention to the disk subsystem.
If this is to be configured with internal SCSI array's, then you need to consider the controller and the number of PHYSICAL arrays you will be using.
If you are configuring this on a SAN, is it an iSCSI interface or a fibre interface?
Do a search for Joe Chang. He has written many articles on hardware configuration for SQL Server.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 17, 2011 at 8:50 am
UMG Developer (3/16/2011)
You could go with VMs to limit memory but that really messes with the licensing.
I really should have specified that our entire environment is virtual, and the licensing issues are the primary push for the new virtual server to replace 3 virtual servers. There is a SAN and the new server would be setup with drives on different LUNs...probably 5 drives at this point.
Additional info I just received (servers are part of a company we just acquired so I'm getting details as they come in) is that their primary database is 90GB and the cube refreshes take an hour and the reports run even longer. Looking at the current resources those VMs are allocated I can understand why it takes forever to do anything. This reinforces the suggestion to go with more memory. That will be a political battle with the server admins that I'll probably loose, but at least they gave me 16GB to start with.
Sometimes I miss the days when servers were physical machines and I didn't have to fight with other servers for resources like memory. *sigh*
Thanks for the info!
March 17, 2011 at 9:19 am
That is the proverbial horse of a different color!
As for getting more memory from the admins, I have no advice other than begging and pleading.
Good luck!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 17, 2011 at 10:01 am
Kudikai (3/17/2011)
UMG Developer (3/16/2011)
You could go with VMs to limit memory but that really messes with the licensing.
I really should have specified that our entire environment is virtual, and the licensing issues are the primary push for the new virtual server to replace 3 virtual servers. There is a SAN and the new server would be setup with drives on different LUNs...probably 5 drives at this point.
You do realize that once you go to a virtual machine that you go from per CPU socket licensing to per logical processor licensing. For example if you go with a 2 X5680 CPU box you have 12 cores with HT for 24 logical CPUs.
* For a physical installation of SQL Server you have to have 2 CPU licenses. For the Stadard edition that would be about $14,000. (2 * $7k)
* If you expose all of those logical CPUs in a VM you now need 24 CPU licenses. For the Standard edition that would be about $168,000. (24 * $7k)
So as you can see virtualizing SQL Server cane cause licensing issues. It appears if you go with Enterprise edition you don't have that problem, so a 2 CPU box would cost you about $54k regardless the number of VMs/logical processors. (Make sure you check with your licensing person to make sure you have the correct licenses for your needs.)
Of course if you are using Hyper-V you are limited to 4 logical CPUs per VM.
In any case I would say 16GB of RAM isn't enough, ask for more.
March 17, 2011 at 1:14 pm
Regarding VM licensing, we've just been through it with our Microsoft rep. They have a licensing model that provides a benefit for virtual servers. See page 3 of this PDF:
It is a little confusing, but comes down to the # of cores in a single physical CPU.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply