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

Issue in reducing index fragmentation Expand / Collapse
Author
Message
Posted Wednesday, February 17, 2010 6:20 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: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
234 MB with only 400 rows? Sure it's not 234 kb? How many pages does the index (not the table) have?


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 #866955
Posted Wednesday, February 17, 2010 6:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, February 13, 2014 8:13 AM
Points: 1,588, Visits: 1,556
Ok. But can you explain me why the avg_percent_in_fragmentation is not getting reduced for this scenario. For the same column if I create a clustered index in stead of non clustered index, I dont find any fragmentation.

Ryan
//All our dreams can come true, if we have the courage to pursue them//
Post #866997
Posted Wednesday, February 17, 2010 6:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, February 13, 2014 8:13 AM
Points: 1,588, Visits: 1,556
Hi Gail,
Sorry. It is 234KB. I typed wrongly.


Ryan
//All our dreams can come true, if we have the courage to pursue them//
Post #867001
Posted Wednesday, February 17, 2010 6:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, February 13, 2014 8:13 AM
Points: 1,588, Visits: 1,556
The index storage space is 55KB.

Ryan
//All our dreams can come true, if we have the courage to pursue them//
Post #867005
Posted Wednesday, February 17, 2010 7:02 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: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
Your index is less than 1 extent (8 pages) in size. There's no point in rebuilding that and, considering the way SQL allocates pages for very very small indexes, rebuilding it won't change the fragmentation anyway.


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

Add to briefcase ««12

Permissions Expand / Collapse