Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Ghost Cleanup Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 5:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 3,966, Visits: 5,207
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”
Post #1407191
Posted Tuesday, January 15, 2013 6:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 9:52 AM
Points: 1,589, Visits: 5,628
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!
Post #1407211
Posted Tuesday, January 15, 2013 6:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:38 AM
Points: 3,675, Visits: 72,434
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
Post #1407250
Posted Tuesday, January 15, 2013 7:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, 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"
Post #1407260
Posted Tuesday, January 15, 2013 7:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:26 AM
Points: 3,265, Visits: 1,290
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.
Post #1407276
Posted Tuesday, January 15, 2013 8:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 5,358, Visits: 8,916
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
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
Post #1407283
Posted Tuesday, January 15, 2013 8:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:38 PM
Points: 2,964, Visits: 2,984
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
www.seavus.com
Post #1407288
Posted Tuesday, January 15, 2013 8:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 5,358, Visits: 8,916
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
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
Post #1407301
Posted Tuesday, January 15, 2013 8:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 12,928, Visits: 12,347
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??


_______________________________________________________________

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 Moden's 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)
Post #1407308
Posted Tuesday, January 15, 2013 8:54 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 17,628, Visits: 15,487
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??






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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1407312
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse