Ghost Records, Background Cleanup, Checkpoints, Recovery, and DBCC PAGE/IND Concepts

Learning outcomes

After a large ServiceHub delete, pages can temporarily contain rows that are logically gone but not yet physically removed. SQL Server commonly marks those rows as ghost records and lets a background cleanup task reclaim them later when safe. At the same time, dirty pages are written by checkpoint/lazy-writer mechanisms and crash recovery uses the log to restore consistency. This final lesson connects those background mechanisms while drawing a hard boundary around low-level page inspection.

1. Ghosting makes DELETE cheaper, then cleanup catches up

For many rowstore deletes, SQL Server marks a leaf row as deleted instead of immediately reorganizing the physical page. A background ghost cleanup process later removes rows that are no longer required. Ghosts also interact with row-versioning requirements: older versions cannot be discarded while an active versioned transaction can still need them. The cleanup process is asynchronous, so a lab cannot promise “you will see exactly N ghost rows for 10 seconds.”

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab08') IS NULL EXEC(N'CREATE SCHEMA lab08 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab08.GhostProbe;CREATE TABLE lab08.GhostProbe(  id int NOT NULL,  payload char(500) NOT NULL,  CONSTRAINT PK_GhostProbe PRIMARY KEY CLUSTERED(id));;WITH n AS( SELECT TOP (12000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects a CROSS JOIN sys.all_objects b)INSERT lab08.GhostProbe(id,payload)SELECT n,REPLICATE('g',500) FROM n;DELETE lab08.GhostProbe WHERE id%2=0;GOSELECT ghost_record_count,version_ghost_record_count,page_count,       avg_page_space_used_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'lab08.GhostProbe'),1,NULL,'DETAILED')WHERE index_level=0;GO

If cleanup runs quickly, the ghost count might already be low or zero. That is a correct observation, not a failed lab. The supported takeaway is that the count is transient operational evidence.

2. Operational counters add context, but they are not history

sys.dm_db_index_operational_stats includes leaf_ghost_count, a cumulative counter of leaf rows marked as deleted but not yet removed (with caveats for version-retained rows). Like many DMVs, the counters can reset and should be captured with timestamps and instance context.

SELECT leaf_delete_count,leaf_ghost_count,range_scan_count,       leaf_allocation_count,leaf_page_merge_countFROM sys.dm_db_index_operational_stats (DB_ID(),OBJECT_ID(N'lab08.GhostProbe'),1,NULL);GOSELECT SUM(ghost_record_count) AS sampled_ghost_recordsFROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'lab08.GhostProbe'),1,NULL,'SAMPLED');GO

3. Checkpoint is not the lazy writer

Both processes can write dirty pages, but for different reasons. The lazy writer helps maintain free buffers by evicting infrequently used pages; dirty pages must be written before reuse. A checkpoint is database-focused: it writes dirty pages so recovery has a more recent known point and less redo work after a crash. SQL Server also has eager writing for certain minimally logged operations. None of these means every COMMIT writes every modified data page immediately.

SELECT name,target_recovery_time_in_seconds,       is_accelerated_database_recovery_onFROM sys.databasesWHERE name=DB_NAME();GOCHECKPOINT;GOSELECT total_log_size_in_bytes/1048576.0 AS total_log_mb,       used_log_space_in_percentFROM sys.dm_db_log_space_usage;GO

A manual checkpoint is appropriate for this disposable demonstration, not a routine “performance fix.” Automatic/indirect checkpoints and recovery-target settings should be governed at database scope.

4. Crash recovery: analysis, redo, undo — with ADR nuance

After an unexpected stop, SQL Server must restore transactional consistency. The classic mental model is analysis, redo, and undo. Redo reapplies logged work that must be reflected in data pages; undo reverses incomplete transactions. Accelerated Database Recovery (ADR) keeps a Persistent Version Store and a secondary log stream (SLOG), which changes how redo/undo work and can make rollback/recovery much faster. Therefore, do not hard-code a single “recovery scans from the oldest transaction and undoes everything from the log” story for all modern configurations.

SELECT SERVERPROPERTY('ProductVersion') AS engine_build,       SERVERPROPERTY('Edition') AS edition;SELECT name,compatibility_level,recovery_model_desc,       target_recovery_time_in_seconds,       is_accelerated_database_recovery_onFROM sys.databasesWHERE name=DB_NAME();GO

5. Page inspection: supported first, DBCC internals second

For normal operations, use supported catalog/DMV evidence. SQL Server 2019+ provides sys.dm_db_page_info, and Microsoft says it replaces the need for DBCC PAGE in most page-header cases. It does not return the page body. When an expert corruption/internals investigation truly needs the raw body, DBCC PAGE and DBCC IND are historically used low-level techniques, but they are not a stable application/monitoring contract and should stay out of ordinary automation.

-- Supported pattern: map an active request's page_resource to a page header.SELECT r.session_id,r.wait_type,r.wait_resource,       p.file_id,p.page_id,p.page_type_desc,       OBJECT_SCHEMA_NAME(p.object_id,p.database_id) AS schema_name,       OBJECT_NAME(p.object_id,p.database_id) AS object_name,       p.index_idFROM sys.dm_exec_requests AS rCROSS APPLY sys.fn_PageResCracker(r.page_resource) AS cCROSS APPLY sys.dm_db_page_info(c.db_id,c.file_id,c.page_id,'LIMITED') AS pWHERE r.page_resource IS NOT NULL;GO

This query is opportunistic: it returns rows only when active requests expose a page resource. It is an example of supported evidence composition, not a promise that every wait maps to a page.

-- ADVANCED / INTERNALS-ONLY LAB. Run only against disposable objects.-- DBCC IND (ServiceHubLab, 'lab08.GhostProbe', 1);-- DBCC TRACEON(3604);-- DBCC PAGE (ServiceHubLab, <file_id>, <page_id>, 3);-- DBCC TRACEOFF(3604);---- Prefer sys.dm_db_page_info for supported page-header diagnostics.GO

The commands are intentionally commented. A learner can study their role without making unsupported internals mandatory. Never parse DBCC PAGE output into production code or assume record-layout details remain compatible across builds.

6. Chapter checkpoint, cleanup, and bridge

Storage-engine knowledge becomes valuable when it improves a decision: page/allocation evidence explains file growth; heap forwarding explains extra lookup hops; B+ tree mechanics explain row locators and splits; log evidence explains reuse waits; ghost/checkpoint/recovery mechanisms explain asynchronous background state. Chapter 09 builds on this foundation to engineer an index portfolio rather than treating individual indexes as isolated objects.

DROP TABLE IF EXISTS lab08.GhostProbe;-- If no Chapter 08 objects remain, remove the disposable schema.IF NOT EXISTS( SELECT 1 FROM sys.objects WHERE schema_id=SCHEMA_ID(N'lab08'))AND SCHEMA_ID(N'lab08') IS NOT NULL  EXEC(N'DROP SCHEMA lab08;');GO

Authoritative references

• Ghost cleanup process guide — ghost rows and background cleanup
• Write pages in the Database Engine — lazy writer, eager writer and checkpoint
• Database checkpoints — checkpoint semantics
• Accelerated Database Recovery — PVS, SLOG and recovery phases
• sys.dm_db_page_info — supported page-header diagnostics
• SQL Server 2025 build versions — servicing baseline