Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 stackoverflow.com Jul 15 at 7:47

This question came from our site for professional and enthusiast programmers.

2 Answers 2

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

http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx

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

ALTER INDEX [UNIQUE_XXX] ON [dbo].[XXX] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO

--PRIOR TO YOUR  - ALTER INDEX--REBUILD

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 at 5:08

Your Answer

 
discard

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.