Discussion in 'Getting Started' started by rashmi_1234, Feb 13, 2011.
What is the reason for fragmentation to increase even after rebuilding.
Welcome to the forum!
Users working with the database? 
Every data modification operation that has to be reflected in an index might contribute to fragmentation. So, as soon as you have finished your rebuilding and users start working with the database, modify this and that, fragmentation is likely to increase over time again.
Welcome to the forums.
As Frank referred, the usage of data will also lead to fragmentation and when the data is inserted/deleted/updated all the indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. All leaf pages of an index contain pointers to the next and the previous pages in the index. The fragmentation occurs on physical disk too in addition to database pages.
See the other related links on this toipc:
Besides normal users/application activity, heavy fragmentation can be caused by (auto)shrinking database/data files.
Thanks for the replies. But the question that i asked was:-
We ran index rebuilding jobs and that gave status of fragmentation.
And when we get the status many times it shows that just before index rebuilding the fragmentation was less and just after index rebuilding fragmentation increased.I have to explain the reaon to the users.What is the actual problem???
Most likely you'll observe this for objects with only a few pages. Check this answer from Paul Randal why it is not worth it to care about index maintenance for such objects.
Can you get those object names where the higher fragmentation or miscalculated values are shown?
Hi,Does your table have a clustered index, or is it heap table? As far as I know the only way to remove fragmentation from heap table is to build a clustered index in it and then drop it. While you can use rebuild and reorganize on heap table, it doesn't do much with there being no logical order for the data.
Separate names with a comma.