Ghost Cleanup

Question

Ghost Cleanup

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

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/[/url]

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 2

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,My blog: www.igormicev.com

WayneS SSC Guru Points: 95460 More actions · Answer 3

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/[/url])

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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?? 😀

:hehe:

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

sknox SSChampion Points: 12388 More actions · Answer 6

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. 😎

Bangla Hall of Fame Points: 3137 More actions · Answer 7

Bangla

Hall of Fame

Points: 3137

January 16, 2013 at 2:15 am

#1577707

Hmmm, very interesting questions..

Anipaul SSC-Insane Points: 24681 More actions · Answer 8

Anipaul

SSC-Insane

Points: 24681

January 16, 2013 at 2:18 am

#1577709

Nice question...

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 9

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

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 10

PChiragS

SSCarpal Tunnel

Points: 4965

January 16, 2013 at 9:03 am

#1577881

Great Question...

Thanks..

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 11

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
Propack, Inc.

Revenant SSC-Forever Points: 42467 More actions · Answer 12

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

TomThomson SSC Guru Points: 104773 More actions · Answer 13

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/[/url]
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

TomThomson SSC Guru Points: 104773 More actions · Answer 14

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

TomThomson SSC Guru Points: 104773 More actions · Answer 15

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