Extent Fragmentation High

I had come across a situation when one of my tables shows highly fragmented. When I looked into table I found that there is only small amount of data (2500 rows).There are no much updates or deletes happening in the table. So I am little bit confused why this happens .So I Re-index the table and again it shows the same data. So I spend  little hours on this finding the Problem. At last I found this is due to the fill factor specified when created  the index.


FROM   universities

–Running a DBCC Command to view the Fragmentation

DBCC SHOWCONTIG(‘universities’)

–Finding the fillfactor values an Index.So here Pk is the Index.

SELECT [name],

FROM   sys.indexes

WHERE  name = ’PK_Universities’

–Rebuild the Index with the Fillfactor 1

ALTER INDEX   PK_Universities

ON universities

REBUILD WITH (fillfactor = 1)

–Running a DBCC Command to view the Fragmentation

DBCC SHOWCONTIG(‘universities’)

Here comes the Question, what is fill factor?

An indexes fill factor specifies the percentage that the index data pages on disk are filled when the index is first created. This is ideal from a disk capacity standpoint as there is no wasted space with data pages that are not fully allocated. However, this is not ideal from a SQL Server performance perspective regarding data updates (Inserts, Updates and Deletes). If you create a clustered index that has a fill factor of 100%, every time a record is inserted, deleted or even modified, page splits can occur because there is likely no room on the existing index data page to write the change. Page splits increase IO and can dramatically degrade SQL Server performance.