Not lowering avg fragmentation I have a script which iterates through all database and underlying tables. With this script i want to rebuild/reorganize the indexes and i am testing this on AdventureWorks2017 database. So after executing the script, lot of indexes have the same results (before 50%, after 50%) and i don't know why? Can someone explain to me why indexes are not fragmented or the script is not good? Thanks

WHILE @@FETCH_STATUS = 0       
BEGIN    
-- &gt; 30 rebuils
IF @avg_fragmentation_percent &gt; 30      
BEGIN       
     -- &gt; 30 rebuils
     SET @SQLString = 'USE  ' +'['+ @Database_name +']'+ CHAR (13 ) +            
     'ALTER INDEX ' + LTRIM(RTRIM(@IndexName)) + ' ON '+LTRIM(RTRIM(@SchemaName)) +'.' + LTRIM(RTRIM(@TableName)) + ' REBUILD PARTITION = ALL'    
     PRINT @SQLString        
     EXEC (@SQLString)  
END       
ELSE  
BEGIN      
 --&lt;30 reorganize
       SET @SQLString = 'USE  ' +'['+ @Database_name +']'+ CHAR (13 ) +            
      'ALTER INDEX ' + LTRIM(RTRIM(@IndexName))+ ' ON '+LTRIM(RTRIM(@SchemaName)) +'.'+ LTRIM(RTRIM(@TableName)) + ' REORGANIZE  PARTITION = ALL'    
       PRINT @SQLString            
       EXEC (@SQLString)  

       SET @SQLString = 'USE  ' +'['+ @Database_name  +']'+ Char (13 ) +     
       'UPDATE STATISTICS  ' +LTRIM(RTRIM(@SchemaName)) +'.'+ Ltrim(Rtrim(@TableName)) + ' WITH FULLSCAN'       
       PRINT @SQLString    
       EXEC (@SQLString)       
   END
FETCH NEXT FROM dbmaint INTO @TableName, @Database_name, @IndexName, @IndexID,@avg_fragmentation_percent ,@SchemaName       
END
I am not sure from where you have got this script however it seems you are trying to rebuild indexes if the fragmentation level is more than 30 and reorganize if the same is less than 30. Your script is actually command to issue reorg and rebuild statement based on  other part of code which is not copied here.

You may try below piece of code for inquiring the fragmentation level within a given database:

1st Query:

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
and indexstats.page_count &gt;=1000 and
dbindexes.name is not null
ORDER BY indexstats.avg_fragmentation_in_percent desc


2nd Query:

DECLARE @DatabaseID int

SET @DatabaseID = DB_ID()

SELECT DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count &gt;= 1000


Kindly note that, page count dictates how big is the table and contains how many pages. For performing reorg or rebuild, you may check wonderful script from Mr. Ola Hallengren and use them for your maintenance job, here also you can customize the fragmentation level on which you want to perform reorg or rebuild, it also has option of offline and online, you may read more details at this link.

https://ola.hallengren.com/

You may also check the best practice and possible impacts on website of Mr. Brent Ozar and can be referred as below:

https://www.brentozar.com/archive/2017/12/index-maintenance-madness/

If fragmentation is not showing showing any sign of improvement, that could mean its a very small table having page count much lesser than 1000. Standard practice is to consider only those tables which is at least 1000 pages or more.

Kindly refer below links explaining same:

Why is my database still fragmented after I rebuilt and reindexed everything?

SQL Server 2012, rebuild not lowering avg fragmentation

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c419f809-c4c6-4c7f-9f36-c9a56d21d37a/index-still-fragmented-after-rebuild?forum=sqldatabaseengine

In case, you have bigger tables however doesn't have clustered index meaning its a heap then you should go for table rebuild with below command:

Alter table SchemaName.TableName rebuild


You can read more details about that on this link.

The most important aspect is to see "How to detect whether index fragmentation affects SQL Server performance". please check more details about that here.

Hope above helps.