Forum Moderators: phranque
I'm looking at upgrading our Windows DB platform. We've outgrown our current server (dual P3 Xeon 1.3 GHz, 4 GB RAM) and looking at my budget, are looking at either of the following two options (which are similarly priced):
On our current DB server, the RAM never really goes above 3.3 GB, and I've profiled our queries, so I've decided that CPU is probably the bottleneck.
The million dollar question, of course, is will SQL Server benefit more from the faster dual processors, or from multiple, slower quad processors?
An IBM DB guy I know suggested that for my DB needs, the quad config was probably better. However, I know the IBM DB guys are generally CPU happy, so I wanted to get a second opinion. Anyone have any insight on this?
Having more CPU horsepower, and more RAM, will always increase SQL performance. It depends on how complex your queries are - before you would do anything, I would make sure that your queries are as optimized as they can get AS WELL AS making sure the data in tables is stored in the most efficient manner (using numbers instead of words whenever possible, for example, provides tremendous performance boosts in SELECT queries).
It's a good idea to make sure that the amount of RAM in your system is greater than the size of your database(s) - that will allow your databases to load entirely into memory. It will use less CPU power because the database is in RAM and doesn't require the drive to spin (so the query is executed faster and doesn't take up the CPU while waiting on data to be retrieved from the disk)
Also if you have any stored procedures that reserve memory (for example a dynamic SQL statement which uses a 2000 character varchar or nvarchar) - estimate how many users will be executing that procedure at any time, and factor those bytes in to the total system RAM.
Another biggie for SQL - swap files. If your database is larger than the total amount of RAM your system can HANDLE (ie; your system only supports 16 GB of RAM and your database is 20 GB) then make sure you have a huge SWAP file on a drive other than your system ("C") drive.
I'm running a Win2k/SQL 2k configuration that receives over 100,000 complex INSERT and SELECT/INSERT statements per day - it's running on a dual Pentium 800 Mhz with 8 GB RAM. When I only had 4 GB of RAM in it, the CPU was averaging 40% - 80% utilization. By adding another 4 GB of RAM, it dropped the CPU utilization down to 10% because the databases could load into memory and didn't require so much disk/swap file access. This is a huge database with millions of records that's read from/written to 24/7. In most cases it's really more about how well optimized your queries are, and how data is stored, over the hardware itself.
I'm not sure how frequently your data is accessed, but it's generally a better idea to go with more RAM than more CPU's since memory is usually the bottleneck.
If your RAM is never going above 3.3 GB it doesn't mean that it doesn't WANT to ... it means that you only have 4 GB available and SQL is either configured to only use 85% of the available resources, or other services you have running are taking up the rest. Your CPU is probably high because of swap file usage. Double the RAM in your existing system first and see how much that helps. You might not have to upgrade at all.
I would make sure that your queries are as optimized as they can get
Oh yeah, we're well past this point.
make sure that the amount of RAM in your system is greater than the size of your database(s)
Check. Got that, although a good tip for future readers.
over 100,000 complex INSERT and SELECT/INSERT statements per day
We're at about 200-250K at this point. :-/ We've done as much caching and optimization as humanly possible.
Double the RAM in your existing system first and see how much that helps.
Can't - this baby's maxed out. That's why I'm looking to upgrade. The quad setup can take up to 32 GB, but I would be maxing out with the dual at 16 GB. I'm leaning towards getting the quad now, seeing how things go with 8 GB, then adding more RAM as I need it.
a caching raid subsystem on a striped mirror
set does wonders for db performance.
a caching raid subsystem on a striped mirror set does wonders for db performance.
Thanks plumsauce, good to hear from you again.
The current machine is a RAID 5. On the new machine, we'll go to RAID 1+0, so I'm in total agreement with you here.
The first thing you want to do, as mentioned before, is try to stuff as much memory into the thing so as to be able to cache your entire, database in RAM (if this is feasible). This helps alot with SELECTs.
On a system heavy with UPDATES, and INSERTS *nothing* will improve your preformance more than your disk setup.
1)Make sure your system has a RAID card with *battery-backed* cache, the more cache the better. I can't stress this enough, this will make a huge difference.
2)As for RAID setup, there are many options. Alot of people have a setup where they keep their OS, programs, AND DB transaction logs (WALs) on a Raid 1, then keep their data on either a Raid 10 or a Raid 5. This helps alot since a DB has to do 2 writes/update, one to the WAL and one to the actual DB data, when these two things are on different disk arrays, performance improves. Alot of people prefer a Raid 5 to a Raid 10 because they can easily just keep adding disks (I've head of setups with dozens of disks) to improve performance. More disks = more concurrent writing = more throughput. More disks also = more empty space per disk = faster seek times.
Compared to your disk setup, what CPUs you choose will be largely irrelevant. On a write heavy DB a dual PIII 800 with a good disk setup, with beat the pants off a dual P4 3.2 with a not-so-good disk setup. I've actually seen this with my own eyes.
Of course, there's always the
possiblity of using a SAN :)
Lot's of advantages there too.
+++
The first thing you want to do, as mentioned before, is try to stuff as much memory into the thing so as to be able to cache your entire, database in RAM (if this is feasible). This helps alot with SELECTs.
Bingo - this is our situation. We do a TON of heavy selects, and a moderate-few amount of writes.
That right there answers my question - I think I'll go with the quad xeon, as it will allow us to expand to 32 GB of RAM.
Thanks all!
