IndexOptimize - Configuration
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]
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'