SQL Server 2008 - Worth the Wait
Multiple CPUs in the server are important for data warehousing applications because they allow SQL Server to run parallel queries, which can greatly speed performance. If you know your data warehouse or OLAP cube is going to be busy, seriously consider 4, 8, or more CPUs, especially dual and quad CPUs. [7.0, 2000, 2005] Updated 1-28-2005
*****
Like with any SQL Server application, the more RAM, the better. Don't consider any RAM less than 512MB in a OLAP production server, and more is better. [7.0, 2000, 2005] Updated 1-28-2005
Your disk I/O system should be at least RAID 5, and RAID 10 is even better. Since your servers will be doing much more reading than writing, RAID 5 is not as much a problem as it is with OLTP applications where there are a lot of writes taking place. And as always with any SQL Server applications, more smaller drives are better than fewer larger drives. The more physical disks, the more the I/Os can be spread out, increasing overall system I/O performance. [7.0, 2000, 2005] Updated 1-28-2005
Don't forget your user's hardware. Like server hardware, it can directly affect performance. This is especially true if you will be putting cubes on user's workstations.
At a minimum, workstations should have at least one 2 GHz CPU and 512 MB RAM, and IDE drive storage capacity large enough for any local cubes. For power users, consider purchasing them 2 CPU (single, dual, or quad core) workstations with 1 GB or more of RAM, and fast SCSI drives large enough to hold any local cubes. [7.0, 2000, 2005] Updated 1-28-2005
Since log files are rarely written to in data warehousing applications, you can put them on the same RAID arrays as your databases instead of locating them on separate arrays as is recommended for OLTP databases. [7.0, 2000, 2005] Updated 1-28-2005
Always run OLAP applications on their own dedicated servers, never sharing a server running OLTP applications. The two types of applications are mutually exclusive when it comes to performance tuning. [7.0, 2000, 2005] Updated 1-28-2005
If your data warehousing/OLAP application is very busy, consider separating the data warehouse data from the OLAP cubes, putting each on its own dedicated server.
If you do this, you will find that MOLAP cube processor time is 100% run on the dedicated Analysis Services (OLAP) server. But if you use ROLAP or HOLAP cubes, most of the processor time will be on the data warehouse server, with only some of it on the Analysis (OLAP) server. How much processor time that is devoted to the data warehouse server and the Analysis Services (OLAP) server for ROLAP and HOLAP cubes will depend on the number of aggradations performed.
So the type of cube you select will help determine on which server you should have the most processor power. [7.0, 2000, 2005] Updated 1-28-2005
If you choose to run the data warehouse and the cubes on separate servers (which is recommended for best performance) these servers should be connected via a very fast, switched network, ideally, 100Mbs, full-duplex, or faster. [7.0, 2000, 2005] Updated 1-28-2005
Since most data warehousing applications will make significant use of such features as DISTINCT, GROUP BY, ORDER BY, and other aggregate functions, the tempdb database will be used substantially. To maximize the performance of the tempdb database, place it on a dedicated array, such as a mirrored drive set, of its own. This way, there will be less resource conflict between it and other SQL Server I/O operations. [7.0, 2000, 2005] Updated 1-28-2005
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset