I've got a couple of nonclustered indexes on tables that have rows inserted quite often. These indexes are currently around 90% fragmented, according to SQL Server. If I rebuild them, they go down to ~5% fragmentation but they quickly get back up to 90% in a couple of hours.
What's going on, why are my indexes so persistently fragmented, and do I need to worry about it?
Best Answer
Nonclustered indexes get fragmented whenever page splits occur; this happens mostly when inserting non-sequential data, i.e. not in index order (it happens occasionally when inserting in index order, too - but not nearly as often.)
Consider rebuilding them with 50% padding, i.e. all current pages will be left half-empty.
This gives you the time required to double the index before fragmentation becomes an issue again.