Home / server / Questions / 268052
In Process
Groo
Asked: 2011-05-10 23:40:41 +0800 CST

Index reorganizing slow after index rebuild with MS SQL

  • 772

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).

sql-server

1 Answers

  1. 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.

    • 2