Is it ok to reorganize indexes with > 90% of fragmentation?

1.5k views Asked by At

MSDN recommends the following:

index fragmentation > 30%            => REBUILD
index fragmentation >  5% and < 30%  => REORGANIZE
index fragmentation <  5%            => DO NOTHING  

However, my SQL Server license is Standard and I can't rebuild indexes online. So, whenever I need to rebuild the indexes of my OrdersItemstable, with some millions of rows, my customers can't create/edit new orders for about 2-4 minutes. To solve this, I want to use the REORGANIZE operation every time.

  1. Is it ok even if the index fragmentation is high (> 90%)?
  2. Should I worry about a significant impact performance?

I'm worried with performance because I want to create a scheduled maintenance job that will execute REORGANIZE in all tables with frag > 5%

1

There are 1 answers

0
user_0 On BEST ANSWER

1) It works, but it can take longer time. 2) Depending of your server yes and no. If it is under pressure you are adding load. Anyway, it will not lock your tables.

Just run your job when load is lower and, if you can, stop it when load increase.