Ghost Cleanup

  • WayneS

    SSC Guru

    Points: 95362

    Comments posted to this topic are about the item Ghost Cleanup

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Lokesh Vij

    SSChampion

    Points: 10836

    Thanks Wayne. Got to learn something new today 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • demonfox

    SSCertifiable

    Points: 6289

    I got it right for wrong reasons ;

    Learned something.. thanks for the question..

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks Wayne

    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

  • okbangas

    SSChampion

    Points: 11773

    As far as I understand it, there is something missing from the explanation. If we assume that only what's written in the scenario has happened, the scenario is as follows:

    * Table is created

    * Records are inserted

    * Records are deleted -> Marked as ghost

    * Ghost Cleanup Task is running (every 5 seconds).

    But, there has not been any scan between the marking of ghost records and Ghost Cleanup Task, hence it has nothing to do.

    The ghost cleanup task doesn't just start up when it's told to – it starts up in the background every 5 seconds and looks for ghost records to cleanup. Remember that it won't be told to go cleanup a specific page by a delete operation – it's a subsequent scan that does it, if a scan happens.

    http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/[/url]



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Great question Wayne, thanks. Definately learned something.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Stuart Davies

    SSCoach

    Points: 18874

    Good question Wayne - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • Carlo Romagnano

    SSC-Insane

    Points: 21876

    Interesting!

    Thanks!

    🙂

  • sqlnaive

    SSCoach

    Points: 17435

    Great question. I needed to know. Lost a point but worth the knowledge.

  • Gazareth

    One Orange Chip

    Points: 27737

    Nice question, thanks.

  • This was removed by the editor as SPAM

  • paul.knibbs

    SSCoach

    Points: 15270

    okbangas (1/15/2013)

    But, there has not been any scan between the marking of ghost records and Ghost Cleanup Task, hence it has nothing to do.

    I think that's a scan that's carried out by the ghost cleanup task itself, not a random scan that's done as part of a SELECT statement. May be wrong on that, but that's how I read it!

  • mtassin

    SSC-Insane

    Points: 23096

    Good question Wayne. Thanks!



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • DugyC

    Hall of Fame

    Points: 3804

    paul.knibbs (1/15/2013)


    okbangas (1/15/2013)

    But, there has not been any scan between the marking of ghost records and Ghost Cleanup Task, hence it has nothing to do.

    I think that's a scan that's carried out by the ghost cleanup task itself, not a random scan that's done as part of a SELECT statement. May be wrong on that, but that's how I read it!

    +1

    Great question Wayne, thanks.

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • Mighty

    SSCrazy Eights

    Points: 8559

    okbangas (1/15/2013)


    As far as I understand it, there is something missing from the explanation. If we assume that only what's written in the scenario has happened, the scenario is as follows:

    * Table is created

    * Records are inserted

    * Records are deleted -> Marked as ghost

    * Ghost Cleanup Task is running (every 5 seconds).

    But, there has not been any scan between the marking of ghost records and Ghost Cleanup Task, hence it has nothing to do.

    I agree with you that in this scenario the scan was not done, so nothing was done, but that is not relevant for the explanation: the page will never be completely deleted by the Ghost Cleanup Task, even if it would have been scanned before. As far as I can see that was the important lesson to be learned here.

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

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