Mike had a lot of good comments. How dynamic is your data? Is it constantly being updated? If so, do you need real-time query results?
You need to balance the creation of indexes (as many as possible on a relatively static server) with the fact that indexes can slow down insert/update/delete operations. It sounds like it's your select statements that are slow so have you gone through all those queries and indexed everything properly? If so, I'd be surprised if you're not running out of RAM with only 4GB. This of course leads to dropped indexes, which leads to poor performance. Analyze the RAM usage of your SQL Server, especially once you've tuned your indexes. Then buy (more than) the amount of RAM you'll need. Don't automatically assume that 8GB will be enough because there's a good chance it won't be. Regarding cores, are you going to have to run this app under load? Will you have multiple people requesting the same queries? Can you cache results? If you have a query that takes even 20 seconds to respond and you can't cache the results, and you expect to have multiple concurrent users, then you'll run out of cores very quickly. If you don't need real-time data, or your data is fairly static, you can also look into Microsoft Analysis Services. - Andrew. On 2010-07-29, at 08:40, John M Bliss 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:335859 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
