February 2, 2010 at 9:46 am
As time becomes available to IT here, we are going to be upgrading a SQL2000 instance to more than likely a 2008 instance. I am attempting to get a rough idea of minimal hardware needs for our data warehouse. While I know there is a lot of depends on this, hopefully I can give enough information to get either a general "need at least xx RAM" sort of thing, or tips on which hardware pieces/systems are "most important".
Our warehouse holds a core of roughly 40 GB of source data. With the various precalculated reports, and processes we hover around 130 GB of total data. During business hours the database is used at close to 80% reads, while it reverses over night when transforming and loading data.
We have roughly 100 users, and rarely have more than 5 users hitting the server concurrently. A general average for queries would be a few thousand records returned or manipulated, while on the highest end, a summary of ten million or so records, but such is not even a daily activity.
Hardware is not my best skill set, yet the network/server guys claim less understanding of DB servers than I, and always seek only the bare minimum to allow software to run at all. (As in the 130 GB DB is allocated 150 GB of space, and 2 GB of RAM, meaning my laptop might be able to compete performance wise...)
I am thinking, and please correct me if it is not ideal, in general one should have at least 25% free space on a disk for a DB, and then if I count in average growth for the past few years, in two years we should be roughly at 160GB of data. So min I would think I should be looking at 200GB, perhaps more in being proactive, maybe 300 GB.
RAM is giving me some trouble, I don't often see the RAM utilization being a problem, yet 2 GB (for SQL and OS) just seems far too little, 4 GB to 6 GB seems more reasonable, split between OS and SQL.
Processors, presentlty we have a dual 3 Ghz set up. The majority of the time the CPUs are at 50% or more, daily spiking to the 100% range. I would think utilization at 50% would be a fairly optimal set up, but I am concerned about those periods of 100% usage.
In the end, I know the hardware people will make this a virtual server on a SAN. In this DBs case switching between physical server and virtual server actually helped. (The physical server had terrible I/O)
In the end I am initially thinking of..
4 Gb RAM
2-4 CPUs
200 GB for DB itself
Any suggestions, thoughts etc?
Thanks!
February 2, 2010 at 11:12 am
Hi David,
Not easy to predict the resources you will need from an armchair. If memory is a problem, go to 64 bits Enterprise Edition if affordable, you won't have to bother with memory limitations. If you have a 8Gb box, you can dedicate 4-6Gb to SQL Server by setting its max memory parameter and locking its pages in memory, depending on the other applications on the box.
For the disks, you can set a 4x146Gb SAS 15KRPM spindles as RAID10, it makes 300Gb for your data, and a 2x73Gb SAS spindles as RAID1 for your transaction logs, and finally another 2x146Gb spindles as RAID1 for your backup files (you can compress your backups in SQL2008 Enterprise Edition).
You have a dual core with a very high clock rate, and it is 50% with peaks at 100% from time to time. I'd say go for 2x4cores as a minimum, you'll pay the same price in the processor licensing mode, because licensing is still per-socket.
It may look too much, but at the end, a higher end system is more scalable. I'm configuring hardware right now for some RDBMS purposes and I can have this configuration (with 16Gb RAM) for 4300€, that makes about $6000.
HTH
David BAFFALEUF.
David B.
February 2, 2010 at 4:54 pm
Personally I would lean towards 32Gb - understanding that I don't know your environment, usage patterns, etc. That would also depend on if 64 bit Enterprise Edition is being used or not. By spending a little more money, there is room for growth and more efficient use of resources.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2010 at 5:46 am
I agree with Jason regarding the RAM. Another thing to keep in mind is the Disk IO. Please keep in mind that the TEMP DB is used very heavily when you are using 2005/2008. For better performance, it would be best to have different controllers for each Data and Log file.
Keeping the individual Disk size as small as possible is advised by many experts since it gives you more spindles.
You could do some simulation tests on the server to make sure you get the best performance with your hardware using SQLIOSIM/SQLIO
-Roy
February 3, 2010 at 8:20 am
Thank you all for the tips and suggestions. It is good to see that there are others who, like me, think the hardware is cheap enough at the moment that being a bit oversized is not something to be hung up on. The shop here are not on that line of thinking. They seem to aim for just barely enough hardware to run, and it is just enough if the system has issues during business hours once a month. :blink:
February 3, 2010 at 10:41 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2010 at 9:05 pm
David Lester (2/3/2010)
Thank you all for the tips and suggestions. It is good to see that there are others who, like me, think the hardware is cheap enough at the moment that being a bit oversized is not something to be hung up on. The shop here are not on that line of thinking. They seem to aim for just barely enough hardware to run, and it is just enough if the system has issues during business hours once a month. :blink:
Just remember that it doesn't take much bad code to whack even the best of servers. Spend some money on good people, too. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
February 4, 2010 at 8:17 am
Well Jeff... that is why I am on this board all the time, learning better code and processes. I doubt the existing machine we have could handle the things it does without all the things I have learned here.
As many have, I fell into DB stuff, it needed doing and no one was doing it. Since I have joined here I have cut the issues and process times on my server by 25%, yet it's use has increased at least three fold. The code is always where I look first for gain now, still it never hurts to have some breathing room above minimal requirements on the hardware.
The one thing on performance which I wish could be done, would be a guideline of what to 'expect' from a server. ie 100,000 records per second transfers, etc. Of course, all that depends, and so as far as I am aware of, can't actually be done. Heh, would be nice to know if I am driving a Model-T vs a Camero, both work, one you can expect more from.
February 4, 2010 at 11:18 am
David Lester (2/4/2010)
The one thing on performance which I wish could be done, would be a guideline of what to 'expect' from a server. ie 100,000 records per second transfers, etc. Of course, all that depends, and so as far as I am aware of, can't actually be done. Heh, would be nice to know if I am driving a Model-T vs a Camero, both work, one you can expect more from.
You could get a rough idea of how your server handles load and how much Transactions/sec can be done by using some of the simulation tools that are out there.
-Roy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply