October 4, 2004 at 1:15 pm
Folks
I am running our corporate server with 4 x 700mhz cpu's, 2.5gig RAM, and approximately 400 gig of disk space.
There are 85 databases, SQL replication is actively used, multiple daily downloads of data from our AS400 approximately 5 gig daily, supporting approximately 100 websites( some internal and some external).
We have been experiencing response time problems lately and I wish to make a case to migrate to bigger, badder machine.
My question is: Does anyone know of a site, documentation, software etc that can aid me in creating a recommended configuration ?
I feel I'm under powered but criticisms/suggestions are welcome.
Thank you all
October 4, 2004 at 1:27 pm
netimpress.com. Brian Kelley has a performance tuning book. You can also search performance here. I'd go big in your case, maybe even contact MS and ask them for some help. I'd look to an 8 or 16 ways box, even with newer faster CPUs. You want to be able to grow the next box a little.
October 4, 2004 at 2:20 pm
Depending on how quickly you need to improve things :
"Never throw hardware at a poor design, as the ROI is never as good as taking the time to rethink the original design" unknown credit.
Hardware is too often the easy way out.
We had a branch with some "Really good" programmers etc. One monthly process ran so long they had put in bigger hardware, and were already attempting to justify still bigger.
I got a hold of the scripts etc, after a few days I had the primary monthly process that took just under 2 days to run (70 % of the time, 30 % it crashed), to under 2 hours. I could have cut this in half again, but it was not worth my time. Interestingly we are running it here on 1/2 the hardware with twice as much other stuff.
I was able to clean up a bunch of other stuff with similiar results, the same office and others.
This WAS an extreme example, the main problems were pretty basic. But I've done enough cleanup on many systems to know that if it is performing poorly, until someone has reviewed it with performance in mind, there is probably a lot of wasted computer power.
I guess I'll follow up to, try to ID the bottleneck. Is it CPU, Disk IO, memory, network ....
What is causing the performance hit ?
Go to an 8 way and find out memory was the real culprit, or double the RAM and find out you are CPU constrained ... and you'll feel kinda ????
KlK
October 4, 2004 at 3:26 pm
I totally agree, I have been using Spotlight to evaluate the server and gradually cleaned up most of the warning messages. Soft Paging and CPU cycles > 80% , and CPU cycles / Processor queue length are the only warnings I get now. I got some improvement but not a great deal. I do have a number of third party applications running for which I have no way of evuating their internal workings. Thats another reason I don't like DTS pkgs.
October 5, 2004 at 8:06 am
I agree with KlK and Dave's suggestions to find out what your current bottleneck is, and if possible how close you are to other performance limits. That said, there are a number of sites that provide SQL Server configuration tools. The following hardware vendor sites (buyer beware) provide either tools or documents to help size SQL Server processing requirements.
http://www1.us.dell.com/content/topics/global.aspx/alliances/en/sizing?c=us&cs=555&l=en&s=biz
http://h71019.www7.hp.com/ActiveAnswers/cache/70729-0-0-0-121.aspx (You will have to register.)
Unfortunately, none of these will match your real world database.
Another useful tool in examining database performance relative to hardware is the Transaction Processing Council (TPC). The widely recognized TPC-C benchmark is a standard benchmark that has been executed against most common platforms. See http://www.tpc.org for benchmark descriptions and results.
One big difference in the TPC benchmark results and the vendors sizing information is that when vendors run the TPC benchmarks they extensively tune the application to get the best performans.
HTH,
Mike
October 5, 2004 at 8:27 am
P.S. As technicians, we often forget Return on Investement (ROI). Most of us work for organizations that must make a profit.
It is often cheaper to throw additional hardware at an application than it is to optimize the application. The trade off is often a minimal performance analysis to identify the bottlenecks and a $30K - $40K investment in new hardware versus a detailed performance analysis followed by database and application modifications. The cost of the database and application modifications, along with testing, certification, documentation, etc. must have a better ROI and less risk than throwing hardware at the problem.
Mike
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply