Issue in reducing index fragmentation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Issue in reducing index fragmentation

15 posts, Page 2 of 2

««1 2

Issue in reducing index fragmentation

Rate Topic

Display Mode

Topic Options

Author

Message

GilaMonster

Posted Wednesday, February 17, 2010 6:20 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:36 AM
Points: 37,675, Visits: 29,926

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

Ryan007

Posted Wednesday, February 17, 2010 6:54 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:43 AM
Points: 1,564, Visits: 1,488

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

Ryan007

Posted Wednesday, February 17, 2010 6:57 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:43 AM
Points: 1,564, Visits: 1,488

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

Ryan007

Posted Wednesday, February 17, 2010 6:59 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 2:43 AM
Points: 1,564, Visits: 1,488

The index storage space is 55KB.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Post #867005

GilaMonster

Posted Wednesday, February 17, 2010 7:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:36 AM
Points: 37,675, Visits: 29,926

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 »

15 posts, Page 2 of 2

««1 2

Permissions