June 1, 2011 at 8:04 pm
Comments posted to this topic are about the item Ghost Records
June 1, 2011 at 8:05 pm
First to answer and got it right! Never heard of it but the right answer was perfectly logical.
Great question!
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
June 1, 2011 at 10:49 pm
I remember reading about this in an article.
M&M
June 2, 2011 at 12:43 am
When rows are marked as deleted after a DML operation and the transaction is committed, the rows become Ghost records.
I got it right 🙂
June 2, 2011 at 2:17 am
Nice to know! DB2 behaves quite differently, because it just re-uses the page after records have been marked as deleted. It doesn't need an extra background process (ghost exorciser) to make a physical delete.
Not sure why SQL Server adopted this approach 🙂
Kwex.
June 2, 2011 at 5:49 am
I believe it is done for performance reasons. Deleting data can occur faster and rollbacks would be faster. Then a process can later update clear up the ghost records.
June 2, 2011 at 9:00 am
As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.
June 2, 2011 at 9:14 am
cfradenburg (6/2/2011)
As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.
Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2011 at 9:17 am
Interesting. What does the ghost cleanup process do then?
June 2, 2011 at 9:19 am
GilaMonster (6/2/2011)
cfradenburg (6/2/2011)
As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.
Sounds like a fun experiment...
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
June 2, 2011 at 9:29 am
cfradenburg (6/2/2011)
Interesting. What does the ghost cleanup process do then?
http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2011 at 9:30 am
SQLRNNR (6/2/2011)
GilaMonster (6/2/2011)
cfradenburg (6/2/2011)
As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.
Sounds like a fun experiment...
Utterly trivial. If you want I'll test now and blog on tuesday.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2011 at 9:32 am
GilaMonster (6/2/2011)
SQLRNNR (6/2/2011)
GilaMonster (6/2/2011)
cfradenburg (6/2/2011)
As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.
Sounds like a fun experiment...
Utterly trivial. If you want I'll test now and blog on tuesday.
That works for me - then I can try it out by following your blog 😀
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
June 2, 2011 at 9:33 am
Interesting question - thanks!
I was wrongly under the impression that SQL Server didn't mark records for deletion. FoxPro does that also, but FoxPro uses a hidden column on the table to mark them. So, how does the SQL engine mark the records as deleted?
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
June 2, 2011 at 10:50 am
SQLRNNR (6/2/2011)
GilaMonster (6/2/2011)
SQLRNNR (6/2/2011)
GilaMonster (6/2/2011)
cfradenburg (6/2/2011)
As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.
Sounds like a fun experiment...
Utterly trivial. If you want I'll test now and blog on tuesday.
That works for me - then I can try it out by following your blog 😀
Done (though Paul's blog post already demoed it, so I really haven't written anything useful and new here)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply