Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

it's a bug? Expand / Collapse
Author
Message
Posted Monday, September 2, 2013 2:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 10:09 AM
Points: 62, Visits: 434
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
Post #1490690
Posted Monday, September 2, 2013 3:15 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1490691
Posted Monday, September 2, 2013 4:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 10:09 AM
Points: 62, Visits: 434
I understood.

So, it's normally i've 11,1111 when i use REORGANIZE so? is not possible my fragmentation is lower?
Post #1490700
Posted Monday, September 2, 2013 4:08 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
The DMV is correct. If it shows 11.1111%, then that index is 11.1111% fragmented.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1490701
Posted Monday, September 2, 2013 4:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 10:09 AM
Points: 62, Visits: 434
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!
Post #1490704
Posted Monday, September 2, 2013 4:37 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1490708
Posted Monday, September 2, 2013 4:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 10:09 AM
Points: 62, Visits: 434
hm, all right.

last doubt dude...

i have this index:

id index_type_desc index_id avg_frag
1 CLUSTERED INDEX 0 80

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?
Post #1490711
Posted Monday, September 2, 2013 5:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1490713
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse