whorsfall
asked on
SQL Index Fragmentation?
Hi,
I have some fragmented indexes in SQL 2008 R2.
I have been reindexing them with:
DBCC DBREINDEX
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_perce nt, fragment_count, page_count
FROM sys.dm_db_index_physical_s tats(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_perce nt DESC OPTION (RECOMPILE);
Thanks,
Ward.
I have some fragmented indexes in SQL 2008 R2.
I have been reindexing them with:
DBCC DBREINDEX
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_perce
FROM sys.dm_db_index_physical_s
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),
Thanks,
Ward.
Your script it's only taking in consideration the page count (1500+) but Microsoft's recommendation is to check for fragmentation level. Here's the MSDN article about it where you'll see that a reindex should be performed when fragmentation level between 5% and 30% and a rebuild should be performed when fragmentation's higher than 30%.
ASKER
Hi,
Thanks for that so I can assume after a rebuild then the fragmentation should be 0?
Plus assuming I get all the tables by changing my query.
And what about the command dbcc dbreindex?
Thanks,
Ward
Thanks for that so I can assume after a rebuild then the fragmentation should be 0?
Plus assuming I get all the tables by changing my query.
And what about the command dbcc dbreindex?
Thanks,
Ward
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Thanks for the clarification that was great :)
Ward
Thanks for the clarification that was great :)
Ward