x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Ghost records.....ooooooo....scary

OK, Gotta admit to being floored by this.

First I would like to know how many people have heard of this.

Second I would like to go on to describe what this is, how it happens, and how your Ghosts are exorcised.  I guess there truely is a Ghost in the Machine.

When rows are marked as deleted after a DML operation and the transaction is committed, the rows become Ghost records.  Now in DB2, it doesn't care and if the page is mark deleted it will just reuse the page.  Not so in SQL Server.  I was amazed (and shocked) by the fact that there is a background process, the ghost exorciser, that goes on to search for these Ghosts and then does a physical delete (I think).  When Pat Phelan original pointed this out in his thread  he was having trouble where a 3rd party application was not taking care of the ghosts on a particular server.  We then descended in to a discussion of Trace Flags (again another WTF, I never heard of this before...or really only in passing) and didn't find much about them in documentation or books (well I must admit as of this writing I have not search SQL Team yet).  In the thread Pat goes on to show some trace flags that where used to identify these things. 

We were then joined by Paul Randal, who is the Dev Lead for Microsoft SQL Server Storage Engine.  The discussion turned to the trace flags as a potential cause of the problem.  There is actually a trace flag that will turn off the ghost exorciser.  Why anyone would want to do that, I have no idea, but it certainly looked like Pat's problem. Paul's comment “There are lots of (potentially scary) undocumented, or little-understood behaviors one could monitor in SQL Server, but unless its causing a problem, its best to be just understood and left alone.” sounded like good advice to me.  I will never ceased to be amazed at what 3rd party vendors come up with, when they can't tackle to root cause of a problem.  In most cases (read all) it's been becuase of bad database design or implementation.

Why SQL Server does not just reuse a page marked as deleted, I do not know.  I mean why incur the overhead of this excorsism process?

Anyway, the geek really came out in me today.  I was very excited to read about the internal process of SQL server, read about trace flags, to know about them as a potential place to look when thing just don't make sense on someone elses box, and to have a conversation with the guy who built the damn thing.

Yes, this was a good day.

Thanks for reading

Brett 

PS. As to not to regurgatate everything that was said in the thread, and to not parrot everything like I know what the He11 I'm talking about,  I suggest you give it a read

Print | posted on Tuesday, June 21, 2005 1:21 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Ghost records.....ooooooo....scary

Now that's a huge blog entry. Everything in bold?
6/21/2005 1:24 PM | Tara
Gravatar

# oops, sorry

Sorry, Font was too big...didn't look like that when I was writing it. Should be all better now.

So Tara, have you heard of Ghost records?
6/21/2005 1:30 PM | Brett Kaiser
Gravatar

# re: Ghost records.....ooooooo....scary

hi
10/20/2005 4:53 AM | lenny
Gravatar

# re: Ghost records.....ooooooo....scary

im relly scared of ghosts:(
12/10/2005 1:21 PM | ryan
Gravatar

# re: Ghost records.....ooooooo....scary

hi
8/12/2006 11:12 PM | noon
Gravatar

# re: Ghost records.....ooooooo....scary

OK
8/13/2006 1:01 PM | KULALAMBO
Gravatar

# re: Ghost records.....ooooooo....scary

i think it's very interesting~
???????
9/3/2006 9:47 AM | qmn91
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET