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