Ghost Records

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715375

    Comments posted to this topic are about the item Ghost Records

  • Peter Trast

    SSCarpal Tunnel

    Points: 4332

    First to answer and got it right! Never heard of it but the right answer was perfectly logical.

    Great question!

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • M&M

    SSC-Insane

    Points: 21679

    I remember reading about this in an article.

    M&M

  • khullargirish02

    SSChasing Mays

    Points: 651

    When rows are marked as deleted after a DML operation and the transaction is committed, the rows become Ghost records.

    I got it right 🙂

  • Kwex

    Default port

    Points: 1407

    Nice to know! DB2 behaves quite differently, because it just re-uses the page after records have been marked as deleted. It doesn't need an extra background process (ghost exorciser) to make a physical delete.

    Not sure why SQL Server adopted this approach 🙂

    Kwex.

  • dave.clark

    SSC Veteran

    Points: 235

    I believe it is done for performance reasons. Deleting data can occur faster and rollbacks would be faster. Then a process can later update clear up the ghost records.

  • chrisfradenburg

    SSCrazy Eights

    Points: 9592

    As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    cfradenburg (6/2/2011)


    As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.

    Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • chrisfradenburg

    SSCrazy Eights

    Points: 9592

    Interesting. What does the ghost cleanup process do then?

  • SQLRNNR

    SSC Guru

    Points: 281210

    GilaMonster (6/2/2011)


    cfradenburg (6/2/2011)


    As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.

    Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.

    Sounds like a fun experiment...

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Gail Shaw

    SSC Guru

    Points: 1004446

    cfradenburg (6/2/2011)


    Interesting. What does the ghost cleanup process do then?

    http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail Shaw

    SSC Guru

    Points: 1004446

    SQLRNNR (6/2/2011)


    GilaMonster (6/2/2011)


    cfradenburg (6/2/2011)


    As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.

    Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.

    Sounds like a fun experiment...

    Utterly trivial. If you want I'll test now and blog on tuesday.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • SQLRNNR

    SSC Guru

    Points: 281210

    GilaMonster (6/2/2011)


    SQLRNNR (6/2/2011)


    GilaMonster (6/2/2011)


    cfradenburg (6/2/2011)


    As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.

    Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.

    Sounds like a fun experiment...

    Utterly trivial. If you want I'll test now and blog on tuesday.

    That works for me - then I can try it out by following your blog 😀

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • mtillman-921105

    SSCertifiable

    Points: 7049

    Interesting question - thanks!

    I was wrongly under the impression that SQL Server didn't mark records for deletion. FoxPro does that also, but FoxPro uses a hidden column on the table to mark them. So, how does the SQL engine mark the records as deleted?

    ______________________________________________________________________The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • Gail Shaw

    SSC Guru

    Points: 1004446

    SQLRNNR (6/2/2011)


    GilaMonster (6/2/2011)


    SQLRNNR (6/2/2011)


    GilaMonster (6/2/2011)


    cfradenburg (6/2/2011)


    As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.

    Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.

    Sounds like a fun experiment...

    Utterly trivial. If you want I'll test now and blog on tuesday.

    That works for me - then I can try it out by following your blog 😀

    Done (though Paul's blog post already demoed it, so I really haven't written anything useful and new here)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply