[AccessD] Needs analysis

Hmmm, couple of questions.  But before I begin, please understand that I
work with SQL Server, but nowhere near as in depth as you apparently
are.  So PLEASE correct me if I am wrong, and I would appreciate any
links/info you have to share on it.  I do understand quite a bit about
hardware though, so that's why I am still tossing my two cents in here.

First, you said you split your RAID volume into 2 partitions due to a
Windows limitation... what limitation is that?  FAT 32 is limited to 2
terrabytes, but NTFS can have WAY more than that.  We have several
servers here running Windows 2003, (standard and enterprise mixed) which
have 5+ terrbytes.

Next, I am not saying 'get faster disks'.  LOL, I really am trying to
just be helpful here.  In fact, right now, I'm in process of getting two
new servers for our systems, so I know how expensive things can be! LOL.
I am specifically telling you to stay away from RAID 6 (and RAID 5).
Either go with RAID 0 (with no redundancy) or a RAID 10.  I certainly
understand the cost involved, and I'm not trying to say 'tough, do this
or die!' LOL.  I am simply pointing out that while you are looking at
memory and processor specs, factor in a drive set that will compliment
them.

Where I am confused about SQL Server, is that while I know it uses
whatever memory it can take, I didn't think it runs strictly off of
memory, whether you are simply reading or not.  IE, if you access xx
number of records, sure, it would cache what it pulls, but I don't think
it pulls the entire DB into memory from the get go.  That sounds more
like the 'new' dbs that have been talked about, which do run completely
in memory.  

I would assume (and you know what happens when we assume things! LOL),
that if you are truly treating a db as read only, then the disks are
only going to be accessed for new information.  But ANY write backs,
would cause multiple memory <--> disk operations.  Memory is always
going to be faster than disk, so the quicker the disk operations are,
the less bottlenecking you'll face.

So here's a question for you, have you ever thought about splitting the
load between different machines on a gigabit network?

Again, sorry if I seem clunky in my thoughts here, I've perused your
threads about this stuff, but have too much going on, on my own, to keep
too much detail of it in my head.

I have noted many of the performance issues you've mentioned in the
past, and many of them are functionally identical to when a RAID goes
beserk, or can't handle the load.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, June 21, 2010 1:28 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Needs analysis

LOL, yep one terabyte, not one gigabyte.

As for 10, I got it.  Span to do the RAID 0, then mirror to do the RAID
1.  So that is 2X.

I went back in and looked at what I actually have.  I have eight 1
terabyte drives.  I use two of 
them Raid 1.  That leaves six which I use to create a single RAID 6
array.  That leaves four 
terabytes of data storage.  Due to the limitations of Windows, I break
that into two 2 terabyte 
volumes.

 > You said you had a budget for upgrading your machine.  John, I'm
serious, when reading and 
writing from a database, your absolute most limiting factor is going to
be drive speed!

Of course this is true.  However there is more to the story.  For
example


Imagine I have a database.  The whole thing is only 500 megs of data
indexes and all.  I have 16 
gigs of ram.  Yes, the slowest thing is the disk but... SQL Server loads
the whole damn thing into 
ram and then executes ten thousand queries pulling this that and the
other, all "instantly".

Next scenario, imagine same server, but the database is 40 gigs.  Now
only a piece can be loaded at 
a time.

You get the point.  At what point does it pay to go to more memory vs
the disk thing?  If I invest 
heavily in memory, now I have the whole thing loaded in memory but I
have 47 potential threads but 
only 3 available cores.  At what point does it pay to invest in a ton of
cores?

Drew, I understand the disk thing, and I know that I absolutely am
hitting an I/O bottleneck. 
However it seems not useful to just say "get faster disks".  I know that
needs to happen but there 
is much much more to this than "more disks".  Understand that these
databases are freakin huge. 
Just reading the data off a raid 0 or 10 will still take a ton of time
if the disks in the raid are 
magnetic.

OTOH I do not write back to these big databases.  During processing they
are literally read-only. 
And much of the time I hit two specific databases, and then write a
"small" (few thousand to few 
million) record order table.  My plan (in the absence of explicit
bottleneck data) is to go get some 
SSDs to hold those two databases, probably two 60 gig SSDs for each
database, raid 0 (120 gig total 
per database).

That effectively does what you are discussing, but just for my two most
frequently used databases. 
After that, providing a processor / memory pipe that can handle the
flood of data will still be a 
requirement.

John W. Colby
www.ColbyConsulting.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.