[AccessD] Needs analysis

jwcolby jwcolby at colbyconsulting.com
Sat Jun 19 10:56:30 CDT 2010


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



More information about the AccessD mailing list