no rebuild

[dirksc]

Hi guys, I have a table with 5 indexes, one of them is fragmented 85 %, one other 96 %, but no rebuild is done when I don't configure @minFragmentation and @rebuildThreshold (so default 5.0 and 30.0 should be used). No configuration of @rebuildThreshold will lead to rebuild. When I set @minFragmentation=80 and @rebuildThreshold=98.0 the both indexes are reorganized. Other parameters I set are the following:,@Exec_Print = 1,@printCmds=1,@scanMode='DETAILED' and @dbScope and @tblName of course in the right way. The both indexes are both "normal" nonclustered indexes. Can someone give me a hint what might be the reason for that?

Thanks in advance

[DiHo78]

Hi @dirksc ,

I do not use Pedros procedure for index maintenance, so I cannot give proper advise. I have just checked (roughly) how the procedure works. So my question: what information regarding your 2 indexes gets listed in the table tbl_AdaptiveIndexDefrag_Analysis_log or _Defrag_Working ? What fragmentation values are listed there?

[dirksc]

Hi, nothing is logged, only if it would be rebuild, in the output from the procedure comes something like '..no need to...'

[pmasl]

Apologies for the delay - Github was not sending updates. It would be useful to get a repro. Can you run with @Exec_Print = 0, @printCmds=0 and see if it gest listed in the table? If you run the below query, what info do you get for indexes? Replace

with the relevant object id for that table - lookup in sys.objects.

SELECT QUOTENAME(DB_NAME(ps.database_id)) AS [dbName], ps.* FROM sys.dm_db_index_physical_stats(DB_ID(),

, NULL, NULL, 'DETAILED') AS ps LEFT JOIN sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, 0) AS os ON ps.database_id = os.database_id AND ps.object_id = os.object_id AND ps.index_id = os.index_id AND ps.partition_number = os.partition_number WHERE avg_fragmentation_in_percent >= 0 AND ps.page_count >= 1 --AND ps.index_level = 1 AND ps.alloc_unit_type_desc = 'IN_ROW_DATA' OPTION (MAXDOP 2);

SELECT QUOTENAME(DB_NAME(ps.database_id)) AS [dbName], ps.* FROM sys.dm_db_index_physical_stats(DB_ID(),

, NULL, NULL, 'LIMITED') AS ps LEFT JOIN sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, 0) AS os ON ps.database_id = os.database_id AND ps.object_id = os.object_id AND ps.index_id = os.index_id AND ps.partition_number = os.partition_number WHERE avg_fragmentation_in_percent >= 0 AND ps.page_count >= 1 AND ps.alloc_unit_type_desc = 'IN_ROW_DATA' OPTION (MAXDOP 2);