SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ghost Cleanup


Ghost Cleanup

Author
Message
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10968 Visits: 7325
Good question, thanks Wayne
Had to dust off the old plates for this one...

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2994 Visits: 6235
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
mtassin
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5336 Visits: 72521
Good question Wayne. Thanks!



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
DugyC
DugyC
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1550 Visits: 779
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.

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Mighty
Mighty
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4614 Visits: 1710
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.
WayneS
WayneS
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12207 Visits: 10602
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.

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/


You're absolutely correct. However, the question states:
If you delete the first 12 records, how many data pages will the table have after the Ghost Cleanup process has been run against this table?

It's not going to run against this table until something scans this table, but once it does, it will leave that page with only one record in it.

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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6954 Visits: 5129
Hi,
Interesting question!

The expected one data page will be listed after rebuilding the index on the table.

alter index PK_Test on dbo.Test rebuild
dbcc ind ('TestDB','Test',1)

Thanks,
IgorMi

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
WayneS
WayneS
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12207 Visits: 10602
IgorMi (1/15/2013)
Hi,
Interesting question!

The expected one data page will be listed after rebuilding the index on the table.

alter index PK_Test on dbo.Test rebuild
dbcc ind ('TestDB','Test',1)

Thanks,
IgorMi


Absolutely correct (and this was pointed out in the demonstration example on my blog at http://blog.waynesheffield.com/wayne/archive/2012/12/ghost-cleanup-process/)

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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Sean Lange
Sean Lange
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33158 Visits: 17669
Great question Wayne. I knew the page would not be deallocated but I didn't know all of the details. The article was very interesting and informative read.

I wonder if Celko will start bashing Paul Randal for calling them records instead of rows?? :-D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40303 Visits: 18565
Sean Lange (1/15/2013)
Great question Wayne. I knew the page would not be deallocated but I didn't know all of the details. The article was very interesting and informative read.

I wonder if Celko will start bashing Paul Randal for calling them records instead of rows?? :-D


Hehe



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search