| Author |
Topic |
|
sumo
Starting Member
45 Posts |
 Posted - 2003-02-28 : 09:23:32
|
| Here's the scenario.. we're restricted to a particular server that will have two of the fastest processors and 4 GB of RAM. However, it can only hold 3 hard drives. What is the fastest configuration for SQL Server for those 3 drives? Is it 1 drive for OS and applications and 2 for RAID 0 for the databases and transaction logs?All this server is going to do is transform large catalogs from one format to another. We have the option of upgrading an existing server to 4 Xeon 700MHz (2MB cache) processors and 4 GB of RAM, but it will cost more than the new server above and cause us to take it down to re-configure the 8 hard drives it has for the optimal SQL configuration.Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-28 : 14:22:16
|
| Yes that probably is the fastest way to go, but aren't you concerned about redundancy as well as performance? |
 |
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-28 : 14:24:50
|
It's an offline server just to do the processing. There will be a backup of it somewhere and it wouldn't be necessary to have it back up immediately...My real question that is somewhat hidden from my original post is will it work sufficiently enough compared to the 2nd server, which would give us much better hard drive performance because of the additional drives...quote:
Yes that probably is the fastest way to go, but aren't you concerned about redundancy as well as performance?
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-28 : 15:23:53
|
| You will get more performance from the upgraded server if it has more controllers. Better performance will be observed as the physical disk-to-controller ratio approaches one.You need to keep in mind that with this new server that will only have 2 drives allocated to the database and transaction log is that you've got two points of failure. If either drive fails, you lose everything. Of course you mentioned that you've got backups, but now you have to spend the time restoring the data.Tara |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-28 : 15:32:18
|
Well, my personal experience tells me that upgrading the existing server would serve us better in the long run. The existing server is a Dell PowerEdge 6400 w/two 700MHz PIII Xeon 2MB cache processors, 1GB of RAM, and eight 9.1 GB/10K drives.The new server would end up having two 2GHz P4 processors, 4GB of RAM, and three 36GB/15K drives.The existing one would be upgraded to four processors and 4GB of RAM. Would the existing outperform new for SQL server that is looping through large cursors (100,000-500,000 rows) and doing that many single line inserts?We can buy the new one cheaper than it'd cost to upgrade the existing one. And, we'd have to re-configure the RAID arrays on the existing one for optimal performance. The existing one is currently used for offline processing of other things. We do have a backup server that can be configured to take over for it while it is being reconfigured.We also have a Compaq DL380 sitting around with 1GB of RAM and 1 processor. It's capable of taking 5 drives in its backplane and we're considering upgrading that one as well instead of purchasing a new one.quote:
You will get more performance from the upgraded server if it has more controllers. Better performance will be observed as the physical disk-to-controller ratio approaches one.You need to keep in mind that with this new server that will only have 2 drives allocated to the database and transaction log is that you've got two points of failure. If either drive fails, you lose everything. Of course you mentioned that you've got backups, but now you have to spend the time restoring the data.Tara
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-28 : 15:39:55
|
| It is hard to say which one would perform better without actually running tests on them. You should contact your server vendor and see if they can loan you one for this test. A server vendor will often loan you a server in hopes that you will buy it.Tara |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-28 : 15:43:14
|
That's an interesting option...I mean, from tests on the existing server the process is running on, it doesn't seem to be thrashing the hard drives at all. The CPU gets pegged, though, and the queue length is much longer than it should be. I'm guessing that throwing more CPU and memory at it will help. Maybe memory moreso than CPU. I was just looking for opinions...  Getting a loaner might not be a bad idea...quote:
It is hard to say which one would perform better without actually running tests on them. You should contact your server vendor and see if they can loan you one for this test. A server vendor will often loan you a server in hopes that you will buy it.Tara
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
bm1000
Starting Member
37 Posts |
Posted - 2003-02-28 : 15:51:42
|
| Replacing 8 disk drives with 3 disk drives seems like a bad idea. You are now going to be able to do 300 I/Os per second vs 800 I/Os per second. If your batch update processing is single-threaded, you may not have an issue. However, if you have parallel processes you could have serious performance problems. If the application already exists, turn on perfmon and see how many I/Os per second the application is currently performing. |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-28 : 15:54:30
|
Well, it'd be configured more like this:2 drives mirrored for the OS and applications3 RAID 0 for database3 RAID 0 for transaction logsI wouldn't have all 8 drives set to RAID 0...quote:
Replacing 8 disk drives with 3 disk drives seems like a bad idea. You are now going to be able to do 300 I/Os per second vs 800 I/Os per second. If your batch update processing is single-threaded, you may not have an issue. However, if you have parallel processes you could have serious performance problems. If the application already exists, turn on perfmon and see how many I/Os per second the application is currently performing.
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-28 : 16:08:38
|
Which specific monitors should I be looking at?I'm currently looking at:% Processor Time% Disk Read Time% Disk Time% Disk Write Time% Idle TimeCurrent Disk Queue LengthDisk Bytes/secDisk Read Bytes/secDisk Transfers/secDisk Write Bytes/secDisk Writes/secThings I'm noticing:* When CPU 1 spikes, CPU 2 flatlines.* When CPU 1 flatlines, CPU 2 spikes.* % Disk Read Time's highest is ~61%, with average of 3%.* % Disk Write Time is 0%* Disk queue seems low (< 1)* Disk Write Bytes/sec spiked to 62,000, averages 10,000* The rest seem low for now...quote:
Replacing 8 disk drives with 3 disk drives seems like a bad idea. You are now going to be able to do 300 I/Os per second vs 800 I/Os per second. If your batch update processing is single-threaded, you may not have an issue. However, if you have parallel processes you could have serious performance problems. If the application already exists, turn on perfmon and see how many I/Os per second the application is currently performing.
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-28 : 16:31:12
|
| That seems very unusual that when CPU1 spikes, CPU2 flatlines and vice versa. Both processors should get hit almost equally if you have setup SQL Server to use both processors.Here are some additional processor counters to monitor:· System: Processor Queue Length – If this number is greater than two per CPU, then more processor power needs to be given to the server.· Processor: % Privileged Time – If this counter is greater than 20, then there is a possibility that I/O may be bottlenecked on the server.· Process: % Processor Time: sqlservr – Compare this to % Processor Time. If other processes are consuming too much time, terminate the nonessential processes or move them to other servers.TaraEdited by - tduggan on 02/28/2003 16:32:51 |
|
|
|
sumo
Starting Member
45 Posts |
Posted - 2003-02-28 : 16:32:33
|
It's definitely set up to use both. Win2K sees both and SQL Server sees both (and is configured to use all available CPU's).quote:
That seems very unusual that when CPU1 spikes, CPU2 flatlines and vice versa. Both processors should get hit almost equally if you have setup SQL Server to use both processors.Tara
Michael SumeranoTechnical Web AdministratoreKeystone.comhttp://www.ekeystone.com/ |
|
|
|
|
Best hard drive config for SQL server w/3 drives