With this query I can list index fragmentation:
SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', 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() ORDER BY indexstats.avg_fragmentation_in_percent desc
But sometimes, it's listed like this:
What does this mean? is the table fragmented?
And, while I'm here, I would like to ask another question.
I'm using a job to reorganize indexes. All of them are just being reorganized, because this database is running at full speed 24/7 and I can't use rebuild because of locks.
What is the best way to use rebuild\reorganize in this situation? is Hallengren a better solution in this case? if im not mistaken, if the index can't be locked, it will just reorganize it.