SQL Server Index Storage – When to Store Nonclustered Indexes on Separate Filegroups

At the risk of being way too general in my answer, I will say that you should run an index maintenance process regularly. However, your index maintenance process should only rebuild/reorganize the indexes that specifically require it.

This presents the question: when does an index require to be rebuilt or reorganized? Rolando touched on this nicely. Again, I risk being extremely broad. An index requires maintenance when the fragmentation level adversely affects performance. This level of fragmentation could vary based on the size and composition of the index.

Speaking for SQL Server, I tend to choose a index size and index fragmentation level at which point I begin performing index maintenance. If an index contains less than 100 pages, I will perform no maintenance.

If an index is between 10% and 30% fragmented, I will REORGANIZE the index and UPDATE the statistics. If an index is over 30% fragmented, I will REBUILD the index - with no UPDATE STATISTICS, as this is taken care of by the REBUILD. Remember though that a rebuild only updates the statistics object directly associated with the index. Other column statistics will need to be maintained separately.

This answer is really just a long way to say: Yes, you should do routine index maintenance, but only on the indexes that need it.

First and foremost: those logical disks better be backed by at least 8 different physical disks. If you're asking about load balancing 8 logical disks created on the same physical storage (the same spindles) then you're wasting time.

The best (and simplest!) option is to create a single filegroup with 8 files (equal in size and pregrown), each on a spindle, and then place the table and the indexes in this filegroup. SQL Server will balance the data equally among the files.

Partitioning is a feature for ETL switch in and switch out. It should not be used for performance, as the best you can hope for is equal performance with the original table. For performance use a well designed clustered index, one that matches the typical load.

If your data is really known upfront and the index usage characteristics are very well understood then you may try to balance them explicitly on their own filegroups. But trying to wrestle manual control over this is more likely to cause harm than benefit. The simpler option of single file group with 8 files balances IO better than manual explicit control 99% of the times.