Statistics Update being mis-triggered by OlaHallenGren

• Tushar Kanti

  Ten Centuries

  Points: 1145

  More actions

  September 12, 2024 at 3:25 am

  #4456799

  Hi Guys,

  I have a very problem with Azure SQL DB taking more time to update stats than index rebuild. I am trying to filter by using on update stats if modifications are 40% but I think it getting triggered even if the modifications are less than 1%. I am taking about column stats here not index stats. And I am using Ola Hallengren's scripts and help is appreciated.

  

   

  Here are the parameters

  EXECUTE dbo.IndexOptimize

  @FragmentationLow = NULL,

  @FragmentationMedium = NULL,

  @FragmentationHigh = NULL,

  @UpdateStatistics = 'ALL',

  --@OnlyModifiedStatistics = NULL,

  @StatisticsModificationLevel = 40,

  @StatisticsSample = 100,

  @StatisticsResample = 'N',

  @PartitionLevel = 'Y',

  @MSShippedObjects = 'N',

  @Indexes = NULL,

  @TimeLimit = NULL,

  @Delay = NULL,

  @WaitAtLowPriorityMaxDuration = NULL,

  @WaitAtLowPriorityAbortAfterWait = NULL,

  @Resumable = 'N',

  @AvailabilityGroups = NULL,

  @LockTimeout = NULL,

  @LockMessageSeverity = 16,

  @StringDelimiter = ',',

  @DatabaseOrder = NULL,

  @DatabasesInParallel = 'N',

  @LogToTable = 'Y',

  @Execute = 'Y'

  Thanks,

  Tushar

• Mr. Brian Gale

  SSC-Insane

  Points: 24800

  More actions

  September 12, 2024 at 2:26 pm

  #4456991

  Thinking this MAY be working as designed. As per the documentation for the parameter 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).

  Based on your table, if the number of rows modified is greater than 356109 (which yours is), it will update statistics.

  link - https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

  This is working as designed.

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.