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.