August 18, 2008 at 8:34 am
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?
August 18, 2008 at 9:17 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 18, 2008 at 9:23 am
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?
August 18, 2008 at 9:24 am
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
August 18, 2008 at 11:00 am
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
August 18, 2008 at 12:27 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply