October 5, 2011 at 1:00 pm
I am considering purchasing a new application server, to run SQL Server 2008 R2 x64 Enterprise, using a Windows Server 2008 R2 x64 Enterprise o/s.
The server i am considering is a Dell Poweredge R910 that holds 4 processors.
The server can hold up to 1TB of RAM and four 8 core processors.
Is it better to buy more RAM and wait for the 8 cores to come down in price, or buy more cores and wait for the price of RAM to come down?
What matters more to SQL, RAM, muticores or both?
Thanks
October 5, 2011 at 1:14 pm
That's a hard question to answer directly without knowing more about the nature of your application... the size of the database(s) and what sort of traffic load you'll be dealing with.
Most likely you'll be better served with more memory because you can use that now as well as in the future. IMHO, I'd be more interested in what sort of disk setup this systems have or are capable of.
Tell us more about the application(s) you plan to run and we can provide more intelligent comments.
😎
The probability of survival is inversely proportional to the angle of arrival.
October 5, 2011 at 1:18 pm
That really depends on your usage. I would typically go with RAM at this point, especially if this is strictly dedicated database server. My first 2 things are RAM and storage.
EDIT: Comparing the price of RAM vs CPU is really not much of a comparison. If you are looking for a bang for your buck, RAM is probably the best way to go at this point.
Thanks,
Jared
Jared
CE - Microsoft
October 5, 2011 at 1:33 pm
Current DB size 150 GB, 4 Million records.
Each day i load approx 5000 new records(via bulk insert), with an average size of 30k per record. This could increase to 30000 records per day.
Heavy searching load, using FTI, of the 4 Million records.
How much RAM would be a good starting point ?
4 GB RAM stick are still quite cheap, so i could get 256 GB, without costing too much!
I will be using:
Raid1 - Program Files (2 HD)
Raid10 - Data files (10 HD)
Raid1 - Log Files (2 HD)
Raid0 - TempDB files 2(HD)
The server can house 16 SAS drives, using 6 Gb/sec.
October 5, 2011 at 1:40 pm
Well... To be honest I think that would be overkill. I mean, it would be nice server! But you should think about ROI. We do a much larger load than that, and run on 32GB. Don't go overkill just because you can, do what you think will last you 3-5 years with no degradation in performance. Look at your total usage right now using performance counters. Get a baseline of what you are doing now.
Jared
Jared
CE - Microsoft
October 5, 2011 at 2:06 pm
I always heard that a good rule was to start with [installed] RAM at 10% the size of the databases in the instance and go up from there as needed.
Currently we have a 350GB database running on dual hex cores with 64 GB RAM. We were running the same box with dual quad cores and 32 GB of ram, but upgraded when we clustered it with another active instance. This one box get's hit very hard on a daily basis. Before upgrade we didn't really have a performance issue with this server, but after the upgrade, the CPU + RAM is always waiting on the disk (meaning it is not a bottleneck, but there is so much horsepower that the disks cannot keep up =) )
D: Data on raid 1+0
L: Log on raid 1+0
T: temp also raid 1+0 (this particular instance cannot lose the tempdb disk without substantial monetary loss on the business).
All disks are 15K rpm.
October 5, 2011 at 2:10 pm
Is your page file on a separate partition?
Thanks,
Jared
Jared
CE - Microsoft
October 5, 2011 at 2:13 pm
Mine or his? =) We have no page file on these SQL boxes.
October 5, 2011 at 2:15 pm
Swap file. Is it on the OS partition, or does it have its own partition?
Jared
Jared
CE - Microsoft
October 5, 2011 at 2:16 pm
The OS is on C:. These boxes have no swap/page file
October 5, 2011 at 2:19 pm
Umm... Why not? 🙂
Jared
Jared
CE - Microsoft
October 5, 2011 at 2:38 pm
jared-709193 (10/5/2011)
Umm... Why not? 🙂
Because it's not really necessary on a dedicated SQL box. I usually create one of a couple GB, no more. Just enough for a kernel dump if a crash should occur.
If SQL is using the page file at all, you already have serious problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2011 at 2:40 pm
GilaMonster (10/5/2011)
jared-709193 (10/5/2011)
Umm... Why not? 🙂Because it's not really necessary on a dedicated SQL box. I usually create one of a couple GB, no more. Just enough for a kernel dump if a crash should occur.
If SQL is using the page file at all, you already have serious problems.
Yes, but like you said. There is a reason to keep it enabled. I have not found a good reason to disable it.
EDIT: Also, I respectfully disagree. I know the subject has been beaten to death, and people do what works for them. However, I have found some good explanations about how the page file is used and how having it disabled or too small can cause issues. I believe that the page file will always be used when enabled no matter what your memory size.
http://stackoverflow.com/questions/2588/appropriate-pagefile-size-for-sql-server
Thanks,
Jared
Jared
CE - Microsoft
October 5, 2011 at 3:11 pm
jared-709193 (10/5/2011)
Umm... Why not? 🙂Jared
The instance running on these boxes never actually get to consume memory anywhere near the amount available to them. They're also set up so that they can only consume (total ram - 4GB).
The only time there could be a problem is when one of the other nodes in the cluster fail over. However, each of these instances have a start up proc that checks how many instances are running in it's physical node and then re-configures the maximum memory size in each of those instances so that they play nice together while in the same physical server. This has been deemed acceptable by the business in case of a real problem that caused a fail over.
The only other thing that may run on those servers are either an RDP session for some tweaking here or there (SP installs, etc), or something like a PS script that gets remoted to it for some very minor stuff.
I still have not found convincing arguments as to why anyone would run a sql server in a way that swap/page file use would be of any benefit, and by that I mean actually used, not just configured to be available. As far as my sql instances are concerned, either they can work with the memory they're given or they require more RAM installed. The only time I want my SQL data in disk is when it is committed there when it's written or just before it is fetched up to be used by a query or another.
October 5, 2011 at 3:14 pm
My understanding is that moving to a page file when no more memory is available is only 1 of the uses for a page file. Nevertheless, if you are not experiencing problems I guess it is not a big deal.
Jared
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply