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.
–MyTable
SELECT *
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],
[fill_factor]
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.
Vignesh
14 years agoSuperb…………..!