Can we have page split for a HEAP? How can we track page splits?

The answer to this question is We cannot have page splits in heap.

The forwarding pointer is applicable to only heap having non-clustered indexes. SQL Server actually implemented this as a optimization technique to improve heap update performance.

Example – In heap table Customer, We are updating LastName from ‘K’ to ‘I am Pawan Kumar Khowal’.

Now assume that the modified row size has increased and cannot fit on the page it currently resides on. Here the record is moved to a new page, and a small forwarding record is left at the original location. The forwarding record points to the new location of the record, which is known as a forwarded record or forward pointer. This is performed as a performance optimization technique so that all the nonclustered indexes on the heap do not have to be altered with the new location of the heap record.

This improves the performance of INSERT and UPDATE operations; otherwise this will take considerable time for inserting and updating records on heap.

Query to track page splits for an index.

--

SELECT
IOS.INDEX_ID,
O.NAME AS OBJECT_NAME,
I.NAME AS INDEX_NAME,
IOS.LEAF_ALLOCATION_COUNT AS PAGE_SPLIT_FOR_INDEX,
IOS.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(N'DB_NAME'),NULL,NULL,NULL) IOS
JOIN
SYS.INDEXES I
ON
IOS.INDEX_ID=I.INDEX_ID
AND IOS.OBJECT_ID = I.OBJECT_ID
JOIN
SYS.OBJECTS O
ON
IOS.OBJECT_ID=O.OBJECT_ID
WHERE O.TYPE_DESC='USER_TABLE'

--

I hope you have enjoyed the article. Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com