Ghost Cleanup

Question

Post reply

Ghost Cleanup

WayneS

SSC Guru

Points: 95460
More actions
January 14, 2013 at 9:41 pm

#266983

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
January 15, 2013 at 5:21 am

This was removed by the editor as SPAM

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

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

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 More actions · Answer 2

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: 281334 More actions · Answer 3

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 More actions · Answer 4

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: 259105 More actions · Answer 5

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: 19505 More actions · Answer 6

Good question Wayne - thanks

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There 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: 22668 More actions · Answer 7

Carlo Romagnano

SSC-Insane

Points: 22668

January 15, 2013 at 1:33 am

#1577174

Interesting!

Thanks!

🙂

sqlnaive SSCoach Points: 17437 More actions · Answer 8

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 9

Gazareth

One Orange Chip

Points: 27737

January 15, 2013 at 2:55 am

#1577212

Nice question, thanks.

paul.knibbs SSCoach Points: 15320 More actions · Answer 10

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: 23099 More actions · Answer 11

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 More actions · Answer 12

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: 9063 More actions · Answer 13

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.