SQL Server Performance

seperating clustered index from table.

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by haridba, Dec 10, 2007.

  1. haridba New Member

    Hi All
    Couple of questions/concerns
    1. Can we seperate clustered index and its table into two seperate file groups(each file group in seperate physical disk) ?
    2. Is it worth in terms of performance to have clustered index and its table in two seperate filegroups?
    Thanks
  2. satya Moderator

    With a clustered index the actual data pages are the lowest level (leaf pages) of the index. You cannot separate the two.
    The reason is because a clustered index means that entries will be inserted into the table by the clustered index. You might need to create an index filegroup that does not include the clustered index. Eventually what I found on the seperation of tables, using lots of separate database files and filegroups can create so many parallel disk threads that the cost of handling the threads outweighs the benefits of distributing data. This is another reason why investing in more advanced hardware -- such as high-performance RAID controllers.

Share This Page