Index Fragmentation

Last Post 26 Dec 2012 04:30 AM by peter. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Resolved
rerichards
New Member
New Member

--
20 Dec 2012 04:24 PM
We have a table with a clustered index on an identity column (ever increasing).

This table is an insert only table, never updated.

There are several nonclustered indexes on the table as well.

Is it possible that these indexes become fragmented? If so, how?
rm
New Member
New Member

--
21 Dec 2012 05:42 AM
New rows may cause non-clustered index page split.
gunneyk
New Member
New Member

--
21 Dec 2012 05:54 AM
Do you see fragmentation in the clustered index? If so how much? As rm pointed out the NCI's can easily get fragmentation since they are on columns other than the identity column and thus the values being inserted are not ever increasing such as the identiy and eventually you will get page splits.
rerichards
New Member
New Member

--
21 Dec 2012 07:50 AM
I am not seeing fragmentation on the clustered index, but I am on the nonclustered indexes. I mostly wanted to confirm what I was seeing. Thanks.
gunneyk
New Member
New Member

--
22 Dec 2012 01:11 PM
If you rebuild those clustered indexes you might want to change the fill factor to some value less than 100 to minimize the page splits and thus the fragmentation.
peter
New Member
New Member

--
26 Dec 2012 04:30 AM
the ans is yes ..n for more detail follow the link

http://sqlfool.com/2009/06/index-de...cript-v30/

Other than a long except the query You can defragment your indexes via any third party tool other than a long except the query

You are not authorized to post a reply.

Acceptable Use Policy