[dba-SQLServer] Needs analysis

jwcolby jwcolby at colbyconsulting.com
Sat Jun 19 11:18:19 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 dba-SQLServer mailing list