Question About Index Optimize

[Nehamnsu]

1. Is it a good idea to provide fragmentationlevel1 and fragmentationlevel2 parameters in the IndexOptimze SP?

2. Currently, we are not leveraging @UpdateStatistics parameter in the IndexOptimize stored procedure.. My understanding is that update statistics is run automatically when indexes are rebuild. We absolutely want to update stats but because the assumption is that it’s run as part of index rebuild, we didn’t specify the parameter in the stored procedure call. Is this applicable for Index optimize as well? Please advise if we should include @UpdateStatistics and @onlyModifiedStatistics parameters as well?

[olahallengren]

Is it a good idea to provide fragmentationlevel1 and fragmentationlevel2 parameters in the IndexOptimze SP?

In most scenarios, the default values works well. Then you don't need to specify these parameter.

For more details, please see the documentation:
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html#FragmentationLevel1
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html#FragmentationLevel2

My understanding is that update statistics is run automatically when indexes are rebuild.

It is correct that the statistics is updated, when an index is rebuilt. However that is not the case for when an index is reorganized.

We absolutely want to update stats but because the assumption is that it’s run as part of index rebuild, we didn’t specify the parameter in the stored procedure call. Is this applicable for Index optimize as well? Please advise if we should include @UpdateStatistics and @onlyModifiedStatistics parameters as well?

In order to assure that the statistics is updated, you should specify the parameters @UpdateStatistics = 'ALL' and @OnlyModifiedStatistics = 'Y'.

[Nehamnsu]

Thank you