How to check for SQL ghost records?

09 March,2013 by Jack Vamvas

Question: How can I check for  the SQL ghost records count?

Answer: Use the sys.dm_db_index_physical_stats view and check the ghost_record_count column.

The Ghost record count  is the row count in the index leaf level marked for deletion, but not yet deleted by the database engine

Ghost_record_count is the ghost record count awaiting removal by the ghost cleanup task in the allocation unit.

Version_Ghost_record_count  is the ghost records count sustained by a snapshot isolation transaction in an allocation unit.

 

SELECT 
db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count

FROM sys.dm_db_index_physical_stats(DB_ID(N'db_name'), OBJECT_ID(N'table_name'), NULL, NULL , 'DETAILED');

 

Read More

SQL Index Fragmentation and sys.dm_db_index_physical_stats

SQL Kill connections to a database

SQL Server - Monitoring a Rollback and sys.dm_exec_requests

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to check for SQL ghost records?


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer