May 18, 2011 at 12:25 pm
I don't understand the results of my query to check fragmentation;
01HEAPIN_ROW_DATA
281NONCLUSTERED INDEXIN_ROW_DATA
281NONCLUSTERED INDEXIN_ROW_DATA
281NONCLUSTERED INDEXIN_ROW_DATA
I have fragmentation and with no clustered index I don't see how the fragmentation would be corrected unless I alter index on the nonclustered individually. And then what about the heap? Any thoughts?
May 18, 2011 at 12:45 pm
Alter index on each nonclustered index or ALTER INDEX ALL On <table>
Heaps cannot be rebuilt in SQL 2005.
Someone is sure to suggest adding and dropping a clustered index. While that does remove forwarding pointers and should do something about the extent fragmentation, it will rebuild all nonclustered indexes twice. That's a lot of data movement, a lot of log records.
If removing fragmentation is important, consider adding a suitable clustered index to the table.
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
May 18, 2011 at 1:14 pm
GilaMonster (5/18/2011)
Alter index on each nonclustered index or ALTER INDEX ALL On <table>Heaps cannot be rebuilt in SQL 2005.
Someone is sure to suggest adding and dropping a clustered index. While that does remove forwarding pointers and should do something about the extent fragmentation, it will rebuild all nonclustered indexes twice. That's a lot of data movement, a lot of log records.
If removing fragmentation is important, consider adding a suitable clustered index to the table.
How do you rebuild a heap in 2008 or 2008 R2?
May 18, 2011 at 1:24 pm
In 2008+, you can issue ALTER TABLE dbo.TableName REBUILD. However, you are still going to be rebuilding all of the nonclustered indexes automatically.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply