Ghost Records in SQL Server

SQL Server support ghost records concept, ghost records are nothing but are deleted records from leaf node of Clustered/Non clustered index or versioned heap page of table which are just marked as deleted in page header logically but are physically available.
SQL Server supports this concept for faster delete/update operation to get optimum performance.
these logically deleted records (Ghost Records) are purged later using Ghost Records cleanup task which runs in every 5-10 seconds based on SQL Server versions.

We can forcefully clean/purge these ghost records using below DBCC command.
DBCC FORCEGHOSTCLEANUP;


To retain ghost records SQL Server have trace flag 661, this trace flag just disable system defined Ghost Records cleanup task which execute in ever 5-10 seconds.
DBCC TRACEON (661,-1) /* will disable Ghost records clean process */
DBCC TRACEOFF (661,-1) /*will enable Ghost records clean process */

We should always make sure this trace flag is used properly otherwise there will be quite possibility SQL server use a space which is not really required as these deleted records are physically available which are just marked as deleted logically or we can say GHOST RECORDS.

Examples:
/* DROP TABLE GhostRecords */
CREATE TABLE GhostRecords
(GhostId INT Identity(1,1),GhostName VARCHAR(100))

INSERT INTO GhostRecords(GhostName)
VALUES( 'GhostReocrd ' + CAST(ISNULL(IDENT_CURRENT('GhostRecords') ,1) AS VARCHAR))
GO 100

/*Records with Heap Table according to column Index_Type_Desc*/
SELECT 'QUERY1: Heap Table/without cluster Index' TableDetails , Object_name(object_id) AS NAME,record_count, ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

/*Create Clustered & Non Clustered Indexes*/
CREATE CLUSTERED INDEX ClsIdxGhostRecordsId ON GhostRecords(GhostId)
CREATE NONCLUSTERED INDEX NonClsIdxGhostRecordsName ON GhostRecords(GhostName)

/*Records with out Heap Table according to column Index_Type_Desc*/
SELECT 'QUERY2: Without Heap Table/Clustered Table' TableDetails , Object_name(object_id) AS NAME,record_count, ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DBCC TRACEON(3604,-1)
DBCC IND(Ghostrecords,'GhostRecords',1)

DBCC PAGE(Ghostrecords,1,314,3) /* m_ghostRecCnt = 0 This is Data Page*/
DBCC PAGE(Ghostrecords,1,315,3) /* m_ghostRecCnt = 0 This is IAM Page*/

/* Delete 10 Records from table */
DELETE FROM GhostRecords
WHERE GhostId<=10 /*Not >*/

SELECT 'QUERY3: 10 Records deleted, Ghost Cleanup Process Enabled' TableDetails , Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

/*Disable Ghost Records Cleanup Task*/
DBCC TRACEON(661,-1)

DELETE FROM GhostRecords
WHERE GhostId<=20 /*Not >*/

SELECT 'QUERY4: 20 Records deleted, Ghost Cleanup Process Disabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DBCC PAGE(Ghostrecords,1,314,3) /*m_ghostRecCnt = 10 This is Data Page*/
DBCC PAGE(Ghostrecords,1,315,3) /*m_ghostRecCnt = 0 This is IAM Page*/

/* Enable Ghost Records Cleanup Task */
DBCC TRACEOFF(661,-1)

DBCC PAGE(Ghostrecords,1,314,3) /*m_ghostRecCnt = 0 This is Data Page*/
DBCC PAGE(Ghostrecords,1,315,3) /*m_ghostRecCnt = 0 This is IAM Page*/

SELECT 'QUERY5: Ghost Cleanup Process Enabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DELETE FROM GhostRecords
WHERE GhostId<=30 /*Not >*/

SELECT 'QUERY6: 30 Records deleted, Ghost Cleanup Process Enabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

/*Disable Ghost Record Cleanup Task*/
DBCC TRACEON(661,-1)

DELETE FROM GhostRecords
WHERE GhostId<=40 /*Not >*/


SELECT 'QUERY7: 40 Records deleted, Ghost Cleanup Process Disabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DBCC PAGE(Ghostrecords,1,314,3) /*m_ghostRecCnt = 0 This is Data Page*/
DBCC PAGE(Ghostrecords,1,315,3) /*m_ghostRecCnt = 0 This is IAM Page*/

/*Forcefully Start Ghost Cleanup Task*/
DBCC ForceGhostCleanup;

SELECT 'QUERY8: 40 Records deleted, Forcefully Ghost Cleanup Process Enabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DELETE FROM GhostRecords
WHERE GhostId<=50 /*Not >*/

SELECT 'QUERY9: 50 Records deleted, Ghost Cleanup Process Disabled' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'


/* ALTER INDEX ALL ON GhostRecords REBUILD */
ALTER INDEX ALL ON GhostRecords REORGANIZE

SELECT 'QUERY10: 50 Records deleted, Index Reorganized/Rebuild, Ghost Record Removed' TableDetails, Object_name(object_id) AS NAME,record_count,ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

DELETE FROM GhostRecords
WHERE GhostId<=60 /*Not >*/

SELECT 'QUERY11: 60 Records deleted, Ghost Cleanup Process Disabled' TableDetails, Object_name(object_id) AS NAME,record_count, ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

/*Enable Ghost Cleanup Task*/

DBCC TRACEOFF(661,-1)

SELECT 'QUERY12: 60 Records deleted, Ghost Cleanup Process Enabled' TableDetails, Object_name(object_id) AS NAME,record_count, ghost_record_count,
version_ghost_record_count, index_type_desc, alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats (Db_id(N'GhostRecords'), NULL, NULL, NULL,'DETAILED')
WHERE index_level = 0
AND Object_name(object_id) = 'GhostRecords'

In Below Queries Result watch TableDetails & Ghost record Count