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 ««12

Trying to understand non-clustered index Fragmentation Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2013 12:29 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 @ 8:31 AM
Points: 40,456, Visits: 36,912
Robert Davis (1/22/2013)
There must simply not be enough contiguous free space for it to rebuild it unfragmented.


I've seen indexes rebuild fragmented before, but never this bad.



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 #1410222
Posted Tuesday, January 22, 2013 12:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:25 AM
Points: 82, Visits: 301
There was 10gb free in the data file. I was thinking in that direction myself.

219k pages @ 8k/page = 1,752,000 kb = 1.7gb... I just added a few gb more and will try the rebuild again and see what gives...

thanks!
Post #1410225
Posted Tuesday, January 22, 2013 12:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:25 AM
Points: 82, Visits: 301
Doh, there we go... attachments!

scroll bar is my friend


  Post Attachments 
Fragmentation.jpg (16 views, 38.15 KB)
Post #1410227
Posted Tuesday, January 22, 2013 12:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:25 AM
Points: 82, Visits: 301
I added 20gb to the data file, and immediately ran the rebuild.

same, 94.1932% avg_fragmentation_in_percent...

might this have anything to do with the datatypes of the index columns?

also, my first post had an inaccuracy in it. the non-clustered index is based on TWO big-ints and a datetime, not one...

Post #1410232
Posted Tuesday, January 22, 2013 12:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 9, 2014 12:08 PM
Points: 1,618, Visits: 1,553
GilaMonster (1/22/2013)
I've seen indexes rebuild fragmented before, but never this bad.


Same here.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1410233
Posted Tuesday, January 22, 2013 1:03 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 @ 8:31 AM
Points: 40,456, Visits: 36,912
LAW1143 (1/22/2013)
might this have anything to do with the datatypes of the index columns?


No. Nothing strange about bigint columns.

Just checking... You are rebuilding the same index that you're checking (same table, same database, same server)? Not trying to be insulting, but I've seen many times people working on different servers without realising.

Can you post the table and index definitions?



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 #1410237
Posted Tuesday, January 22, 2013 1:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:25 AM
Points: 82, Visits: 301
The highlighted index is the one we're dealing with...

and I'm not above making stupid mistakes... in fact, I usually dont recognize them as such untill I post here or ask someone else to explain something that doesnt make sense...

I just double (er, triple) checked, and im looking at the right server, db, table, index statistics as the one im rebuilding...

thanks again for your time and help! :)





  Post Attachments 
tableDef.jpg (13 views, 129.23 KB)
Post #1410257
Posted Tuesday, January 22, 2013 2:39 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:25 AM
Points: 82, Visits: 301
Final update of the day from me... my brain hurts and im going home...

I just did a Alter Index ALL on this table to see what came of it...

Index_id 11 is my problem child:

index_id avg_fragmentation_in_percent
1 0.179520838203473
2 0.532145960034752
4 0.518418036320809
11 93.7920077651411
16 0.89879248101581

Post #1410269
Posted Wednesday, January 23, 2013 7:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:25 AM
Points: 82, Visits: 301
I did a quick comparison against a few other databases (of varying sizes) with the same table/index definitions and found similar results..

short term, I'll just exclude this from the automated indexing routine i am working up, but I'd still like to understand why this index is so fragmented, even immediately after a rebuild.

thanks again for everyone's help and time!: )


Post #1410574
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse