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

Index Rebuild or Reorganize not working on Clustered index Expand / Collapse
Author
Message
Posted Wednesday, January 29, 2014 11:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
I have one really small table just having 25 rows and in that I have primary column with identity.
when I seen fragmentation(avg_fragmentation_in_percent) on that column it was 50% ,I tried rebuild but no change in fragmentation, than i tried reorganize index but also not worked.

Can anyone suggest me what to do with that column how can i reduce fragmentation. and why is not happening.

P.S : I do have 3 to 4 foreign keys in that table but not index on those columns.


Post #1536179
Posted Wednesday, January 29, 2014 11:54 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 @ 4:42 PM
Points: 41,516, Visits: 34,431
BriPan (1/29/2014)
I tried rebuild but no change in fragmentation, than i tried reorganize index but also not worked.

Can anyone suggest me what to do with that column how can i reduce fragmentation. and why is not happening.


Fragmentation's not dropping because
really small table just having 25 rows
, waaaaay too small for fragmentation to have any effect or rebuilds to do anything. It's probably a 2 page table.



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 #1536182
Posted Thursday, January 30, 2014 12:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
Thanks I got your point..Indeed it is having only 2 page table .

i have other table also they were having more than 1000 rows and having clustered index and page count
3 or max 7 . and they also having same problem.

is it same situation for all those. because some similar tables are having 0 fragmentation. whats the difference.
Post #1536190
Posted Thursday, January 30, 2014 9:50 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:58 AM
Points: 576, Visits: 1,532
Yes its the same problem, the recommendation is usually to ignore fragmentation on indexes with less than 1000 pages, although I find in practice I am not worried on anything slightly over that either.
Post #1536437
Posted Thursday, January 30, 2014 10:43 AM


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 @ 4:42 PM
Points: 41,516, Visits: 34,431
BriPan (1/30/2014)
i have other table also they were having more than 1000 rows and having clustered index and page count
3 or max 7 . and they also having same problem.


Yup.
waaaaay too small for fragmentation to have any effect or rebuilds to do anything.



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 #1536471
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse