Ghost Records and Trace flag 661

Ghost Records:

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.

DEMO:

Turn on the trace flag

DBCC TRACEON(661,-1)

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
GO 20000

Delete few records from table

Delete from GhostRecordTest where ID%7=0

2857 rows were deleted. Run below script to check the ghost records.

SELECT
db_name(database_id) DatabaseName,
object_name(object_id) TableName,
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N'SQLWorks'), OBJECT_ID(N'GhostRecordTest'), NULL, NULL , 'DETAILED')
GO

Turn off the trace flag

DBCC TRACEOFF(661,-1)

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

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 7+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.