September 2, 2013 at 2:46 pm
Hello guys,
I've this command:
Select db_name(DB_ID()) as DBName,SS.name as SchemaName,SO.name as TableName, SI.name as Indexname,index_type_desc as IndexType ,avg_fragmentation_in_percent as FragmentationPercentage, (case when avg_fragmentation_in_percent between 10 and 30 then 'Defrag'when avg_fragmentation_in_percent > 30 Then 'Reindex'Else 'Can be Ignored Currently'End) as Recomendation from sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), null, null, null, 'DETAILED') IPS , sys.indexes SI ,sys.objects SO , sys.schemas SS where IPS.index_id=SI.index_id and IPS.object_id=SI.object_id andSI.object_id=SO.object_id and SO.schema_id=SS.schema_id andIPS.index_type_desc in ('NONCLUSTERED INDEX', 'CLUSTERED INDEX') Order by Recomendation desc
and show some rows with FragmentationPercentage 11,1111111 and 15,11111 etc.
i wrote
alter index nameindex on object reorganize, BUT
its continue show me FragmentationPercetange 11,1111111 and 15,11111, that is normally?
is not show me FragmentationPercetange 0 ?
thanks all
September 2, 2013 at 3:15 pm
Neither rebuild nor reorganise is expected to reduce fragmentation to 0. If the index is very small it's unlikely to change the fragmentation at all. No, it's not a bug.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2013 at 4:00 pm
I understood.
So, it's normally i've 11,1111 when i use REORGANIZE so? is not possible my fragmentation is lower?
September 2, 2013 at 4:08 pm
The DMV is correct. If it shows 11.1111%, then that index is 11.1111% fragmented.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2013 at 4:16 pm
All right, but is possible i have low a fragmentation? ex: 5,55555 or 3,3333 using reorganize or similar?
Microsoft recommends:
If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER INDEX REORGANIZE:
yes, i used, but it's continue 11,11111....
it's my doubt.
thank you very much dude!
September 2, 2013 at 4:37 pm
If the DMV shows that the index has 11.111% fragmentation, then the index has 11.111% fragmentation. Not 5%, not 3%, 11.111%. The DMV is accurate.
If the index is small (which it very likely is) the fragmentation is meaningless and should be ignored.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2013 at 4:51 pm
hm, all right.
last doubt dude...
i have this index:
id index_type_desc index_id avg_frag
1CLUSTERED INDEX080
alter index indexname
on table X
rebuild with (online=on)
after that i have avg_frag = 20.
so, i'll consider that 20 is normally because size my index... all right?
September 2, 2013 at 5:05 pm
No idea. Maybe. Possibly not.
Ignore fragmentation on very small indexes. It's meaningless on very small indexes (which are the ones that usually show this kind of behaviour)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply