Heap versus Clustered Table

Hi Friends,

Today we are going to discuss on Heap versus Clustered Table. Many times we as a DBA have given the suggestions that avoid the use of Heap [Table without clustered index] in the database. There are lots of reasons for recommending the Clustered Table [Table with clustered index] rather than Heap. One of them is: As comparison to Clustered Table number of reads will be high for Heap.  The reason for high number of reads is due to forwarded records.

Forwarded record is the record in a heap that has forward pointers to another data location. (This state occurs during an update, when there is not enough room to store the new row in the original location.) Let me show you this practically.

First I would like to show you the number of reads during the use of Clustered Table:

Use Master
GO
--Create a New database
Create Database ClusteredTest
GO
Use ClusteredTest
GO
--Create a new table with clustered index [Here By default Primary key will create a clustered index]
Create Table xtClusteredTest
(
id int identity(1,1) primary key,
name varchar(20),
details varchar(2000) 
)
GO
--Insert the records
Insert into xtClusteredTest values('prince','He is a SQL DBA!')
GO 1000
--Try to check the number of Forwarded records
Select * from sys.dm_db_index_physical_stats(DB_ID('ClusteredTest'),OBJECT_ID('xtClusteredTest'),NULL,NULL,'Detailed')
GO
SET STATISTICS IO ON
GO
Select id,details from xtClusteredTest where name='prince'
GO
SET STATISTICS IO OFF
GO
Update xtClusteredTest SET details='Prince Rastogi is working as SQL Server DBA in INDIA. Prince started his career working on SQL Server since Yukon. Prince is having 5+ years of experience. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. Prince is ITIL certified professional. Prince likes to explore technical things on SQL Server.' Where name='prince'
GO
Select * from sys.dm_db_index_physical_stats(DB_ID('ClusteredTest'),OBJECT_ID('xtClusteredTest'),NULL,NULL,'detailed')
GO
SET STATISTICS IO ON
GO
Select id,details from xtClusteredTest where name='prince'
GO
SET STATISTICS IO OFF
GO

In the below output you can see that there is no Forwarded record count in clustered index. As well as in the message tab we have number of reads for clustered table.

Clustered_Table

Clustered_Table2

   

Now we can see the same operation on Heap Structure:

Use Master
GO
--Create new database
Create Database HeapTest
GO
Use HeapTest
GO
--Create a Heap Structure
Create Table xtHeapTest
(
id int identity(1,1),
name varchar(20),
details varchar(2000) 
)
GO
Insert into xtHeapTest values('prince','He is a SQL DBA!')
GO 1000
--Check for any forwarded records
Select * from sys.dm_db_index_physical_stats(DB_ID('HeapTest'),OBJECT_ID('xtHeapTest'),NULL,NULL,'detailed')
GO
SET STATISTICS IO ON
GO
Select id,details from xtHeapTest where name='prince'
GO
SET STATISTICS IO OFF
GO
Update xtHeapTest SET details='Prince Rastogi is working as SQL Server DBA in INDIA. Prince started his career working on SQL Server since Yukon. Prince is having 4+ years of experience. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. Prince is ITIL certified professional. Prince likes to explore technical things on SQL Server.' Where name='prince'
GO
--Check for any forwarded records
Select * from sys.dm_db_index_physical_stats(DB_ID('HeapTest'),OBJECT_ID('xtHeapTest'),NULL,NULL,'detailed')
GO
SET STATISTICS IO ON
GO
Select id,details from xtHeapTest where name='prince'
GO
SET STATISTICS IO OFF
GO

In the below output you can see that there are Forwarded record count in the Heap. As well as in the message tab we have number of reads for this Heap structure.

HeapStructure1

HeapStructure2

Now if you will compare the IO Stats after the update statement then you will find out the  logical reads are very much higher for Heap with forwarded Records as comparison to Clustered Table.

HAPPY LEARNING!

Regards:

Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.