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:
- Why don't "statistics" options appear in the job's command?
- Is it wrong to edit MaintenanceSolution.sql directly for this?
- Is there a way to add theses parameters to the job using a query?
Best Answer
Because you updated the default parameter in the procedure call. This means that if you call the procedure without these parameters, E.G.
that these default parameters
@UpdateStatistics = 'ALL'
&@OnlyModifiedStatistics = 'Y'
will be used.It is by design that these job steps are not changed.
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.Sure, change the jobstep to this:
EDIT
Sure, executing below procedure with the default
NULL
parameter for the@UpdateStatistics
parameter runs instantlyDate and time start
Date and time end:
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 databaseIt takes too long on all user databases ;)
Rerunning the procedure on one small database
Date and time start:
Date and time end:
One of the statements in the messages tab: