Ghost Records

  • Comments posted to this topic are about the item Ghost Records

  • 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

  • I remember reading about this in an article.

    M&M

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

    I got it right 🙂

  • 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.

  • 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.

  • 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.

  • 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
  • Interesting. What does the ghost cleanup process do then?

  • 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

  • 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
  • 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
  • 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

  • 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

  • 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