[AccessD] Needs analysis

Memory and processor can also go more and faster, but to throw my two
cents in, drop the RAID 6.

http://en.wikipedia.org/wiki/Standard_RAID_levels

http://en.wikipedia.org/wiki/Nested_RAID_levels

RAID 0 gives you the best performance of any RAID.  However, it has NO
redundancy, so if you lost one drive, you lose all your data.  RAID 5 is
probably the worst for performance, and RAID 6 is a little better (well,
RAID 1 in some scenarios is worse..it has NO write advantage, and in
older implementations, RAID 1 doesn't let both drives read at the same
time, but most new RAID controllers do this, so RAID 1 , in a two drive
system would read at the same speed as a RAID 0 with 2 drives.)

I personally recommend RAID 10 (or RAID 1+0).  That is NOT RAID 0 +1
(there is a difference, described in the second link.  One is a mirrored
stripe, the other is a striped mirror.  RAID 1 +0 gives you the absolute
best performance along with redundancy.  And as you can see in the
second link, that's what is recommended for database systems, in fact,
it's what MS recommends for exchange servers.

Drew


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Saturday, June 19, 2010 10:57 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Needs analysis

I am about to upgrade my SQL server.  Currently I run a quad core with
16 gigs ram, using data on 
raid6 arrays with a dedicated raid co-processor.  I have an opportunity
to build a server that 
better meets my needs but I need to discover what those needs are.

As I have posted previously I process fairly substantial lists where
(for example) I will join a 
table with 20 million names to a table with 65 million names on a sha
hash field and select by a 
half dozen field criteria.  Stuff like that.  My databases are,
generally speaking, read-only.  This 
is not transaction stuff, but rather "data mining" kind of stuff.

These queries can take a long time to run, tens of minutes or more.
What I would like to find out 
is what is the bottleneck.  If I increased my memory to 32 gigs would
that be enough?  Would 64 gigs 
be better or not be any better than 32 gigs?  How much memory do these
queries want?  If I increased 
my cores to 8 or 16 would that be enough?  How many threads would these
queries use?  If I moved 
some of the database onto SSDs would that help more than additional
memory?  How much time / 
resource is spent loading the data off of disks.

I have absolutely no idea how to discover this kind of information.  I
am going to have X dollars to 
use to build a server, and of course X is never enough, so I need to
decide whether to spend more on 
cores, memory or disks and in what combination.  As an example I have
enough to buy either 24 cores 
and 32 gigs of memory, or 16 cores and 64 gigs of ram, or 16 cores and
32 gigs of ram and a bunch of 
SSDs.

I am pretty sure that regardless of what I do I will get a substantial
performance leap, however 
maximizing that performance leap is still a good thing.

Any help appreciated.  BTW, I am NOT a DBA so if you give advice like
"look at the logs", please 
give specific directions on how to do that.

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.