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