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.
Thanks
Best Answer
Dont try to reinvent the wheel as there is a cost to reinventing it.
Use Ola's maintenance scripts - they are tested well enough and follow best practice. You can even use LockTimeout in Ola's solution.
I would not worry on the fragmentation levels where the page count is less than 5K.
Do the index maintenance during less activity or maintenance time.