Max Memory - Enterprise, W2K AS, W2003 EE
-
I'm confused. I'm buying a new datawarehouse server and wanted to max memory but did not want to go 64bit or Datacenter server due to cost.
I had the impression from numerous documents the max was with SQL Server Enterprise and Windows Advanced Server, and the max was 8GB. It's right there in BOL.
However, I've read numerous places that with Windows 2003 Enterprise (which I think is the equivilent of Advance Server) that SQL Server can use 32G of memory. Though I'm having trouble finding it written really clearly in anything from Microsoft.
Can I use 32G of memory with SQL Server 2000 Enterprise and W2003 Enterprise?
and am I right that 8G was the max in W2K Advanced Server?
-
It appears that W2K3, Enterprise has a 32GB RAM limit. Also an 8CPU limit.
W2K, Advanced Server allowed up to an 8 way x 8GB RAM.
References:
http://www.microsoft.com/windows2000/advancedserver/howtobuy/choosing/default.asp
http://www.microsoft.com/windowsserver2003/evaluation/features/compareeditions.mspx
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Right. But have you seen anything that said SQL Server could USE the 32G (or a substantial part of it) on W2003EE?
-
AFAIK, There is no limit on SQL Server 2K Enterprise for Memory, limited by the OS. This ref (http://www.microsoft.com/sql/techinfo/administration/2000/scalabilityfaq.asp) shows that it can take up to 64GB of RAM in 32bit mode. 64 bit can go to 512GB.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Again I'll be iconoclastic with my (gratuitous) advice. If you are ready to spend enough money to buy 32GB on a server, and this is not a mission-critical (i.e. production) system, then go with the 64-bit hardware and software. The software costs no more, and the Itanium processors will only add a small percentage to the cost of a server with that much memory.
If you wish to be conservative, then just buy Windows 2003 Server (which supports 3GB user-mode memory addressing), SQL Server 2000 Enterprise, a nice server with 4GB of RAM, and put the money into a lot of 15Krpm drives on caching controllers (not a SAN). For a 150GB data warehouse, that will probably be just as fast as using 28GB of AWE memory with a 2GB User-mode address space. If you will only have a few concurrent users (typical of a data warehouse) and have enough time to load data and build indexes with lower parallelism, then fewer but faster (i.e. Xeon DP vs. Xeon MP) processors may also work better. 32-bit Windows will never work particularly well with more than 4GB of memory: it's always a kludge.
Memory has sunk in cost faster than Intel could mainstream the (64-bit) technology to take advantage of it, so we're currently in a limbo where there is no good answer. Perhaps buy a 4GB server for now and save the excess funds for when the 64-bit technology is more thoroughly debugged (Windows Server 2003 SP1 with SQL Server 2000 SP4?) and hardware prices have inevitably dropped, when you can buy that and redeploy the 4GB server as an OLTP box.
--Jonathan
--Jonathan -
Thanks Jonathan! Again two new ones
I like this flowery language!Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Again I'll be iconoclastic with my (gratuitous) advice.
I take advice of any flavor.
quote:
If you wish to be conservative, then just buy Windows 2003 Server (which supports 3GB user-mode memory addressing), SQL Server 2000 Enterprise, a nice server with 4GB of RAM, and put the money into a lot of 15Krpm drives on caching controllers (not a SAN).
Ok, if I spend the $ for SQL EE why not go with more memory? Is it because you think it won't work, or is a waste of money? The SQL EE is a large cost itself.
We are looking at a HP/Compaq DL7xx (not sure which) 8-way box, with only 4 processors initially, two 6402 controllers and 28 15k 36G drives in a pair of shelves (so divided amongst 4 channels). Memory for it is not (in comparison) all that expensive. But I do want to make sure it is going to ACTUALLY use it if I buy the memory.
quote:
If you are ready to spend enough money to buy 32GB on a server, and this is not a mission-critical (i.e. production) system, then go with the 64-bit hardware and software.
Oh... it's very mission critical. I probably shouldn't really call it a data warehouse server, as it also carries several applications like order regulation for our customers (which is heavily data driven).
Is the Itanium/64bit software really ready for prime time?
But there's a better reason -- this sits beside a "backup" server which is 32 bit. That's a bit misleading as it is also a build machine -- we do most of the loads and cleanup on that machine, and then run most of the ad-hoc and production queries on a copy of the database on this new machine. The ad-hoc stuff is killing the current version (4x700Mhz, 2G) so we are looking for a substantial boost. However, it is also a true backup -- if either fail, we have mechanisms to run load + production on one box (much slower obviously, and some stuff people would simply not do). So I do not think I can have a 64 bit and 32 bit and expect it to be equally interchangeable.
-
quote:
Ok, if I spend the $ for SQL EE why not go with more memory? Is it because you think it won't work, or is a waste of money? The SQL EE is a large cost itself.
It will work, but poorly; particularly for a large typical DSS database. That much memory will give you much better bang for the buck on an Itanium server.
quote:
We are looking at a HP/Compaq DL7xx (not sure which) 8-way box, with only 4 processors initially, two 6402 controllers and 28 15k 36G drives in a pair of shelves (so divided amongst 4 channels).
Use two 6404 controllers instead. Actually, the IBM x445 is a better server entirely...
quote:
Memory for it is not (in comparison) all that expensive. But I do want to make sure it is going to ACTUALLY use it if I buy the memory.
That's about US$20,000 for the extra 28GB, right? You could use 18GB drives and double the number of drives and controllers for that.
quote:
Oh... it's very mission critical. I probably shouldn't really call it a data warehouse server, as it also carries several applications like order regulation for our customers (which is heavily data driven).
When you've got this many servers, don't mix DSS and OLTP on the same box.
quote:
Is the Itanium/64bit software really ready for prime time?
I think I already implied my opinion on this...
quote:
But there's a better reason -- this sits beside a "backup" server which is 32 bit. That's a bit misleading as it is also a build machine -- we do most of the loads and cleanup on that machine, and then run most of the ad-hoc and production queries on a copy of the database on this new machine. The ad-hoc stuff is killing the current version (4x700Mhz, 2G) so we are looking for a substantial boost. However, it is also a true backup -- if either fail, we have mechanisms to run load + production on one box (much slower obviously, and some stuff people would simply not do). So I do not think I can have a 64 bit and 32 bit and expect it to be equally interchangeable.
Yes, data is data, so it will be interchangeable. I don't know enough to give you a definitive opinion (and I am a paid consultant
), but it sounds from this as though you'd be better off with two (cheaper) new servers, one for OLTP and one for DSS, that could back each other up (e.g., hang a RAID 10 of four 72GB drives off the OLTP server so you have have fallback capacity for your warehouse).--Jonathan
--Jonathan -
FWIW, YMMV, etc...
We could not get 32-bitAnalysis Services or SQL Server to use all the memory we were throwing at it. We recently moved to 64-bit for our warehouse environment. It was well worth the $ for us. We actually went down(!) on the number of processors and are getting many times the throughput. Many of our (bad/large/stupid but necessary) SQL queries that used to get stuck in the optimization phase now work acceptably.
Reminder: 32-bit DTS is not supported on a 64-bit box. You will need a 32-bit machine from which to run your tools.
Is 64-bit ready for prime-time? We have had one production issue so far - a driver problem with our SAN that did blue-screen the box. *Other than that*, no problems, and great performance. In short, it worked for us.
Good luck,
Larry
Disclamer:
The above represents the absolute truth, and therefore cannot possibly be the responsibility of my employer.
quote:
Ok, if I spend the $ for SQL EE why not go with more memory? Is it because you think it won't work, or is a waste of money?(SNIP)
But I do want to make sure it is going to ACTUALLY use it if I buy the memory.
(SNIP)
Is the Itanium/64bit software really ready for prime time?
(SNIP)
So I do not think I can have a 64 bit and 32 bit and expect it to be equally interchangeable.
Larry
-
Re pricing, etc. -- I'm waiting on some new configuration pricing, will see how this looks then. But as to this:
quote:
Use two 6404 controllers instead. Actually, the IBM x445 is a better server entirely...
we've got way too much Compaq stuff and zero IBM in house, and way too little staff, to change right now.
However... I'm curious about the 6404 comment. The two 6402's have 2 channels each. We plan to use 2 split-bus shelves, so 4 channels will be in use. 6404's will have a total of 8 channels and only 4 can be hooked up.
What am I missing?
One aspect is that the 6404 can come with 256M cache, the 6402 with 128M. I've been told (but not reliably) I can upgrade the 6402 to 256M as well.
But even if not, I've also been told (pretty reliably) that the 6404 is basically a 6402 with a daughter card that is another 6402 stuck onto it, so each pair of channels has its own processor. What I haven't been told is whether they can share that memory, or whether then the 256M memory is limited to effectively 128M each.
However... Maybe that is what you meant about the 6404 -- would you then take only one channel from each to each split-bus shelf, and expect the dual processors to run faster? I had not considered that. Can I split raid sets across the two processors of one 6404? If not, that might not really improve things, trading raid processor power for channel contention.
-
quote:
we've got way too much Compaq stuff and zero IBM in house, and way too little staff, to change right now.
I know what you mean, and I concur.
quote:
However... I'm curious about the 6404 comment. The two 6402's have 2 channelsWhat am I missing?
One aspect is that the 6404 can come with 256M cache, the 6402 with 128M. I've been told (but not reliably) I can upgrade the 6402 to 256M as well.
That's right; my mistake.
I should have written that you should use spec 256M on the controllers.quote:
We plan to use 2 split-bus shelves, so 4 channels will be in use. 6404's will have a total of 8 channels and only 4 can be hooked up.But even if not, I've also been told (pretty reliably) that the 6404 is basically a 6402 with a daughter card that is another 6402 stuck onto it, so each pair of channels has its own processor. What I haven't been told is whether they can share that memory, or whether then the 256M memory is limited to effectively 128M each.
However... Maybe that is what you meant about the 6404 -- would you then take only one channel from each to each split-bus shelf, and expect the dual processors to run faster? I had not considered that. Can I split raid sets across the two processors of one 6404? If not, that might not really improve things, trading raid processor power for channel contention.
The daughterboard is not "another 6402" but instead just another I/O (SCSI) processor and connectors. So the 6404 has no more array processing power than does a 6402.
--Jonathan
Edited by - Jonathan on 12/08/2003 07:57:15 AM
--Jonathan -
Reminder: 32-bit DTS is not supported on a 64-bit box. You will need a 32-bit machine from which to run your tools.
CynicalDBA, I read a review that said dts jobs ran considerably slower with 64bit. Have you seen this? Do you have a work around that is sufficient?
We are considering the 64bit version, but are very reliant on dts being very fast.
Thanks.
-
Guys,
I really appreciate ALL your posting on this thread. I have learn a lot here. Hardware has allways been my WEAKEST point (never had the opportunity to work for a company with Big $$)
(Jonathan) -- Thank you very much for your (gratuitous advice) I really appreciate when a consultant does that.( it doesn't happen very often
)Can someone point me where to start on sql server hardware ?
TIA
Noel
* Noel -
Sql Server 2000 Performance Tuning Technical Reference is a good start on how to size your hardware to support the speed you are trying to attain. HP did several test with 64bit and Sql Server and found that ERP solutions and smaller datamarts would almost fit entirely in memory only going to the drives to commit writes. Remember even the fastest sans only do about 35 to 40 thousand I/0's a sec at around 350 to 400Mb a sec throughput with 128 or more drives. While system memory will easily do 100,000 I/O's and 3 to 6Gb of transfers a second at that point its almost always wait time for
processors on the server to feed the system memory. We are currently testing w2k3 with 32 gig of ram in it to see what kinds of performance gains are to be had. We run our ETL on a DL740 with 8gb ram and 8 procs now and completly soak the memory on that box.
Wes
-
quote:
We are currently testing w2k3 with 32 gig of ram
Are you testing 32bit or 64bit?
And would love to hear what you are finding (especially if at 32bit the change to 32G improved).
I like this flowery language!
), but it sounds from this as though you'd be better off with two (cheaper) new servers, one for OLTP and one for DSS, that could back each other up (e.g., hang a RAID 10 of four 72GB drives off the OLTP server so you have have fallback capacity for your warehouse).
I should have written that you should use spec 256M on the controllers.Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply