Bill Patten
bill_patten at embarqmail.com
Sat Jun 19 12:09:36 CDT 2010
Hi John, I really do not have an answer to your question, but it seems to me since you already have processes running you could do analysis of your current situation and determine where the bottlenecks are. I know that you frequent sql ServerCentral, and one of the members there often does presentations using stored procs at our local SQL Server User Group, to determine timing and index issues. (Jason Brimhall). You might try posting this question over there. Also http://www.sql-server-performance.com/articles/audit/hardware_bottlenecks_p1.aspx has some info on "Identifying Hardware Bottlenecks" that may be of help. Anyway, your in way over my head but hope fully this may help. Bill -------------------------------------------------- From: "jwcolby" <jwcolby at colbyconsulting.com> Sent: Saturday, June 19, 2010 8:56 AM To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Subject: [AccessD] Needs analysis 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com