IndexOptimize - Configuration

Question

We recently switched to Ola Hallengren's maintenance script and automated the deployment of MaintenanceSolution.sql to our customers's SQL Server instance.

We need to set these parameters for the job IndexOptimize - USER_DATABASES:

• @UpdateStatistics = 'ALL'

• @OnlyModifiedStatistics = 'Y'

I see that these parameters are present in MaintenanceSolution.sql:

After I change the values of the above-mentioned parameters in MaintenanceSolution.sql to

@UpdateStatistics nvarchar(max) = 'ALL'

@OnlyModifiedStatistics nvarchar(max) = 'Y'

then execute, I don't see either @UpdateStatistics = 'ALL' or @OnlyModifiedStatistics = 'Y' added in Job Step properties - IndexOptimize - USER_DATABASES:

My questions are:

1. Why don't "statistics" options appear in the job's command?
2. Is it wrong to edit MaintenanceSolution.sql directly for this?
3. Is there a way to add theses parameters to the job using a query?

Answer 1

Why don't "statistics" options appear in the job's command?

Because you updated the default parameter in the procedure call. This means that if you call the procedure without these parameters, E.G.

EXECUTE dbo.IndexOptimize
 @Databases ='USER_DATABASES',
 @LogToTable='Y'

that these default parameters @UpdateStatistics = 'ALL' & @OnlyModifiedStatistics = 'Y' will be used.

It is by design that these job steps are not changed.

Is it wrong to edit MaintenanceSolution.sql directly for this?

Depends on your requirements.

By default no statistics will be updated when calling the procedure without the parameters specified.

If you change these @OnlyModifiedStatistics & @UpdateStatistics parameters in the procedure, all modified statistics will be updated when calling the procedure without these parameters added.

Is there a way to add theses parameters to the job using a query?

Sure, change the jobstep to this:

 EXECUTE dbo.IndexOptimize
 @Databases ='USER_DATABASES',
 @LogToTable='Y',
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y';

---

EDIT

So even if i don't see "@UpdateStatistics = 'ALL'" & "@OnlyModifiedStatistics = 'Y'" in the jobstep and that i have updated it as the default parameters in the MaintenanceSolution.sql ... Parameters will still be used ? –

Sure, executing below procedure with the default NULL parameter for the @UpdateStatistics parameter runs instantly

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL
--,@UpdateStatistics = 'ALL'

Date and time start

Date and time: 2019-08-21 14:49:22

Date and time end:

Date and time: 2019-08-21 14:49:22

No stat updates statements found.

This statement will do no index optimization and no update stats if no default parameters are changed on the procedure itself.

Changing the @UpdateStatistics parameter in the procedure and rerunning it, on a small database

It takes too long on all user databases ;)

@UpdateStatistics nvarchar(max) = 'ALL',

Rerunning the procedure on one small database

Date and time start:

Date and time: 2019-08-21 14:50:16

Date and time end:

Date and time: 2019-08-21 14:50:23

One of the statements in the messages tab:

 UPDATE STATISTICS [Database].[dbo].[test] [UIX_dbo_test_RecordType]

Answer 2

You can just add the parameters to the job, it will just execute the SQL statement. This is the official documentation, any parameters here you can add to the procedure, it also has some examples. Ola hallengren site

For example this is an example that I have running now:

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