February 12, 2010 at 11:44 am
I have used this below query to find indexes whose fragmentation is >10% and I got 13 indexes having fragmentation>10%. But when I add one more condition i.e page_count>100, then I got NO results. That means can I ignore the fragmentation for these indexes? because many DBA's recommended that indexes having page_count>100 no need to worry. Even if you defragment them, they will NOT defragment. Is that correct? please advice..
USE SharedServices1_Search_DB
DECLARE @currentDdbId int
SELECT @currentDdbId = DB_ID()
SELECT DISTINCT i.name,
st.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@currentDdbId, NULL, NULL , NULL, 'SAMPLED') st
INNER JOIN sys.indexes AS i
ON st.object_id = i.object_id
WHERE st.avg_fragmentation_in_percent >10
One more thing, when I open the index properties, I can see that the total fragmentation as 0% but from the above query, the fragmentation is 66.6%. I did not understand why this difference is? plz see the attachmnet
Thanks
February 12, 2010 at 12:41 pm
Your DBA is correct, in the sense pages will defrag but will not have much effect. Eg: if you defrag table which has 20000 pages count may reduce the page count to 15000 pages depending on fragmentation level, same way if you defrag a table which has 100 pages may reduce pages count to 90 -95, again depends on the fragmentation level that is why your DBA said to exclude pages less than 100, does it make sense?
EnjoY!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply