SQL Server Performance

Indexes and datafile storage

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by adef, May 29, 2008.

  1. adef New Member

    Are there any advantages of separating nonclustered indexes from base table in terms of storage. Clustered indexes resides on the same datafile as the base table right?
    I'm trying to justify if there is any performance gain by moving the nonclustered indexes on our databases from data disk drives to a designated disk drive for indexes.
    If the nonclustered row locator still need to fetch the data value from the clustered index or base table, is there any reason then why nonclustered index be housed in a different disk drive other than data disk drive?
    thanks
  2. MohammedU New Member

    Clustered index is nothing but the sorted base table...
    If you seperate clustered and non clustered indexes you may gain peformance but it depends on what kind of HW you have. If you have the SAN, you may not get performance boost because how the LUN gets created...
    http://msdn.microsoft.com/en-us/library/ms998577.aspx

Share This Page