I am hitting database performance issues, so we rebuilt all our indexes as we haven't been doing a great job maintaining them. Rebuild ran for about 20 minutes. Then I found a query to see how my indexes are looking
SELECT t.NAME 'Table name', i.NAME 'Index name', ips.index_type_desc, ips.alloc_unit_type_desc, ips.index_depth, ips.index_level, ips.avg_fragmentation_in_percent, ips.fragment_count, ips.avg_fragment_size_in_pages, ips.page_count, ips.avg_page_space_used_in_percent, ips.record_count, ips.ghost_record_count, ips.Version_ghost_record_count, ips.min_record_size_in_bytes, ips.max_record_size_in_bytes, ips.avg_record_size_in_bytes, ips.forwarded_record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips INNER JOIN sys.tables t ON ips.OBJECT_ID = t.Object_ID INNER JOIN sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id WHERE AVG_FRAGMENTATION_IN_PERCENT > 0.0 ORDER BY AVG_FRAGMENTATION_IN_PERCENT, fragment_count
So most of my indexes have an average percentage fragmentation <1 But I have about 10 that are 85%, and another 10 that are 100%
I am no dba, and understand very little about what I am doing here, but surely this is very bad?
Also worth noting that the 100%'s are all primary keys
Am I in a world of trouble, or is my understanding of the stats incorrect?
note: worth mentioning my database is 24gb, and my drive has 46gb free out of 115gb
EDIT: as requested reindex script below
DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 80 DECLARE TableCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+name+']' AS TableName FROM sys.tables OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN print @TableName SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@sql) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor GO
results of original index fragmentation qry attached as requested results