up vote 2 down vote favorite
share [g+] share [fb]

Why does index reorganizing take 3 times longer after I make an index rebuild?

I wasn't sure if this happens every time, so I made a test for the last month. I configured SSMS to execute Index Reorganize plans daily (a 4AM), and an online Index Rebuild weekly (Sundays at 5AM), regardless of fragmentation levels. Rebuild was set to fill pages at 85%.

What bothers me is that first reorganizing after a rebuild took much longer every time, and it doesn't make sense (at least with my understanding of what each of the operation does).

link|improve this question
83% accept rate
feedback

1 Answer

up vote 1 down vote

Can you check what is the number of pages allocated for the index after the online rebuild (before the reorganize) and after first reorganize following the rebuild? Check total_pages in sys.allocation_units. I suspect the reorganize is compacting the index after the rebuild and thus moving quite a bit of data around.

link|improve this answer
Thanks, I will try this next time. But, fill factor is defined in index properties, and is not overriden in index rebuild settings, so I believe both operations should respect the value. According to MSDN: Reorganizing also compacts the index pages. (...) Compaction is based on the fill factor value in the sys.indexes catalog view. – Groo May 11 '11 at 7:35
feedback

Your Answer

 
or
required, but never shown
discard

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