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 OrdersItems
table, 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.
- Is it ok even if the index fragmentation is high (> 90%)?
- 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) 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.