More Memory Fixes Everything
If at first your server sucks… just throw more memory at it… specifically, get the most bang for the buck and fill up all your RAM slots with the stuff (currently, it’s 4GB sticks).
Seriously.
Insufficient RAM has been the single biggest impediment to my data warehouse development efforts. That, and disk contention (yes, I know the two are related). More RAM means SQL Server has a chance at not even touching a hard disk unless it really really has to. Combine that with the built-in compression of SQL Server 2008, and things can get really fast really quick. Imagine actually reaching that promised land of always being CPU-bound rather than disk IO bound.
As reference points, I had been stuck with a 32-bit server running 3 instances of SQL Server, each of which had 2 GB of statically-allocated RAM, leaving the OS, SSRS, and SSAS with 2GB to fight over. And fight they did, believe you me. Nothing sucks more than the OS paging out all your fundamental SSRS files… right when everyone wants to access them. That’s also about when you hit “process” on some grand new mining model scheme you’ve been working towards for a while. Things don’t go well for the next bit of time.
Another thing- try to have lots and lots of physically independent hard disk [arrays]. The secret is to embrace the inner parallellisation fiend. Lots of RAM and 8 cores allows for that. I’ve cobbled together a new server that has 16GB of RAM and 4 physically independent drive arrays. I cannot emphasize enough how much better that is. We’re talking an order of magnitude, especially when SQL Server gets everything all cached in memory. Soon, I will be getting a total of 32GB for this server, a fibre channel card so I can get some SAN juice, and maybe later some larger local drives (which would be very nice). Incidentally, if you have to use a SAN, make absolutely sure you know what’s physically indepdendent (not logically - that’s not good enough). If you have to share a tray of drives with too many other virtual machines or something, your performance is toast, and you can give up on ever doing the really cool classifier stuff in Analysis Services [so you can nuke all those pesky exploitive gold farmers].