Ghost records are the records that just have been deleted. SQL do not directly delete the records from Index pages but it mark these records as deleted to make the deletion faster and rollback much easier. PFS pages keep the track of these records
Ghost Records clean up process:
This is a background process which delete the ghost records physically from Index pages. This process startup in every 5 seconds or 10 second and clean up the ghost records created by committed transactions.
Trace Flag 661:
Scope: Global only
Trace Flag 661 disable the ghost records cleanup process. Ghost records clean up uses lots of memory, CPU and IO in order to perform the clean up so in a system with heavy deletion, the clean up process may cause performance issues and disabling the ghost record clean process in such systems can be a good optimization but system will never reclaim the space created by the delete(There are other ways to reclaim the space like Rebuild or Re-Organize the indexes).
Although disabling the clean up process is not a good option most of the time so it should be used with proper precautions. You may also refer KB 920093 for more information.
Turn on the trace flag
Create a table
CREATE TABLE GhostRecordTest
(ID int Identity)
Create a clustered Index
CREATE CLUSTERED INDEX IDX_GRT_ID ON GhostRecordTest(ID)
Insert few records in the table
INSERT INTO GhostRecordTest DEFAULT VALUES
Delete few records from table
Delete from GhostRecordTest where ID%7=0
2857 rows were deleted. Run below script to check the ghost records.
FROM sys.dm_db_index_physical_stats(DB_ID(N'SQLWorks'), OBJECT_ID(N'GhostRecordTest'), NULL, NULL , 'DETAILED')
Turn off the trace flag
Run the script to check the ghost records again
All the ghost records were cleaned up as soon as trace flag 661 was turned off