• opc.three (8/1/2012)


    SQLSACT (7/31/2012)


    pooyan_pdm (7/31/2012)


    To increase the performance of DELETE operation the records are not deleted immediately, instead they are marked as ghosted . A background process called ghost cleanup task later removes the deleted records physically from the index page.you should look for 'leaf_ghost_count' column in the 'sys.dm_db_index_operational_stats' for number of logically deleted records.

    How long after the delete does the Ghost Cleanup happen?

    Thanks

    There is no way to predict when it will be physcially cleaned up. The ghost cleanup thread wakes up every 5 seconds looking for things to clean, but it will not be guaranteed to cleanup all ghosted records each time it wakes up. The algorithm is hidden from us and it will only do enough work to try and keep up while balancing overall system performance.

    Deep dive: Inside the Storage Engine: Ghost cleanup in depth

    Thanks for this