More, slower memory or less, faster one?

In a new server I'm configuring I'm debating whether to go with 32GB at 400MHz (PC3200), 48GB at 333MHz (PC2700), or 128GB at 266MHz (PC2700)]

Considering that Windows 2003 64 Bit will handle 128 what would you recommend?

BTW the server is the HP DL 585 with the Quad AMD Opteron.

Thank much for your advice.

Andrea

What are you using this server for? Personally, I would go with the faster RAM and boost up the size later if you need to. How big are the databases, what type of database (OLTP, OLAP, etc), and how busy are you expecting this server to be?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

The server is for a telecommunication company which processes real time call detail records. The problem is that what I posted is the maximum the server can handle; so once gone with 32G that's it. No growing from there unless one were to toss those 32G and replace them.

I would probably still stick with the faster RAM. How big are the databases?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Main DB will be in excess of 500GB.

Currently I have two similar ones (250GB each) on a machine with 4GB of memory with AWE disabled and I'm going to upgrade to 32GB to see how much it will help.

Andrea

What are your current issues with the 4GB machine (HUGE UPGRADE [<img src='/community/emoticons/emotion-1.gif' alt='' />]). Have you ran Profiler and looked at the performance counters to see what the current issues are?<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.

I actually have looked at the profiler and spent quite some time with it. The biggest issues are with the Disk queue length when the updates and insert are performed by the server. The next issue is with computing certain data which needs to be read from the disks. I figured, even if I can't store the entire DB in memory at least I'd be covering all the largest tables.
Last in line is the quad CPU with hyperthreading which seldom reach full 100%.
Please note that I currently run two separate, similar, databases on this machine but on the new one they'll be consolidated.

Andrea

"seldom full 100%" is a huge deal actually. If you're processors are above 75%, you are taking a huge performance hit. After 75%-80%, the performance is exponentially worse for SQL Server server. When you're disk queue lengths are really high during these operations, have you noticed what the buffer cache hit ratio and regular cache hit ratio look like? Also, is it the physical disk queue lengths? Are both read and write high at the same time?

One other question...what's the RAID controller like on the new server compared to the old one?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

I agree with you on the 100% issue, in fact I don't consider that to be an issue at all at this point. My average CPU utilization is around 25 to 25% with regular spikes above that and occasional sustained 100% (10 to 20 seconds).
Going back to the queue length I don't remember on top of my head but back then (about 2 years ago) we concluded that a worthwhile upgrade would have been a switch to faster drives, controller card (now a PERC4/DC on a Dell PE6600) and RAID 10 (from RAID 5). That indeed helped quite a bit but it still left the performance priority in the same order. The new server controller will be an fiber HBA 2Gb of total bandwidth (actually two of them) connected to a fiber switch (two again) which in turn connect to the MSA1000 dual 2Gb controllers.

With specific regards to the profiler, I'd be more than happy to run a few passes and jot down some data for you. If there is anything particular you need me to check besides what you mentioned above, please let me know and I'll post it tomorrow.

Thanks

Andrea

That should be the counters we need. Post per disk and tell us what's on each disk (data, log, etc). There will also be more people on to help tomorrow. Joe Chang might even make an appearance.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

without having read every detail discussed above,
the important decision is whether to buy the processor board with 4 DIMM sockets (for PC-3200) or 8 DIMM sockets (PC-2700, either 333 or 266)

note the memory speed has 2 aspects, bandwidth & latency. I seriously doubt SQL Server can use the full memory bandwidth of the DL585 even at PC-2100, try an in-memory table scan.
Memory latency has more impact on performance, but you need to stick with approved memory for this system, which may mean you don't get a choice

If you think your performance is more limited by disk queue, get a proper disk system first. if you are going SAN, i would be sure to split load across multiple HBA (4 or more), controllers, and disks. I would actually recommend HP's new SAS drives over the SAN.

Back to memory, buy the model with 8 DIMMs per board, skip the PC-3200, you won't need it.
This means your system has 32 DIMM scokets, of which you can only populate 24 to operate at 333MHz.
I think it may make more sense to buy 16 1GB DIMMs (PC-2700) or 8 2GB DIMMs, check the buffer cache hit ratio change from 4GB, then make the decision on whether to fill the remaining 16 DIMMs with 1GB or 2GB DIMMs

but if you CPU is not high, fix the disks firsts

On this model Joe, isn't the entire backplane of the server better on the models that support the PC-3200? I would want to check that before I make the final decision.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Guys,

It goes without saying that your help is invaluable and very much appreciated.

I'm now running the profiler again to give you guys some better data to work with and I have the following counters:

1) Avg Disk Queue Length for Physical disk of just the storage where I have my main DB.
2) Copy Read Hits % from the Cache counters
3) Avg Disk Read Queue Length for Physical disk of just the storage where I have my main DB.
4) Avg Disk Write Length for Physical disk of just the storage where I have my main DB.

Please let me know because I don't see what Derrick mentioned on the Cache counter, should I look elsewhere?

Thanks

Andrea

this is my recollection on the matter of memory bus speed in general, not that specific to this platform, so someone from HP needs to speak on platform specifics.

this is not a matter of quality of the backplane.
it is a matter of the capacitative load of a unpopulated and populated DIMM socket.
each socket adds capacitance, and actually populating a DIMM add more capacitance.

The AMD Opteron processor has 2 72-bit memory channels.
if each channel is wired & populated with 2 DIMMs for a total of 4 DIMM, then its possible to run the channel at 400MHz.
if wired & populated with 4 DIMMs, then 266MHz is the max possible operating speed.
if wired for 4, but populated with 3, then apparently it can run at 333MHz.
since there is not much price difference between PC-2100 and 2700, presumable HP decided to just spec 2700, and elect to run at 266 if all four DIMMs in each channel is populated

All you're saying makes a lot of sense. However, here is what I found out looking carefully at the HP online configurator:

SCSI models
DL585 (PC2100) goes up to 64GB with 32 slots 2GB each
DL585 (PC2700) goes up to 128GB with 32 slots 4GB each (not yet available)
DL585 (PC3200) goes up to 32GB with 16 slots 2GB each (not yet available)

SAS Models
DL585 (PC2100) goes up to 64GB with 32 slots 2GB each
DL585 (PC2700) goes up to 64GB with 32 slots 2GB each
DL585 (PC3200) goes up to 32GB with 16 slots 2GB each

I guess the most flexible option is the one with PC2700 which gives both capacity and performance.

With regards to SAS, do you refer to the internal drives only or as external storage solution as well?
I know that supposedly the available bandwidth is 3Gb as opposed to 2Gb of the MSA1000 but is that enough to say that the SAS perform better than the SAN?

Andrea

I know that's the theoretical bandwidth of the SAS models. I'm not sure I would be comfortable recommending that to you though as a core SQL Server solution. Joe, you have any thoughts on this one? This is one area I'm not comfortable making recommendations on.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

i am anxious to get an SAS storage system, but have not tested on yet. i don't believe there should be any issues from HP because 1) its just an extension of SCSI, 2) this is the Proliant group which has not screwed a storage product, unlike another vendor to remain nameless.

i do not like FC/SAN because i find the cost/peformance per 2Gbit/s port too expensive period, and too poor performance for sequential transfers.

the SAS is 3Gbit/s per port, the SA600 SAS adapter has 6 ports, so this is way more sequential bandwidth than the stupid dual port FC, which costs more and has no RAID logic.

I would be willing to try the SAS,
fill the internal SAS bays, and buy 2 external MSA 50 and fill those

HP currently does not have an enclosure for 3.5in SAS/SATA drives, when they do, arrange to get a small one, so you can use big SATA drives for the backup.

you could also get the SCSI model, use big 10K drives for log and backup, and use external 2 MSA 50 for the data

You have a very good point there. I guess I'm going back to the drawing board...
Thankfully Yukon is not coming out until Nov 7...

I'll post the configuration I get to when I'm done.
Thanks again for all your advice.

Andrea

Andrea,<br /><br />I recently configured and tested a DL385 (Opt275) with 16GB PC3200 with 2 MSA30 arrays. I posted my SQLIO load test results to this thread (which no-one's commented on yet [<img src='/community/emoticons/emotion-6.gif' alt='' />]). Coming from Joe's unnnamed vendor storage solutions (I believe), it's a night and day difference.<br /><br /<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10495>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10495</a><br /><br />For your specific question on 3200 vs 2700 memory, you're looking at a 15% loss of clock rate for double the available memory. Also, have you compared the old server's memory bandwidth vs going to 3200 and 2700 on Opteron? I'd think you'd be gaining multiples of performance regardless of which stepping you go to.<br /><br />Going from here,<br /<a target="_blank" href=http://www.amd.com/us-en/Processors/ProductInformation/0>http://www.amd.com/us-en/Processors/ProductInformation/0</a>,,30_118_8796_8805,00.html<br /><br />At PC3200, you get 6.4GB/s per processor theoretical with a 4P max of 25.6GB/s. Assuming a linear ratio, you'd get 5.4GB/s per processor at PC2700 with a 4P max of 21.6GB/s (almost a straight 15% reduction). Eyeballing the price difference on Crucial shows them to be the same price, as well.<br /><br />I'd recommend looking at the Opteron dual core chips, there are a number of architectural refinements made to them over the single core chips. I also see that HP is now offering the Opteron 880, lucky!<br /><br />I, too, am very interested in the new SAS equipment HP is rolling out, but I'd rather buy into the second generation of the product. I'm particularly interested in small form factor SAS 15k drive arrays, as that will significantly alter the storage density landscape. They need to release this stuff sooner! [<img src='/community/emoticons/emotion-1.gif' alt='' />] <br /><br />hth

Hi there,

Very interesting comments, thanks. I did look at your thread but I have nothing to compare with. The unnamed vendor is the one that is giving me a lot of grief...
So no matter what going back to HP is going to be a relief.
I did look at the 880 and already decided to go for those. I guess we all concur that more memory is better than faster memory especially when the trade off is 3200 vs 2700 and 32GB to potentially 128GB
On the storage the details are still sketchy as more information is needed info about the SAS.

How do you like the Opteron with 2003 64 Bit? R U running SQL 2000 SP4 on it? Any plans to go to Yukon?

Andrea

quote:Originally posted by agw
How do you like the Opteron with 2003 64 Bit? R U running SQL 2000 SP4 on it? Any plans to go to Yukon?

Not sure if this will affect you, but my DL385 came with the SmartStart 7.30 software. I tried to install with this, and it didn't recognize the hardware fully. After checking with HP Support, I found an update (7.40) available as an ISO from the HP Support website. After switching, everything was detected, and installed smoothly.

The machine is running Windows 2003 EE x64, SQL Server 2000 SP4, and not much else. No driver issues at all. I've installed the latest beta of Yukon on it, and there weren't any issues I saw from my playing around with it. I plan on moving to SQL Server 2005 as soon as I can (synonyms, yay!), but I'm going to wait awhile after release to see if anything major is discovered. Every test I've run points towards the Opteron platform as being hands down faster than any Xeon platform I've tested with.

The unnamed vendor is probably EMC. lol I'm like George on this one. I want badly to try the SAS technology. I like "kewl" and helpful technology; however, I would be extremely reluctant to put my production environment on anything that's first generation. <br /><br />Also, if you can look at Opteron, you should go for it. The tests have been extremely impressive and the technology is now mature enough with SQL Server environments that the integration has smoothed and support ability has increased dramatically. We're looking at dual core now to possibly replace our production environment. One important thing to consider is the fact that MS licenses SQL Server by the socket. That means dual core still pays 4 processor when you can effectively have 8 (sort of [<img src='/community/emoticons/emotion-1.gif' alt='' />]). This is much different than a hyperthreaded processor.<br /><br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.

Absolutely guys, all consideration I had made and firmly convinced that HP/AMD is going to be a fast and solid platform. Cost wise can't be beaten either...
I guess once again we'll have to decide what to do on the storage but unless something solid comes out I agree that FC/SCSI may not be the latest and greatest but will (hopefully) let me sleep at night...