August 18, 2012 at 12:27 pm
Experts,
Silly question... When we talk about fragmentation it's both Table as well as Index fragmentation right ?
I always see posts about index fragmentation only.. hence this doubt. Can someone clarify pls...
Thanks in advance.
Smith.
August 18, 2012 at 12:29 pm
Depends. If the table has a clustered index, then the clustered index strucure is the actual table, so you'd talk about fragmentation of the clustered index.
If the table doesn't have a clustered index (it's a heap) then it doesn't get logical fragmentation at all. It can still get extent fragmentation, but that's a lot less of a problem in general and much harder to fix.
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, 2012 at 1:16 pm
If the table doesn't have a clustered index (it's a heap) then it doesn't get logical fragmentation at all. It can still get extent fragmentation, but that's a lot less of a problem in general and much harder to fix.
I will try myself and find why logical fragmentation doesn't occur in case of heaps.
... So essentially it's always "Index Fragmentation" in clustered as well as Heaps.
What a clear explanation Gila.. Thanks a ton.
August 18, 2012 at 1:32 pm
Hint: Logical fragmentation is when the logical order of the pages differs from the physical order.
Most of the time when people talk about index fragmentation they're talking about logical fragmentation of indexes, not extent fragmentation (which both indexes and heaps can get).
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, 2012 at 11:38 pm
"When you have a heap, there is no logical ordering and there is no splitting of pages." 🙂
Suppose I have a heap with 10 rows in the page. I randomly deleted 4 records. So can
I say that, when I insert 4 new records into this table SQL server will save those records in the place of those deleted rows ?.
Thanks.
August 19, 2012 at 4:31 am
Yup.
Assuming the inserts will fit onto the pages, SQL should reuse the empty spaces.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply