ola hallengren on Azure SQL



  • I'm new to the use of the Ola Hallengren optimize index scripts on Azure SQL, but with out of the box settings I notice that some indices with 80-90% defrag % en > 80.000 page counts are simply not tackled. When I perform a manual rebuild on such an index, then everything works as it should. Caný find any sort of threshold on this behavior.
    The only peculiararity is , that these indices are non-unique, non-clustered ones involving at least 2 fields where one of the field only has empty strings . Could this be the reason?

    In other words , is there a reason why the optimize script would skip rebuilding such an index?



  • there are two options that filter the indexes to be rebuilt/reorganized.

    MinNumberOfPages Set a size, in pages; indexes with fewer number of pages are skipped for index maintenance. The default is 1000 pages. This is based on Microsoft’s recommendation.

    IndexOptimize checks page_count in sys.dm_db_index_physical_stats to determine the size of the index.

    and

    MaxNumberOfPages Set a size, in pages; indexes with greater number of pages are skipped for index maintenance. The default is no limitation.

    IndexOptimize checks page_count in sys.dm_db_index_physical_stats to determine the size of the index.

    Said that, your index is quite big and probably you did not set MaxNumberOfPages.

    try to see the message output (using @Execute = 'N' and @Indexes = 'Db1.Schema1.Tbl1.Idx1' with the index you want to defrag)

    It should give you the index status and action choosed. Compare the output with what is coming out from select * from sys.dm_db_index_physical_stats for the same index.

    Are you using the last version of the Ola scripts? Try downloading the last version.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2