I have a sql script that I use to reorganize indexes with more than 5%
fragmentation and rebuild indexes (ONLINE OFF) with more than 30%
fragmentation. On running this, I found that, the script was working fine but
every iteration updated about 550 indexes. After a run, if I queried the
dynamic management view, it still gave back indexes which were fragmented.

My understanding is that after I do a reorg/rebuild, the entry should
disappear from sys.dm_db_index_physical_stats, if the alter table succeeds.

Question -- what can I do as a DBA, after the script runs to make sure that
the management view gives back updated information and not state information?

My script --

BEGIN

     SELECT database_id,object_id,index_id,avg_fragmentation_in_percent

     INTO #INDEX_STATS_TEMP

     FROM sys.dm_db_index_physical_stats(DB_ID(N'DB_NAME'),NULL, NULL,
NULL, NULL)

     WHERE avg_fragmentation_in_percent >= 5

     AND index_type_desc != 'HEAP'





     -- DECLARE LOCAL VARIABLES FOR THE CURSOR

     DECLARE @dbID int,

           @tableID int,

           @indexID int,

           @frag_percent float,

           @index_name varchar(100),

           @table_name varchar(100),

           @sql varchar(1000)



     --DEFINE THE CURSOR

     DECLARE FRAG_CURSOR CURSOR

     FOR SELECT
TEMP.database_id,TEMP.object_id,TEMP.index_id,round(TEMP.avg_fragmentation_in_percent,1,2),S.NAME

     FROM  #INDEX_STATS_TEMP TEMP,DB_NAME.SYS.INDEXES S

     WHERE TEMP.object_id = S.OBJECT_ID AND TEMP.index_id = S.INDEX_ID

     

     OPEN FRAG_CURSOR

     FETCH NEXT FROM FRAG_CURSOR INTO
@dbID,@tableID,@indexID,@frag_percent,@index_name



     WHILE (@@FETCH_STATUS = 0)

     BEGIN

           print @tableID

           SELECT @table_name = name from DB_NAME.sys.objects where
object_id = @tableID and type = 'U'

           IF (@frag_percent <=30)

           BEGIN

                 USE DB_NAME

                 SET @SQL= 'ALTER INDEX '+@index_name+' ON '+@table_name+'
REORGANIZE'

                 print @SQL

                 exec (@SQL)  

           END

           ELSE IF (@frag_percent > 30)

           BEGIN

                 USE DB_NAME

                 SET @SQL= 'ALTER INDEX '+@index_name+' ON '+@table_name+'
REBUILD WITH (ONLINE = OFF)'

                 print @SQL

                 exec (@SQL)

           END

           SET @SQL = NULL

     FETCH NEXT FROM FRAG_CURSOR INTO
@dbID,@tableID,@indexID,@frag_percent,@index_name

     END

     CLOSE FRAG_CURSOR

     DEALLOCATE FRAG_CURSOR

     DROP TABLE #INDEX_STATS_TEMP

END

Regards

Jaideep