Sql-server – Index with high fragmentation percentage

fragmentationindexsql-server-2008

I have a very large table with enormous percentage of index fragmentation( almost 98 % !). Obviously, it needs index rebuild. Main problem is my SQL Server is in Standard edition, thats why I cannot perform online index rebuild. The table has very high production priority and I cant afford to get it offline. I want to try index reorganize several times, as it can be done online. My question is: do you think several reorganizes will be efficient way in order to low the fragmentation percentage ?

Best Answer

Short definition of Index Reorganize, made by Kendra Little:

Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

Even single reorganize should be efficient in Your case, but remember that it depends on Your data (how well pages get filled with row data).

Another advantage of reorganize in Your situation, other than being online operation, is that it can be stopped anytime (while index rebuild would have to rollback), so it's great to combine with Ola Hallengren's Maintenance Solution options: TimeLimit and WaitAtLowPriorityMaxDuration with WaitAtLowPriorityAbortAfterWait.

One disadvantage of reorganize I could think of, is that it can't be used to rebalance data between db files (for which index rebuild is often used).

As for fragmentation itself, even that high level shouldn't have much of impact on performance. It should free up a lot of space in data files, though. Still, the best approach is to have index maintenance scheduled regularly, just don't stress over it.

Kendra Little wrote another article about dealing with fragmentation recently, it's worth reading.

Related Question