IndexOptimize - @StatisticsModificationLevel and @MAXDOP parameters

[pasqualeceglie]

Hi Ola, can you provide additional information about @StatisticsModificationLevel parameter? In a datawarehouse environment what is the best practice about statistics?
In addition, @MaxDOP parameter overrides the "MAXDOP" instance level setting?
Thanks for your work.

Pasquale

[olahallengren]

can you provide additional information about @StatisticsModificationLevel parameter?

Let's first look at the documentation.
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html#StatisticsModificationLevel

"Specify a percentage of modified rows for when the statistics should be updated. Statistics will also be updated when the number of modified rows has reached a decreasing, dynamic threshold, SQRT(number of rows * 1000)."

The lower you set this parameter, the more frequent the statistics will be updated (and the longer the job will take). So this is a balance.

As a reference, you can read about the threshold for auto-update-statistics.
https://techcommunity.microsoft.com/t5/sql-server/query-optimizer-additions-in-sql-server-2016/ba-p/384739

You will have to pick a value and then see how it works in your environment.

@maxdop parameter overrides the "MAXDOP" instance level setting?

Yes, it does.

[pasqualeceglie]

@StatisticsModificationLevel: Is that parameter related to the new feature in In SQL Server 2016 called "Sublinear Threshold for Update of Statistics" or not?
In my case (SQL Server 2012) do I need to set TF 2371 to set @StatisticsModificationLevel?

[olahallengren]

This parameter works the same, no matter which compatibility level that you are on. In addition you don't need any trace flags.

I have just used the same formula as Microsoft.