Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 ?

share|improve this question

migrated from 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

share|improve this answer

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.

share|improve this answer
Thanks. I will try this. – Alen Jul 16 '14 at 5:08

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.