SQL Index Fragmentation?
Posted on 2014-10-27
I have some fragmented indexes in SQL 2008 R2.
I have been reindexing them with:
or command like:
alter index ... on dbo... REBUILD
anyway not all indexes seem to reduce in fragmentation to 0.
So I am curious have I got an unreasonable expectation here?
what should I expect the outcome to be? And why might some
not reduce to 0?
btw here is the SQL I am running to work out fragmentation:
declare @current_time as nvarchar(20)
set @current_time = convert(varchar, getdate(), 3) + ' ' + convert(varchar, getdate(), 24)
SELECT @current_time as Timestamp,
DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],
i.name AS [Index Name], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
AND page_count > 1500
ORDER BY OBJECT_NAME(ps.OBJECT_ID), avg_fragmentation_in_percent DESC OPTION (RECOMPILE);