The Database Engine Tuning Advisor only gets you so far and some of the recommendations it generates are bad recommendations. For example, it might suggest redundant overlapping indexes. It currently takes a human to properly set up indexes.
Your best bet is to find the bottleneck and try to quantify how much that bottleneck is impacting server operations. It would be hard to quantify any benefit from upgrades unless you know what the bottleneck is. Adding a CPU if the existing CPU never gets above 10% utilization wont do much. You can find bottlenecks using a number of methods. Wait stats would be the first thing I would check. In my experience, the hard drive is often the bottleneck. If your budget is limited I would make a guess that it is better to add more drive spindles than add another CPU. If you only have one drive array in your server that can be a problem, even if it is RAID 10. Adding RAM takes pressure off the hard drives and is usually a good idea. 4 GB is considered a small amount of RAM for a production SQL Server box. Even 8 GB might be too little depending on what you are doing. -Mike Chabot http://www.linkedin.com/in/chabot On Thu, Jul 29, 2010 at 8:40 AM, John M Bliss <bliss.j...@gmail.com> wrote: > > Hi. I have a SQL Server running on: > > DELL PowerEdge 2950 > 4 GB Memory > 1 Processors with 4 Cores > Windows 2003 Standard - 32 bit > > ...and one 20 GB database. Normally, everything runs very quickly. In > certain edge-cases such as: user requests 14 month report resulting in query > joining 400,000 rows and 15,000,000 rows and...etc...SQL Server takes about > 1 minute to return results. I've optimized the database and optimized the > SQL and Database Engine Tuning Advisor has no more recommendations for me. > > I have the following three hardware improvement options and need to quantify > (as much as possible) performance increases that'll be realized by > implementing each: > > - add a second Processors with 4 Cores > - upgrade memory from 4GB to 8GB and move to 64 bit Windows Server 2008 > - increase number of hard drives to achieve RAID 10 > > Suggestions for a tool and/or some documentation that'll help with this? > Google has not produced anything useful yet... > > -- > John Bliss > IT Professional > @jbliss (t) / http://www.brandiandjohn.com > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335858 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
