I am using Ola Hallengren's scripts to maintain my databases. On a few of them there are indices that don't seem to be benefiting from the maintenance. I know that his scripts have a lower threshold (I believe 1000 pages), below which it will not attempt to rebuild or reorganize.

For one index, in dm_db_index_physical_stats the clustered index shows up on 2 lines. One is PK_DATA, allocation unit type of IN_ROW_DATA, 16 pages, and 93.75% fragmented. The other PK Data, allocation unit type of LOB_DATA, 57200 pages, and 0% fragmented.

Should I expect this index to be reorganized or is it being skipped due to the differing page counts for the different allocation unit types? Or might it be getting reorganized unsuccessfully?

Thanks in advance for any advice you might have.


In Ola Hallengren's scripts he uses the MAX fragmentation and only considers the IN_ROW_DATA allocation unit type. See this excerpt from his scripts:

ALTER PROCEDURE [dbo].[IndexOptimize]
-- ...
-- Is the index fragmented?
-- ...
-- Note his use of the following below:
---- @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent)
---- WHERE alloc_unit_type_desc = ''IN_ROW_DATA''
  SET @CurrentCommand05 = @CurrentCommand05 + 'SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent), @ParamPageCount = SUM(page_count) FROM sys.dm_db_index_physical_stats(@ParamDatabaseID, @ParamObjectID, @ParamIndexID, @ParamPartitionNumber, ''LIMITED'') WHERE alloc_unit_type_desc = ''IN_ROW_DATA'' AND index_level = 0'

Without going further into the exact logic of the scripts it would appear that the index would not be reorganized because the page count is below the minimum of 1000.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.