December 5, 2011 at 5:56 pm
I need help on looking at index fragmentation via sys.dm_db_index_physical_stats. The Avg_fragmentation_in_percent is over 90% for index_id 0 and index_type_desc is Heap. How do I defrag it if there is no index to rebuild. How is SQL seeing these fragmentation? Please let me know or is there anything that I am doing wrong. I appreciate your assistance on this. Thank you!
December 5, 2011 at 10:23 pm
Debora (12/5/2011)
I need help on looking at index fragmentation via sys.dm_db_index_physical_stats. The Avg_fragmentation_in_percent is over 90% for index_id 0 and index_type_desc is Heap. How do I defrag it if there is no index to rebuild. How is SQL seeing these fragmentation? Please let me know or is there anything that I am doing wrong. I appreciate your assistance on this. Thank you!
Any table which does not have a clustered index is a heap. A heap is an unordered table. If your table is a small one (say 10-20 rows) you don't need to worry about that. However, if it is a big table & highly used in queries then you must think about identifying the right column(s ) for Primary Key & then go with that.
December 6, 2011 at 1:44 am
Heap in the sense there is no index created on that table.u can't do rubuild or reorganize heaps in SQL server.
December 6, 2011 at 2:07 am
MasterDB (12/6/2011)
Heap in the sense there is no index created on that table.
Heap in the sense that there's no clustered index. There can be nonclustered indexes on a heap, it's still a heap if there's no clustered index.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply