SQL Server: No Fragmentation in HEAP from INSERT / UPDATE

SQL Server: No Fragmentation in HEAP from INSERT / UPDATE


Introduction

HEAP won't be "fragmented" in two cases:
- only INSERTS may occur
- UPDATES will occur in the attributes with fixed data length (e.g. char)

 The following example demonstrates the above mentioned situations:


Creating Scenario For The Test


First a simple HEAP will be created:

USE tempdb;
GO
   
CREATE TABLE dbo.heap
(
 Id      int         NOT NULL,
 col1    char(200)   NOT NULL
);
   
SET NOCOUNT ON
GO
   
DECLARE @i int = 1
WHILE @i <= 80
BEGIN
INSERT INTO dbo.heap
(id, col1)
SELECT  @i,'value: ' + CAST(@i AS varchar(5))    
 SET @i += 1
END
GO

After 80 records have been inserted the most interesting issue is the location of the single rows. The following query displays all information by using sys.fn_PhysLocFormatter.

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS PageSlot, * FROM dbo.heap WHERE Id = 34;
  GO

 

The result of the pic (see link) shows in the first attribute [PageSlot] the information about the fileId:PageId:SlotId of the physical location of the record.


Performing The test

Now let's run an update on the row with the [Id] = 34 and check the physical location again:

UPDATE  dbo.heap
SET     col1 = 'Uwe Ricken'
WHERE   Id = 34;
   
-- what has happened to the physical location?
SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS PageSlot, * FROM dbo.heap WHERE Id = 34

 The result won't change because the updated information fits completely into the fixed reserved space of 200 bytes. Indeed the situation will change if you delete a record and add the record again with the same values:

-- Delete the record
DELETE  dbo.heap
WHERE   Id = 34;
   
-- and insert it with exact the same values
INSERT INTO dbo.heap
VALUES ('34', 'Uwe Ricken')
   
-- what has happened to the location?
SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS PageSlot, * FROM dbo.heap WHERE Id = 34;
GO

  


Conclusion

As you can see from the script the record with ID = 34 has been dropped and inserted again. Now the result for the physical location has changed to a different location. The explanation for that behaviour is quite simple. While each record has a fixed length SQL Server can locate the entry and run a simple update (no forwarded records will occur!). If you release storage on a page (with a delete) it maybe used again from Microsoft SQL Server if a scan of the PFS (Page Free Space) records "enough" space on the page for the storage of the data. But don't trust on that behaviour. If Microsoft SQL Server detect a better performance to put the data at its very end it will be done.


Reference

This article comes from this forum thread.

Sort by: Published Date | Most Recent | Most Useful
Comments
  • BTW, I applied the same fixes as Carsten but Uwe changed the article at the same time (I got that warning), so I didn't post my changes. So I re-applied my changes and forgot these two typos I fixed in the first edit

  • Uwe, I added your article here (because you implied that you wanted to enter the competition): social.technet.microsoft.com/.../18211.technet-guru-contributions-july-2013.aspx

  • Naomi, Carsten, thank you for your - really much - appreciated support.

    Ed, yes.... This article should be part for the competition :)

    I have one - important - request to you...

    I wasn't able to add the pics as visible in this article.

    May i ask you to add the pics instead if the links ...

    Your assistance is pretty much appreciated! :)

  • Hello Uwe,

    I fixed the images. Instead of linking to the images from the forum, I download them and uploaded them to the Wiki using the Wiki editor. (The first image was linked twice in your article - I fixed this too)

    Best regards,

    Carsten

  • Regarding images, you can probably just paste them into the Design tab if they're already up on the Forums.

    Thanks!

Page 1 of 1 (5 items)