Determine fragmentation of HEAP table

Question

Determine fragmentation of HEAP table

Wilfred van Dijk

SSCrazy Eights

Points: 9003
More actions
May 26, 2008 at 4:24 am

#190996

Hi,
AFAIK, A heap table can be fragmented because of page splits, which results in forward lookups.
Is there a way to calculate this fragmentation?
Wilfred
The best things in life are the simple things

Viewing 12 posts - 1 through 11 (of 11 total)

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

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

As per books online explanation about DBCC SHOWCONTIG heaps fragmentation is never reported. Also, heaps can never be defragged. Only one option is thr to defrag them:

Drop and recreate the table and load the previous data(before dropping the original table) back into it.

Manu

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

It's much simpler to add a clustered index... doesn't have to be unique, you know? 😉

--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)

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

Or you can convert an existing non-clustered index to clustered using the "DROP_EXISTING" option. Note that you cannot convert a clustered index to a non-clustered index.

SQL = Scarcely Qualifies as a Language

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 4

I know this can be fixed by a clustered index, but I want to see what the situation is before I add this index (I'm struggeling with application administrators for adding clustered indexes)

But I found the answer: it's a column in sys.dm_db_index_physical_stats :

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED')

where index_type_desc = 'HEAP'

and forwarded_record_count > 0

Wilfred
The best things in life are the simple things

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Nice... thanks for that tip!

--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)

Anurag-476894 SSC Rookie Points: 36 More actions · Answer 6

First you can create cluster index .

and run the appropriate operation(Like index defragmentation)

after that You can drop the Cluster index.

Anurag

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

Wilfred van Dijk (5/26/2008)
Hi,
AFAIK, A heap table can be fragmented because of page splits, which results in forward lookups.
Is there a way to calculate this fragmentation?

Heaps don't suffer page splits. Page splits occur when a row must be inserted onto a page (because of index key order) and it won't fit, or if a row on a page increases in size and will no longer fit.

In both those cases, with an index (clustered or otherwise), the page is split, half the rows moved to a new page and the new page linked into the index chain.

With a heap, a row never has to be inserted into a specific page, because the order doesn't matter. Rather rows are inserted into any page that has space. If a row grows and no longer fits onto the page, then in a heap, that row alone is moved to a page that does have space, and a forwarding pointer is left in its place pointing to where the row was moved to.

Forwarding pointers are a problem, because they have to be honoured (and followed) whenever they are encountered. So, if a heap has 500 pages and there is a single forwarding pointer on each page, all pointing to the 500th page of that heap, that page has to be read each time a forwarding pointer is encountered.

One of the signs of large numbers of forwarding pointers is when logical reads for a read of the entire table increase steadily, with no change to the number of rows.

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

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 8

Thanks for your input. Thus: forwarding pointers can only occur on heaps, while page splits only occur on indexes?

Wilfred
The best things in life are the simple things

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

Wilfred van Dijk (9/23/2008)
Thanks for your input. Thus: forwarding pointers can only occur on heaps, while page splits only occur on indexes?

Pretty much.

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

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 10

Are forward pointers always limited to the same physical file, or can you point to a location in another file (within the same filegroup)?

Wilfred
The best things in life are the simple things

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

As far as I'm aware, it can be anywhere in the same filegroup.

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