[AccessD] Needs analysis

Thanks Francisco.

What is meant by spindles.  That becomes a nebulous term when you throw in Raid arrays.  All of my 
data is on Raid6 arrays.  When processing these queries, except for the tempdb I am mostly reading. 
  My thoughts are:

1) Add much more memory so that more of the job fits in memory at once.
2) Move the database being read to SSD so that the IOPS skyrockets, not to mention transfer rate.

Once I do that, how many cores will SQL Server keep busy with crunching?  By that I mean, if I 
currently have too little memory, I am always waiting on disk.  If I am able to get data off the 
disk faster (SSDs) and into a significantly larger memory space, will I suddenly need more cores to 
process the available data.  I see times where I max out 3 of my 4 cores.  I reserve one core for 
the OS.

To add to the mix, I am considering (investigating as we speak) database compression.  I see about a 
50% (estimated) compression rate on my most common table structure using page compression. 
Compression is CPU intensive or so I am told, but it lightens the load on disk I/O and reduces the 
memory footprint.

I can inexpensively move from mu current 4 cores to 8 or 16 cores using the new AMD chips.  This 
move also would give me dual socket and 16 memory sockets.  That is a huge hardware difference, but 
will it make any real world performance difference?

John W. Colby
www.ColbyConsulting.com


Francisco Tapia wrote:
> When I get some time today or tomorrow I'll write you up some explicit
> directions on how to monitor for bottle necks.  The real problem you
> are encountering is i/o bottlenecks add either more spindles to
> improve performance.  In our environment I handle a few terabyte
> databases, each run on 12 or more spindles with our main system
> running on 18 spindles and we are looking at boosting it again to the
> mid twenties(26 total spindles I think) that gives you the best
> throughput.
> 
> Also even though your db is readonly if you are doing sorts of
> anykind, putting your tempdb on ssd drives will also give you a boost
> here.
> 
> Bottom line add more disk and maybe more ram.
> 
>