Francisco Tapia
fhtapia at gmail.com
Sat Jun 19 13:50:24 CDT 2010
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. On 6/19/10, jwcolby <jwcolby at colbyconsulting.com> wrote: > 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 > -- Sent from my mobile device -Francisco http://sqlthis.blogspot.com | Tsql and More... http://bit.ly/sqlthis