[dba-SQLServer] SQL Server - SSD / Rotating media - Side by side test results

jwcolby jwcolby at colbyconsulting.com
Sat Jul 2 13:36:42 CDT 2011


Arthur,

There are definite cases where the gains are minimal, others where they are significant.  The other 
thing is that I am intentionally clearing the cache before each test.  The cache further minimizes 
the differences as it turns out. That is to be expected of course.  This just goes to show the old 
axiom that throwing memory at SQL Server does a world of good.

Without a shadow of a doubt, one thing that SSDs (and faster / better hardware in general) do is 
minimize the impact of ignorance and sloth.  ;)

I am not an accomplished DBA, and I simply do not have the time to become one.  As a result I am 
unable to correctly tune my system.  By throwing cores, memory and SSDs at the problem I manage to 
achieve respectable results in spite of myself.

Hardware is cheap.  My entire server cost somewhere in the neighborhood of $5K.  Additionally I have 
dragged disk and RAID controllers forward through many upgrades.  Back around 2005 I spent the then 
enormous sum of $1600 for three Areca raid controllers which I am still using today.  I bought 10 1 
TB drives back when they were $150, but I am still using them today.

What I upgrade are the motherboards and more frequently the processors.  In 2004 I started with 
single core AMD 3800 processors using Windows2003 X32 and 4 gigs of RAM.  I built two systems for 
$4000!  Moving up to dual and then quad cores, and Windows / SQL Server X64 and 8 GB RAM, then 16 
gigs of ram.  My latest motherboard / processor cost me (my client of course) about $700 (8 cores, 
with 24 cores possible) and 32 gigs of ram was about $1000.  I was looking last night and another 32 
GB of RAM (same modules) is now only $600!  And... I am using my entire old server (quad core / 16 
gigs ram) for a VM server.

The point really is that while it is not a trivial amount spent over the years making these 
upgrades, over that same period I billed a couple of hundred thousand dollars.  All these upgrades 
make me more and more productive, faster and faster getting the results back to the client.  The 
client *loves me* precisely because he gets results back in hours instead of a week as his previous 
provider gave him.  I program custom C# solutions (and bill him for the programming) which have 
enabled me to do orders literally in hours which (back in 2006) took me a full day or even two to 
get out.  Counts which took an hour in 2004 now take my custom program 2 minutes.

*AND* I have developed a system which allows him to send emails with zip lists as attachments.  A 
program running on my server strips off the CSV attachment, generate counts, build a count 
spreadsheet, attach it to an email and send it back to him literally within 5 minutes of him 
pressing send *without* my doing anything.  Again those counts used to take me an hour back when I 
did everything by hand.  Now I log that a count came in and put a small charge in my billing database!

The lesson for me is that my time is worth much more than the cost of the electronics and my 
response time is what makes me valuable to the client.

I fully understand than everyone cannot solve all their problems by throwing hardware / custom 
software at it, but for a sole proprietor it just might be the only way!  I don't have the time to 
be good at all the hats I wear!

And so I do things like spend a thousand on SSDs on an educated guess that they will make a 
significant difference for an uneducated sloth. :)

And finally, because the client loves me, he is sending me a *ton* more work!

MORE CORES!  MORE MEMORY!  More SSDs! :):):)

John W. Colby
www.ColbyConsulting.com

On 7/2/2011 1:48 PM, Arthur Fuller wrote:
> I would be happy to assist. Judging by your IMO rather narrow result-gap (measured in a few
> seconds), my initial guess would be that the SSHDs are not gaining you much over your investment in
> CPU and RAM. However, that remains to be determined. Could be that table-scans or some other factor
> are causing this lag. Should be that SSHD retrieves ought to be an order of magnitude quicker, but
> according to your posted measurements they lag significantly behind that thumbnail benchmark.
>
> And besides all that, how are you? What's new with you and your family?
>
> A.



More information about the dba-SQLServer mailing list