April 4, 2012 at 6:46 am
Hi All,
From SQL Server perspective, When we talk about fragmentation, is it "Index Fragmentation" always ? . (........asking a foolish question 🙂 )
Thanks in advance.
Smith.
April 4, 2012 at 10:51 am
When I'm talking with DB people when I hear fragmentation I always think Index Fragmentation because that's what we can control. When I'm with non-DB people I think physical disk fragmentation.
I try to always say index fragmentation though.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2012 at 11:04 am
Physical fragmentation is about files spread all over the disk. Logical fragmentation is about indexes that are out of their natural sequence and possibly spread all over the data file as well.
Both matter. But when you're doing maintenance planning for databases, most people would be talking about index (logical) fragmentation.
Physical fragmentation is usually caused by auto-growth being mismanaged/misconfigured. Logical is caused by just plain regular use of the database and can be mitigated but can't really be prevented.
- 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
April 4, 2012 at 11:08 am
Most people will be talking about Index fragmentation when planning for DB maintenance. I typically try to include the disk fragmentation as well because that can have an impact and needs to be addressed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2012 at 3:47 pm
a heap table can also be fragmented, so thats not 'index' fragmentation
---------------------------------------------------------------------
April 4, 2012 at 9:05 pm
george sibbald (4/4/2012)
a heap table can also be fragmented, so thats not 'index' fragmentation
If you look at it in the regard that a heap is index type of 0, then you could make the argument that the heap is an index 😀
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2012 at 12:23 am
Thanks to all. More clear now.
If you look at it in the regard that a heap is index type of 0, then you could make the argument that the heap is an index 😀
In that case, we can defragment this heap as any other user defined index, right ?
Thanks again.
Smith.
April 5, 2012 at 3:42 am
the ops got you there Jason! Retract your statement or I link to it on the thread 🙂
nope you cannot defragment it like its an index. the heap is the actual data part of the table when no clustered index is defined. To remove fragmentation from it you would either have to add a clustered index and optionally drop it again or bcp the data out and in again. There is no direct equivalent to alter index for a heap.
Neither option is great.
p.s. Jason was joking.
---------------------------------------------------------------------
April 5, 2012 at 6:30 am
Thanks.
I executed the following query on test DB with few records.
SELECTobject_name(object_ID) as objectname, index_id, avg_fragmentation_in_percent, avg_page_Space_Used_in_Percent
FROMsys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED')
WHEREINDEX_ID <> 0
I am getting index_id, avg_fragmentation_in_percent and avg_page_Space_Used_in_Percent values as '0' (zero). I rebuilt the indexes, but stil
lit's shown as '0'. What does a '0' value mean ?
I am aware when to rebuild or reorganise indexes.
Thanks.
April 5, 2012 at 6:30 am
Joy Smith San (4/5/2012)
Thanks to all. More clear now.If you look at it in the regard that a heap is index type of 0, then you could make the argument that the heap is an index 😀
In that case, we can defragment this heap as any other user defined index, right ?
Thanks again.
Smith.
The whole point of a heap vs a clustered index is that fragmentation doesn't matter in a heap. They don't have a defined storage sequence (well, behind the scenes they do, but that's mechanical and applies to anything stored on a sequential system like an HDD or SSD), so they don't "fragment", in terms of logical fragmentation.
- 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
April 5, 2012 at 7:13 am
GSquared (4/5/2012)
The whole point of a heap vs a clustered index is that fragmentation doesn't matter in a heap. They don't have a defined storage sequence (well, behind the scenes they do, but that's mechanical and applies to anything stored on a sequential system like an HDD or SSD), so they don't "fragment", in terms of logical fragmentation.
Same thought here for heap fragmentation.
just a doubt what is the difference in index rebuilding and index reorganize hope it does same activity in different way.
Regards
Durai Nagarajan
April 5, 2012 at 8:00 am
GSquared (4/5/2012)
Joy Smith San (4/5/2012)
Thanks to all. More clear now.If you look at it in the regard that a heap is index type of 0, then you could make the argument that the heap is an index 😀
In that case, we can defragment this heap as any other user defined index, right ?
Thanks again.
Smith.
The whole point of a heap vs a clustered index is that fragmentation doesn't matter in a heap. They don't have a defined storage sequence (well, behind the scenes they do, but that's mechanical and applies to anything stored on a sequential system like an HDD or SSD), so they don't "fragment", in terms of logical fragmentation.
but if they have deletion and update activity they can suffer from page splits and internal fragmentation (empty space) which is physical fragmentation and such a table can be compacted with the IO benefits that would bring. Carefully choosing a clustered index to add to the table can reduce the size of the non-clustered indexes as well.
---------------------------------------------------------------------
April 5, 2012 at 8:36 am
george sibbald (4/5/2012)
GSquared (4/5/2012)
The whole point of a heap vs a clustered index is that fragmentation doesn't matter in a heap. They don't have a defined storage sequence (well, behind the scenes they do, but that's mechanical and applies to anything stored on a sequential system like an HDD or SSD), so they don't "fragment", in terms of logical fragmentation.
but if they have deletion and update activity they can suffer from page splits and internal fragmentation (empty space) which is physical fragmentation and such a table can be compacted with the IO benefits that would bring.
No, they can't. Heaps are never subject to page splits.
A page split occurs when a row must go onto a particular page and there is no space for it. Heaps have no requirement for where rows must go, so if a page doesn't have space for a particular row, that row goes somewhere else.
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
April 5, 2012 at 9:05 am
I stand corrected, I had forgotten about forwarding pointers. what about internal fragmentation though?
where I have added a clustered index to a heap I have achieved a reduction in pages used so there is compaction somewhere, I presume it is all down to fill fill factors and removing pointers and empty space?
there is no guarantee a new extent will be added consecutively so there is still extent fragmentation in a heap.
Its all under the heading of fragmentation going back to the ops original question.
---------------------------------------------------------------------
April 5, 2012 at 9:13 am
george sibbald (4/5/2012)
I stand corrected, I had forgotten about forwarding pointers. what about internal fragmentation though?where I have added a clustered index to a heap I have achieved a reduction in pages used so there is compaction somewhere, I presume it is all down to fill fill factors and removing pointers and empty space?
Deleted rows.
Especially on heaps, there's a limitation around whether pages can be deallocated after they are emptied. I've before now seen a table with 1 row and 30 000 pages.
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 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply