SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Wayne Sheffield / Ghost Cleanup / Latest Posts

RE: Ghost Cleanup

jfgoude — Fri, 22 Feb 2013 01:17:43 GMT

pure technical questionagain something which full brain with no real interest, interesting though

RE: Ghost Cleanup

L' Eomot Inversé — Fri, 18 Jan 2013 13:57:22 GMT

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.

RE: Ghost Cleanup

L' Eomot Inversé — Fri, 18 Jan 2013 11:33:37 GMT

[quote][b]Sean Lange (1/15/2013)[/b][hr]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[/quote]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.

RE: Ghost Cleanup

L' Eomot Inversé — Fri, 18 Jan 2013 11:23:59 GMT

[quote][b]WayneS (1/15/2013)[/b][hr][quote][b]okbangas (1/15/2013)[/b][hr]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.[quote]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.[/quote] [url=http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/]http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/[/url][/quote]You're absolutely correct. However, the question states:[quote]If you delete the first 12 records, how many data pages will the table have [b][i]after[/i][/b] the Ghost Cleanup process has been run against this table?[/quote] 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.[/quote]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?

RE: Ghost Cleanup

Revenant — Wed, 16 Jan 2013 13:22:45 GMT

Thanks for a really interresting question - it forced me to do some digging.

RE: Ghost Cleanup

Rob Schripsema — Wed, 16 Jan 2013 09:31:15 GMT

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!

RE: Ghost Cleanup

pchirags — Wed, 16 Jan 2013 09:03:05 GMT

Great Question...Thanks..

RE: Ghost Cleanup

(Bob Brown) — Wed, 16 Jan 2013 05:04:27 GMT

Thanks for the question. I learned something about ghost cleanup.

RE: Ghost Cleanup

Anipaul — Wed, 16 Jan 2013 02:18:05 GMT

Nice question...

RE: Ghost Cleanup

Bangla — Wed, 16 Jan 2013 02:15:18 GMT

Hmmm, very interesting questions..

RE: Ghost Cleanup

sknox — Tue, 15 Jan 2013 09:48:41 GMT

RE: Ghost Cleanup

SQLRNNR — Tue, 15 Jan 2013 08:54:57 GMT

RE: Ghost Cleanup

Sean Lange — Tue, 15 Jan 2013 08:50:37 GMT

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

RE: Ghost Cleanup

WayneS — Tue, 15 Jan 2013 08:35:51 GMT

[quote][b]IgorMi (1/15/2013)[/b][hr]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 rebuilddbcc ind ('TestDB','Test',1)Thanks,IgorMi[/quote]Absolutely correct (and this was pointed out in the demonstration example on my blog at [url=http://blog.waynesheffield.com/wayne/archive/2012/12/ghost-cleanup-process/][u]http://blog.waynesheffield.com/wayne/archive/2012/12/ghost-cleanup-process/[/u][/url])

RE: Ghost Cleanup

IgorMi — Tue, 15 Jan 2013 08:18:29 GMT

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 rebuilddbcc ind ('TestDB','Test',1)Thanks,IgorMi

RE: Ghost Cleanup

WayneS — Tue, 15 Jan 2013 08:07:38 GMT

[quote][b]okbangas (1/15/2013)[/b][hr]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.[quote]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.[/quote] [url=http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/]http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/[/url][/quote]You're absolutely correct. However, the question states:[quote]If you delete the first 12 records, how many data pages will the table have [b][i]after[/i][/b] the Ghost Cleanup process has been run against this table?[/quote] 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.

RE: Ghost Cleanup

Mighty — Tue, 15 Jan 2013 07:53:06 GMT

RE: Ghost Cleanup

DugyC — Tue, 15 Jan 2013 07:06:58 GMT

[quote][b]paul.knibbs (1/15/2013)[/b][hr][quote][b]okbangas (1/15/2013)[/b]But, there has not been any scan between the marking of ghost records and Ghost Cleanup Task, hence it has nothing to do.[/quote]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![/quote]+1Great question Wayne, thanks.

RE: Ghost Cleanup

mtassin — Tue, 15 Jan 2013 06:52:42 GMT

Good question Wayne. Thanks!

RE: Ghost Cleanup

paul.knibbs — Tue, 15 Jan 2013 06:06:36 GMT

[quote][b]okbangas (1/15/2013)[/b]But, there has not been any scan between the marking of ghost records and Ghost Cleanup Task, hence it has nothing to do.[/quote]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!

RE: Ghost Cleanup

Stewart "Arturius" Campbell — Tue, 15 Jan 2013 05:21:27 GMT

Good question, thanks WayneHad to dust off the old plates for this one...

RE: Ghost Cleanup

Gazareth — Tue, 15 Jan 2013 02:55:42 GMT

Nice question, thanks.

RE: Ghost Cleanup

sqlnaive — Tue, 15 Jan 2013 02:45:00 GMT

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

RE: Ghost Cleanup

Carlo Romagnano — Tue, 15 Jan 2013 01:33:44 GMT

Interesting!Thanks!:-)

RE: Ghost Cleanup

Stuart Davies — Tue, 15 Jan 2013 00:49:46 GMT

Good question Wayne - thanks

RE: Ghost Cleanup

Koen Verbeeck — Tue, 15 Jan 2013 00:35:28 GMT

Great question Wayne, thanks. Definately learned something.

RE: Ghost Cleanup

okbangas — Tue, 15 Jan 2013 00:10:05 GMT

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.[quote]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.[/quote] [url=http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/]http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/[/url]

RE: Ghost Cleanup

SQLRNNR — Mon, 14 Jan 2013 23:13:44 GMT

Thanks Wayne

RE: Ghost Cleanup

demonfox — Mon, 14 Jan 2013 22:18:45 GMT

I got it right for wrong reasons ;Learned something.. thanks for the question..

RE: Ghost Cleanup

Lokesh Vij — Mon, 14 Jan 2013 22:04:03 GMT

Thanks Wayne. Got to learn something new today :-)

Ghost Cleanup

WayneS — Mon, 14 Jan 2013 21:41:34 GMT

Comments posted to this topic are about the item [B]Ghost Cleanup[/B]