6

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:

enter image description here

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:

enter image description here

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?
6

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]
  • 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 ? – William Aug 21 at 12:27
  • @William That is correct, if they are not specified. I added a small test to the answer :) – Randi Vertongen Aug 21 at 12:52
  • So to conclude, as soon as I modify the default paramters from the script, it is not necessary for me to add them in the jobstep even if i don't see them ? – William Aug 21 at 14:14
  • @William Correct, just remember that every call will execute the proc with these default parameters unless otherwise specified. – Randi Vertongen Aug 21 at 14:42
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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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