[AccessD] 4 gig dims (relatively) cheap

John,

Is that your "database from hell"?  My sympathies!

Steve Erbach
Neenah, WI


On Sat, Jun 13, 2009 at 6:52 PM, jwcolby<jwcolby at colbyconsulting.com> wrote:
> Steve,
>
> This is for 4g dimms.  I have 8 gigs in my servers right now but that is (4) sticks of 2 gigs each.
>  I am now going to replace that with (4) sticks of 4 gigs each.
>
> I run SQL Server on these machines and I have largish databases, about 180 gig database files, 50
> million records, 640 fields wide.  I have to do cover indexes on up to 14 or more fields at a time
> to get my speeds up to snuff.  If I can get twice the memory it should help with the processing
> speed a bit.
>
> My next move is to place these databases on a raid of solid state disks, probably a raid (5) using
> three (to start) of these:
>
> http://www.newegg.com/Product/Product.aspx?Item=N82E16820227395
>
> This is a review of the drive comparing it to other drives, both SSDs as well as normal hard drives.
>
> http://benchmarkreviews.com/index.php?option=com_content&task=view&id=299&Itemid=60
>
> Perhaps of most interest to me are these three tests which show read/write speeds (I rarely write to
> my drives except for generating cover indexes) as well as IOPS.
>
> Access times:
>
> http://benchmarkreviews.com/index.php?option=com_content&task=view&id=299&Itemid=60&limit=1&limitstart=5
>
> Read / write speeds:
>
> http://benchmarkreviews.com/index.php?option=com_content&task=view&id=299&Itemid=60&limit=1&limitstart=5
>
> IOPS:
>
> http://benchmarkreviews.com/index.php?option=com_content&task=view&id=299&Itemid=60&limit=1&limitstart=8
>
> My databases are the exception in that they are almost entirely read only.  This will provide an
> advantage in that I will not be wearing out the SSDs writing to them all of the time, and for a
> database, the scorching read data rates and the truly scorching IOPS rate MIGHT provide truly
> scorching query processing rates.  And to top it off, SSDs to not suffer from the effects of disk
> fragmentation - reads to any location are equally fast.
>
> Basically I set up this "database from hell" which is the very wide table on a raid 6 array of
> normal hard drives.  However I do not USE that database directly.  I set up cover indexes, many of
> which are static, they cover the most common fields that are used over and over from order to order.
>  I then (very occasionally now) have to generate cover indexes for specific sets of fields from
> that big wide table.
>
> If I can set JUST those cover indexes into database files out on a raid 5 array of these SSDs I
> should get just phenomenal performance.  Given disk access times of 100 microseconds, a raid
> sustained data transfer rate of as much as 500 mbytes / second, and even more if I add more disks to
> the raid array,  and IOPS of many thousands per second, it would SEEM that even large queries should
> simply fly.  Talk about keeping the processor fed...
>
> We shall see.
>
>
> John W. Colby