SQL Server Performance

Defrag Non Clustered Indexes

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Arhamg, Mar 3, 2010.

  1. Arhamg New Member

    Hi,
    How important it is to defrag Non Clustered indexes?
    Me and my colleague were discussing the defrag activity and I was of the opinion that we should defrag non clustered indexes along with clustered indexes but I was not able to provide a proper resoning for the same. In some tables we have upto 85% fragmentation in non clustered indexes and 0% in clustered indexes.
  2. preethi Member

    When non clustered indexes are fragmented, you will perform additional IO operations when you use those indexes.
    Additionally, fragmentation is not accounted for in statistics. That means, your estimates are already skewed,
    de-fragmenting the index is comparatively faster than rebuilding the index. If the table is 85% fragmented, rebuilding the index is the best option. I believe MS recommends defragment if the fragmentation is <= 30%
  3. Arhamg New Member

    Thanks Preethi for your reply.
    Just to re-phrase: By additional IO you mean the index is spread over more pages than it should and due to this more pages are referred to fetch the required data, incase of fragmented non clustered index.
    And if the estimates are skewed then the plan being used might not be the optimal plan ??
    Can you please suggest some links for further reading for the same, specifically the cons of fragmented non clustered index.

Share This Page