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 9:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:26 AM
Points: 1,293, Visits: 1,646
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??


Actually, if you want to be pedantic, records is the correct term here, because we're talking about the physical storage. Rows refers to the logical model.
Post #1407347
Posted Wednesday, January 16, 2013 2:15 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, June 30, 2014 10:51 PM
Points: 754, Visits: 145
Hmmm, very interesting questions..
Post #1407687
Posted Wednesday, January 16, 2013 2:18 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,300, Visits: 1,378
Nice question...


Post #1407689
Posted Wednesday, January 16, 2013 5:04 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Thanks for the question. I learned something about ghost cleanup.
Post #1407754
Posted Wednesday, January 16, 2013 9:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:38 AM
Points: 1,780, Visits: 582
Great Question...
Thanks..
Post #1407904
Posted Wednesday, January 16, 2013 9:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:48 AM
Points: 1,935, Visits: 10,956
Great question -- I wasn't even aware there WAS such a thing as the Ghost function. The question forced me to do some research -- thanks!

Rob Schripsema
Accelitec, Inc
Post #1407933
Posted Wednesday, January 16, 2013 1:22 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 4,382, Visits: 3,392
Thanks for a really interresting question - it forced me to do some digging.
Post #1408065
Posted Friday, January 18, 2013 11:23 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 8,551, Visits: 9,043
WayneS (1/15/2013)
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.

If the cleanup process doesn't have any pages it's been told by scans to do, doesn't it use the PFS map to find pages to do? And isn't the PFS map marked by the delete operation, not by a subsequent scan?


Tom
Post #1409036
Posted Friday, January 18, 2013 11:33 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 8,551, Visits: 9,043
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??


I doubt it, because I'm sure that Joe recognizes that "record" is the right name for something deep down in the data engine, way below the relational model, and that's what Paul is talking about here - a string of bits somewhere on a disc (or on a cached copy of part of a disc) not a row in the relational model. The relation no longer contains that row once the transaction is committed, but its storage container will contains the record until the cleanup task or index defragmentation or rebuilding removes it, and that can be a long time after the transactio is committed - that's how we know that this is a record not a row.


Tom
Post #1409041
Posted Friday, January 18, 2013 1:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 8,551, Visits: 9,043
Good question, and the reference in the explanation is useful.
I answered it correctly because I remembered that a table keeps its pages until the index thy are a component of is dropped, rebuilt (or defragmented?) or the table is dropped (or truncated), so the number wasn't going to go down and there was nothing that could cause it to go up - perhaps that's a simpler way to think of it than worrying about low level stuff in the storage model.


Tom
Post #1409097
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse