Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Bigger, Better, Faster
If you need to support a large number of transactions or a DW, make sure that your hardware is up to par for such functionality. Hardware costs go down dramatically each month, but you might still have to fight for your budget with the executives. More memory isn't the answer for everything; after a while, you'll see diminished returns for the amount of memory added. However, additional memory and faster disks can often mean better performance. We've seen analysis services cube processing time reduced from 20 hours to 15 minutes when we put the data warehouse on the appropriate hardware.
If you have disk contention problems, examine how your data and log files are arranged on the disk—this is part of your physical model. In general, it's a good idea to keep transaction logs and data files on separate physical disks (or disk arrays). Occasionally you might also find it useful to move some of the large tables to separate disks. The advantage of doing so is that read/write activity can occur in parallel across several physical disks. Moving a table to a different disk requires two steps:
Create a secondary file group on a separate physical disk.
Drop the clustered index of the large table and re-create it on the secondary file group.
Today's high-quality disk controllers can usually support numerous transactions without any problems, so if the performance numbers you expect can't be accomplished then chances are that your disk controllers are outdated.