I am trying to defragment indexes on SQL Server 2008 using ALTER INDEX--REBUILD. After defragmentation some indexes have fragmentation percentage value greater than its before defragmentation value. Is it possible or an issue ?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
migrated from stackoverflow.com Jul 15 '14 at 7:47
This question came from our site for professional and enthusiast programmers.
For indexes with small page_count this vaue is normal. Page_count can be seen from sys.dm_db_index_physical_stats. If page_count > 1000 only then go for index rebuild or reorganize. Sometimes when you rebuil index and use lower value of fill factor more pages are required and fragmentation might occur. Also when page count is low allocation to new pages are not done from uniform extent but from mixed so chances are that fragmentation will occur.But such fragmentation will not hamper any activity
If you are rebuilding index with ONLINE=ON option and used MAXDOP hint there can be more fragmentation as pointed in article from Bob Dorr from Microsoft have a look at this
PS: If you have small page count no need to worry
Hi if this is part of a maintenance plan the I would suggest calling the
The REORGANIZE has an option to Compact large objects. However, I only do this once a week on the lowest active time.