0

I was thinking of performing The index maintenance and stats collection jobs in the below order for all databases in an Instance.

step 1.Perform all index maintenance activity for all required Indexes(based on thresholds) of all databases in the instance. This step should not do any Stats collection (other than the stats update naturally done by the index rebuild).

step 2.Perform Stats collection of all objects in all databases in the same instance. But this should exclude all Indexes which already had a stats collection as part of Step 1.

Is there anyway I can achieve this with Ola Hallgren Maintenance Solution?

Improve this question
1
  • What is the reason you want to explicitly perform these as two separate steps? Ola's code is optimized to have them run together as a single step. Separating those two steps is certainly possible, but raises a bunch of questions and would require additional code/work to make it happen.
    – AMtwo
    Jan 4, 2022 at 19:38

1 Answer 1

Reset to default
2

These don't need to be done as separate steps. In fact, it would be much harder to do as separate steps, as you would want to filter out any indexes rebuilt in your Step 1, to avoid duplicate work in Step 2.

Looking at the Examples section on Ola's site:

B. Rebuild or reorganize all indexes with fragmentation and update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
  @Databases = 'USER_DATABASES',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationLevel1 = 5,
  @FragmentationLevel2 = 30,
  @UpdateStatistics = 'ALL',
  @OnlyModifiedStatistics = 'Y';

You can simply use both the @UpdateStatistics parameter in conjunction with the index rebuild/reorg parameters.

That said, you probably don't need to be rebuilding your indexes very frequently. Most folks just need a frequent (eg weekly) job to update stats, and an infrequent (eg quarterly) job to rebuild indexes.

Improve this answer
3
  • Thanks. I am in such a situation, where the maintenance window is very limited for the entire instance. So I am trying to avoid all blocking/exclusive locking caused by Index rebuild activity(even in the case of online rebuild) by clubbing all Index rebuilds to happen first, so that I can accommodate this well within the maint window.Once this is done, I can run the stats collections, which doesnt cause that much locking/blocking(just ignoring the CPU use at this time,which I am okay).
    – abi ram
    Jan 4, 2022 at 20:28
  • 2
    How often do you rebuild indexes? What is your maintenance window like? Are you sure you really need to rebuild indexes? If you stopped rebuilding indexes, would it actually affect your performance?
    – AMtwo
    Jan 4, 2022 at 21:37
  • @abiram Whether you do all the index rebuilds first and then stats maintenance second, or do them together such as Ola's script does, you'll experience the same amount of locking either way. So it doesn't matter. But to AMtwo's point, you likely don't need to be rebuilding your indexes anyway, and could try just reorganizing them instead, though I've found little need for either in most cases personally. You can read up more on the two index maintenance options in this Kendra Little article.
    – J.D.
    Jan 5, 2022 at 3:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.