"Defragging" an index that wont defrag

Question

"Defragging" an index that wont defrag

KTG

Ten Centuries

Points: 1065
More actions
August 18, 2008 at 8:34 am

#195918

I am trying to rebuild an index that has a LogicalFrag of 67 for example, and after rebuilding it is remaining at 67. The index is a nonclustered (on a table with no clustered index - don't ask it is what I've inherited). I am actually doing many at once, and I can see the process works for many, or in some cases lowers it but not to 0.
So what is plan B? How do I lower the LogicalFrag? Is it because no clustered index is present on the table?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

How many rows are in the table and how wide are they? If the data can fit on only a few pages, you're probably looking at maximum defrag now.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

KTG Ten Centuries Points: 1065 More actions · Answer 2

Yes, actually this particular index is only one page. . . while the heap is 11.

so what does this actually mean then? Can't the data on a single page be fragmented?

GSquared SSC Guru Points: 260824 More actions · Answer 3

I don't believe that defragmenting a nonclustered index on a heap table works.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

KTG (8/18/2008)
Can't the data on a single page be fragmented?

Defragging an index means to put the pages of that index back into logical order. If there's only one page then it is in order, regardless.

I would have thought that the logical fragmentation of a 1 page index would (should) always be zero. Not sure why it's not. Generally fragmentation only starts to matter over about 100 pages.

The order of rows on the page is meaningless and doesn't affect anything.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

The only way to defragment a heap table is to create a clustered index.

You can create a clustered index and then drop it if there is some reason you don’t want a clustered index.

Better would be to create a clustered index and leave it in place, either by converting an existing index to clustered or by creating a new index.

A heap table with a small number of rows can still be a performance problem if there are a large number of pages. This can happen when the table has a large number of inserts and deletes. As an extreme example, I have seen a table that had less than 200 rows using over 4 GB of space.