Dual Proc Server? 1

[donutman]

Is it true that a dual proc is only going to really help if your server is doing "computations". I've heard that it's better to get massive ram before dual procs if you are only performing joins (regardless of their complexity).
Also note that I only have 10 users hitting the box. So the question is where should I spend $.
-Karl

 

[MDXer]

Multiple procs will be of benefit regardless. Depending on how the DB file groups and files are structured can impact how much of a benefit. More memory is always a good thing but if your using 30% of your memory currently why buy more to only not have it being used. If this is a new server for an existing DB your best bet is run some performance monitor counters and capture 1 or 2 days worth of activity. Lok at you min, max and average counters for 1 or 2 business days. These numbers will tell you were to best invest your money.

Couple questions.

1) Is your db write intensive where you have numerous inserts or updates occuring or is it mainly read?

2) What Hardware manufacture you looking at?

3) Storage type?

The answers to these may have performance impplications as well.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[donutman]

1) read intensive
2) any
3) I'm not sure what you mean: IDE, SCSI? I'm replacing the server, so anything goes.
I have ZERO experience with the performance tools. Don't even know where they are! If it's easy can you direct me or if it requires a lot of "this then that" then what resource should I read.
So is a simplistic understanding of the dual proc prossess: the 1st query is handled by Proc1 and the 2nd query to hit the box is handled by Proc2 in spite of the fact that they both use the same SP?
-Karl

 

[MDXer]

THe reason I asked about storage and manufacturer is that you need to pay special attention to your controller cards with some manufacturers as they don't come stock with Battery Backup Cache. In particular is HP & Compaq. This may not seem like a big issue until you try doing inserts that should take a couple seconds and they take over a minute. If you are going SCSI (which I hope you do) make sure that the read and write cache are user configurable. HP & Compaq requires a $200-$300 upgrade component to allow this if not it defaults to 100% read cache and 0% write cache. Also your raid type will affect performance.

So is a simplistic understanding of the dual proc prossess: the 1st query is handled by Proc1 and the 2nd query to hit the box is handled by Proc2 in spite of the fact that they both use the same SP?

Not necessarily, I have a 4 proc server and there are times I know I have 10 -15 procs or queries running yet you may only see 1 proc in use.

http://www.sql-server-performance.com/

is a great site to understanding how to monior your DB performance. Explains the counters to use and exactly what they mean and how to calculate what they should be in your situation.

if the DB is fairly small and you see that you may benefit from a dual proc machine look at getting a faster proc with HT technology. If you currently have a single 2.6GHZ proc and you feel you need more cpu power look at a 2.8 or 3.0GHZ HT proc. The proc may cost more but it beats the cost of a proc license for SQL server and will give you close to the same performance as 2 procs of the same speed. the rest you could dump into memory. I reccomend buying as much memory as you can but make sure the version of SQL you run can support the amount of memory you have. No use having 6 GIGs of memory running SQL Standard that can only use 2.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp

has memory info on the different versions of MS SQL.

Good Luck.




"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[donutman]

Thanks...I'll do some work and let you know if I need further help.
-Karl

 

[donutman]

BTW, a serial ATA drive is cheaper than going the SCSI route and they are pretty fast. Of course you can spend tons on faster SCSIs. I've also had terrible (I mean dreadful) experience with RAID drives (adaptec controllers) and I've gone through several generations of them. I refuse to use them.
So what do you advise on the ATA vs SCSI issue? My thought so far is to go with dual ATA's (separate channels) with two HDs. One for the Transaction Log and one for the DB. With you comments on procs, I'm thinking 3.0GH HT single proc with 2G RRam (Std Edition).
-Karl

 

[MDXer]

Depends on the hard ware. If you buy a true server, as compared to a beefy workstation that is used as a server then it more than likely will come or have the option for a SCSI controller that is a few steps above a typical off the shelf SCSI Controller. HP and Compaq come with a Smart Array controller (not much good to say as I only have 1 experience with it and it isn't a good one). Dell offers the PERC controller which I have had little to no problems with having worked with them for a few years now.

ATA or SCSI
how critical is the data?
How crucial is uptime?

With ATA you won't get Hardware Level RAID support so your drives will have no parity. The drive goes bad your database goes with it.

Remember also that the number of drives affects your disk IO usage and the can result in Bottle Necks.

If this is a database of little importance then you could probably justify non SCSI. However personally I believe that a production database should never be placed on a Server without some tiype of Failover at the HD level, of course there are instances when a client over rules me.

If you truely want to go ATA then you should draw up a paper with the pros and cons of ATA versus SCSI with real world arguements (sorry in my opinion a couple bad experiences aren't valid reasons). THis way everbody in the decision making process is aware of the impact as-well-as potential problem on whatever system is settled upon. This also covers you incase of problems. If you go ATA and the drive fails in the middle of the day, you had made people aware of this potential situation and what the impact would be. Also if you go ATA be sure that you have a solid disaster recovery plan in place that has been tested. Having a solid and tested recovery plan that has been documented will allow you to solidly know that complete drive failure will require X hours to become production ready.

Sorry I don't mean to sound preachy but I remember being in the situation of having a fairly large DB get lost and discover that a recovery plan had not been implemented or tested and that backups were over a month old. A little time money and frustration up front can mean the world of difference when times are bad.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[donutman]

MDXer, thanks for the tips. I don't think I need a new server just yet. However, from the performance monitor (Target 835K, Total 641K), it does appear that I could use some more ram. There are definite spikes in various metrics, but the averages appear good.
I do have a question about the Processor Que Length (System): it averages 3 with a maximum of 12. The normal range is between 2 and 7. What does that mean? That was one of the metrics that your URL link suggested monitoring.

-Karl

 

[MDXer]

Here is a good explination.

http://www.sql-server-performance.com/q&a2.asp

if you average 3 accoring to the atricle you may have a potential bottleneck, but not a severe. In my opinion i isn't the numbers as much as it is the length of time the counter stays at or above the suggested value. if you had a proc que length of 3 for 15 minutes it would be more of an issue then having it for 15 seconds.

Good Luck

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck