Fragmentation (part 4): what are heaps?

The table scan from hell

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' — Fri, 03 Nov 2006 23:00:20 GMT

Greg Linwood, a fellow SQL Server MVP, has started a series of articles in which he attempts to prove...

re: Fragmentation (part 4): what are heaps?

Gent — Wed, 22 Nov 2006 08:15:50 GMT

Paul - I just wante to make sure I understand correctly your second bullet point on "what is a heap":

"This means that rows will be inserted into the heap anywhere where there is space (don't let this statement confuse you - for a table that has nothing but inserts, records will always be appended to the last allocated page)"

Microsoft kb article 297861 indicates:

"In SQL Server 7.0, and later, SQL Server generally optimizes inserts into a heap with the assumption that saving space is more important than performance. That is the tradeoff you choose to make when you decide to leave a table as a heap. Therefore, an insert into a heap often spends time searching for a location to insert a new row."

Are you saying that if I have a heap which has never had any deletes or updates, SQL Server 7.0 and later do not try to optimize for space usage - they simnply insert the new rows at the last allocated page? Does the same apply if I start from an empty heap and insert millions of rows with a single INSERT/SELECT FROM statement?

re: Fragmentation (part 4): what are heaps?

Paul Randal - MSFT — Mon, 04 Dec 2006 03:20:53 GMT

Hi Gent,

The KB article really only applies when there is free space in the pages that comprise the heap. For a heap that is append-only, with no deletes, the free space will always be in the last allocated page - so there's nothing to optimize - the only place to insert the record is in the last allocated page. The same applies to the insert/select example you give.

Thanks

re: Fragmentation (part 4): what are heaps?

Gent — Wed, 06 Dec 2006 06:53:20 GMT

Paul - thank you for the reply.

If I create a variable length table then, depending on the data inserted on a page, it might happen that there is free space in pages other then the last allocated one. For example:

create table T1(col1 varchar(8000))

insert into T1 select replicate('a', 100)

insert into T1 select replicate('a', 8000)

The above two inserts allocate two pages with free space left on both of them.

I used DBCC PAGE to see where would data go if I ran a third insert (by the way, thank you for the great posting on DBCC PAGE):

insert into T1 select replicate('a', 10)

Although there should be enough free space on the second page, in all my tests SQL Server consistently inserted the data in the first one. I ran into the same behaviour if I changed the second insert (the one that allocates the second page) to insert only 7800 characters:

insert into T1 select replicate('a', 7800)

Again, the third insert (inserting a 10 character row) would consistently use the first page, although there should be more than 200 bytes free on the second one.

I also ran a loop as in the following and watched the FreeSpace Scan/sec counter being high for the duration of the loop (I was running these tests on my local machine with nothing else running on SQL Server):

declare @counter int

set @counter = 1

while (@counter < 10000)

begin

insert into T1 select replicate('a', 1000)

set @counter = @counter + 1

end

I ran my tests both on SQL Server 2000 and SQL Server 2005 with similar results.

All the above seems to indicate that even in the case of 'insert only' heaps, SQL Server still tries to optimize for space and will utilize a page which might not necessarily be the last allocated one. What am I missing?

Thanks again for your time and thank you for the great information and knowledge you are sharing in your posts.

Gent

re: Fragmentation (part 4): what are heaps?

Paul Randal - MSFT — Fri, 08 Dec 2006 05:46:42 GMT

Yes, when the records are randomly sized then it will make use of any free space in earlier pages.

The table scan from hell

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' — Mon, 08 Jan 2007 01:05:45 GMT

Greg Linwood, a fellow SQL Server MVP, has started a series of articles in which he attempts to prove

re: Fragmentation (part 4): what are heaps?

alpha1105omega — Fri, 06 Jul 2007 01:02:00 GMT

Hi, Paul

Very nice blog on those fragmentation series..but where is you next one on the clustered index and non-clustered index?

My another question,

if all the rows from a page are deleted, that page gets deallocated and reclaimed by the database and if all pages are empty due to range deleteion in an extent, the extent will get deallocated and reclaimed by the database. Is that correct statement? Is that the same for the heap? I dont think so as "empty" page(due to deletion) do not reclaimed and deallocated for the heap.

Can you explain in that area in respect to the when a range deletion occurs on the page, what happens to the page?

You can ping me at billkan@microsoft.com

Thank you,

Bill

re: Fragmentation (part 4): what are heaps?

Paul Randal - MSFT — Fri, 06 Jul 2007 01:07:38 GMT

Hi Bill,

I haven't done them yet - I'll probably redo most of the series at the end of the summer.

When pages and extents are deallocated is complicated, and the subject of a whole blog post. I'll get to it...

Thanks

More Pages Reported Than Actually Exist in the Table

Kevin Kline — Fri, 26 Oct 2007 17:42:33 GMT

MVP Hugo Kornelius once reported that he encountered a situation in which it was possible to perform