I have some index with very high fragmentation, 85-100% and rebuilding does not help
The only clue I have is that it may not work if the data file is fragmented on disk and that could very well be the case. However, data is stored on a high performance SSD SAN. Disk fragmentation doesn't really mean anything.
I have the same problem with multiple index in the same database but the one I am most concerned with has 847653 pages and fillfactor 75%. Most other index have fill factor 90% and many of them has the same issue.
This is the script used
SELECT CASE WHEN MAX(ps.avg_fragmentation_in_percent) > 30 THEN 'ALTER INDEX [' + i.name + '] ON [' + sc.NAME + '].[' + so.NAME + '] REBUILD With (maxdop = 1);' ELSE 'ALTER INDEX [' + i.name + '] ON [' + sc.NAME + '].[' + so.NAME + '] REORGANIZE;' END AS [query], max( ps.avg_fragmentation_in_percent) FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps INNER JOIN sys.indexes i ON ps.OBJECT_ID = i.OBJECT_ID AND ps.index_id = i.index_id AND ps.database_id = DB_ID() AND i.name is not NULL AND ps.avg_fragmentation_in_percent > 5 -- min % to return AND ps.page_count > 1000 -- otherwise table is to smal. INNER JOIN sys.objects so ON i.OBJECT_ID = so.OBJECT_ID INNER JOIN sys.schemas sc ON sc.SCHEMA_ID = so.SCHEMA_ID GROUP BY sc.NAME, so.NAME, ps.index_id, i.name ORDER BY sc.NAME, so.NAME, ps.index_id DESC;
Since the question is closed being too broad, I have edited it back to the original question.