When we talk about fragmentation, is it "Index Fragmentation" always ?

Question

Post reply

When we talk about fragmentation, is it "Index Fragmentation" always ?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 1

Thanks.

I executed the following query on test DB with few records.

SELECT object_name(object_ID) as objectname, index_id, avg_fragmentation_in_percent, avg_page_Space_Used_in_Percent

FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED')

WHERE INDEX_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.

george sibbald SSC Guru Points: 104210 More actions · Answer 2

avg_fragmentation_in_percent = 0 means you have no fragmentation so that would not change after a rebuild.

an avg_page_Space_Used_in_Percent = 0 says the pages are empty.

I would suggest there is not enough data in the table(s) to give a good sample.

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

GilaMonster (4/5/2012)
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.

Wouldn't moved rows due to larger updates on variable length rows also cause more rows to be used unnecessarily?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

GilaMonster (4/5/2012)
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.

I have seen this problem also.

I'm not sure I understand the exact mechanism that causes it, except that it seems to be associated with deletes.

I found a heap table that had 128 rows that was using 15 GB of space, so that would have been almost 2 million pages. Adding a clustered index fixed the problem.

I have a hard time spotting this problem because I never create heap tables so I don't think to look for it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Michael Valentine Jones (4/5/2012)
GilaMonster (4/5/2012)
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.
I have seen this problem also.
I'm not sure I understand the exact mechanism that causes it, except that it seems to be associated with deletes.

That's exactly what it's associated with. Paul Randal wrote an article (TechNet I think) describing it, was written early May last year. If no one's foind a link by morning I'll go hunting.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

I think this is the article

http://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/curious-case-empty-heap-table-139573

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

Eric M Russell SSC Guru Points: 125560 More actions · Answer 7

As a result of updates, heap (nonclustered) tables can accumulate a type of fragmentation called "Forwarded Records". If the updated row incrases in length and SQL Server can't fit it on the same page, then the original row space is marked as unusable, and a pointer is used to refer to the updated record at it's new location on another page. If your rows only contain fixed width columns, or you only perform inserts, then this is not a concern.

If you're dealing with this issue in a heap table, then here is some advice from Paul Randal:

If you run sys.dm_db_index_physical_stats (or my old DBCC SHOWCONTIG) on a heap (a table without a clustered index) and it shows some fragmentation, don't EVER create and drop a clustered index to build a nice, contiguous heap. Do yourself a favor and just create the well-chosen clustered index and leave it there - there's a ton of info out there on choosing a good clustering key

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2930)-fixing-heap-fragmentation.aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho