Ghosts in your Database

Yes Virginia, there are ghosts in your database.  More specifically, there are ghosts in your SQL Server database.  They are not there to haunt you.  They are not there just for this holiday season (speaking of Halloween Month).

How can there be ghosts in the database?

Why would there be ghosts in the database?

Do they happen because somebody issued a KILL statement?

Let’s address each of those in turn.   A database ghost record is (in a very basic form) one that’s just been deleted in an index on a table . Delete operations don’t actually physically remove records from pages – they only mark them as having been deleted (ghosted). Now why is it done this way?  The answer here is largely performance based.  This is a performance optimization that allows delete operations to complete more quickly. Additionally, it allows the rollback of delete operations to process more quickly.  The rollback processes faster because all that needs to happen is to “flip the flag” for the records as being deleted/ghosted, instead of having to reinsert the deleted records.  That may be a bit over-generalized, but I hope you get the gist.  In short, records are marked as “ghosted” when a delete operation is performed; and to rollback, you simply undo that mark.

Now, what about this KILL statement thing?  The kill statement is pure Halloween fun and does not create ghost records.

Ghost Hunting

Now that we have established the purpose of Ghosts in the database, how do you verify the existence of Ghosts?  In other words, what can we do to prove there really are spectral things in the database?  This is where the fun really begins.  First, we need to get out the equipment and tools (as any good ghost hunter would do) so we can capture these phantasms.  Let’s call the first tool the “trap”.  Here is what you will need for it.

Use master;
Go
IF DB_ID('Sandbox') IS NULL 
BEGIN
EXECUTE ('CREATE DATABASE Sandbox');
ALTER DATABASE [Sandbox] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [Sandbox] MODIFY FILE ( NAME = N'Sandbox', SIZE = 6144000KB , FILEGROWTH = 262144KB )
ALTER DATABASE [Sandbox] MODIFY FILE ( NAME = N'Sandbox_log', SIZE = 131072KB , FILEGROWTH = 131072KB )
END
GO
Use Sandbox;
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF SCHEMA_ID('Halloween') IS NULL EXECUTE ('CREATE SCHEMA Halloween');
GO
IF OBJECT_ID('Halloween.Ghosts','U') IS NOT NULL
BEGIN
DROP TABLE Halloween.Ghosts
END
GO

DECLARE @BeginDate DATE = '2014-10-01'
,@EndDate DATE = '2014-10-31'
 SELECT TOP 1000000
        Pinky       = IDENTITY(INT,1,1),
        Blinky= 'GHOST ITEM ' + CONVERT(VARCHAR(20),ISNULL('', 0)),
        CandyMan= ABS(CHECKSUM(NEWID()))%50000+1,
        Sadako= CHAR(ABS(CHECKSUM(NEWID()))%26+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%26+65),
        Slimer= DATEADD(DAY,RAND(CHECKSUM(NEWID())) * ( 1 + DATEDIFF(DAY, @EndDate,@BeginDate) ), @EndDate),
        Poltergeist= RIGHT(NEWID(),12),
        MalcomCrowe= CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
        TheBlob= REPLICATE('Hey, Dracula!! Why do you say blah, blah blah?',1000)
   INTO Halloween.Ghosts
   FROM Master.dbo.SysColumns t1,
        Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
/* let's get our Blinky updated properly */Update hg
Set Blinky = 'GHOST ITEM ' + CONVERT(VARCHAR(20),ISNULL(Pinky, 0))
From Halloween.Ghosts hg;
--===== A table is not properly formed unless a Primary Key has been assigned
     -- Takes about 1 second to execute.
  ALTER TABLE Halloween.Ghosts
        ADD PRIMARY KEY CLUSTERED (Pinky)
/* Make sure the soylent green blob is BIG */--UPDATE Halloween.Ghosts
--SET TheBlob = TheBlob + REPLICATE('Casper is not a real Poltergeist! Frankenstein hates being called Frankenmeanie.',15000);
--GO
/* additional index to show the ghosts */Create NonClustered Index IX_GhostPinky on Halloween.Ghosts (Pinky)

This trap, err database, can be a bit large.  As currently configured, we will need about 16GB of disk space to support it.  If that is too much, I recommend removing the last column – “TheBlob”.  As you can see, we are setting a rather large trap.  The table we create (Halloween.Ghosts) will receive One Million records.  This is most probably overkill to catch these ghosts, so you can also cut back on the number of records to be affected.

Now, to make sure we have some data and that we can use the table, let’s just run a little test query.

Select top 100 *
From Halloween.Ghosts

Excellent, we have a good sample of data.

At this point, it is important to note that we have done nothing that will cause database ghost records.  All that has been done is to set the framework so we can see the ghosts.  With the framework in place, let’s try to catch some ghosts.  To do so, we need to try to delete something.  Since we just happen to have had a clerical error in our database, we have 666 prime candidates to try and fix.  We happen to have several records that were supposed to be given a Slimer date of Halloween.  The clerk, being absent minded, thought that Halloween was supposed to be on Oct. 30.  Our business model dictates that the invalid records must be deleted first and then we can try to enter the replacement records.  So, let’s go ahead and try to remove those records.

Before we remove the records though, we need to discuss one important requirement for us to be able to see the ghosts.  Let’s call it spectral vision goggles.  In the database realm, we call it a trace flag.  In order to see the the ghosts on the pages, we need to enable TF 661.  We can do that with the following statement.  There is a serious side effect to this method too – it alters the behavior of the Ecto Containment Unit or automatic ghost cleanup process.  If you enable this, you will need to disable it later and/or manually run a ghost cleanup.

/* tweak the ghost cleanup with an undocumented TF 
Required for DBCC Page results to show which pages have a GHOST entry
Don't try this at home - we are what you call experts (besides this is only a lab box!!) */DBCC TRACEON (661, -1)
GO

Now that we have the last piece of equipment in place, let’s go ahead and try to delete some records.

Delete top(666)
From Halloween.Ghosts
WHERE Slimer = '10/30/2014';

With all of those records deleted (all 666 of them), let’s see what we might have captured.  First, let’s take a look at some index stats.

/* ghost_record_count */Set transaction Isolation level read uncommitted
SELECT DB_NAME(database_id) AS DBName,schema_name(so.schema_id) + '.' + so.name as ObjName, record_count,index_type_desc
,ghost_record_count,version_ghost_record_count
FROM sys.dm_db_index_physical_stats(db_id('sandbox'), OBJECT_ID('Halloween.Ghosts'), NULL, NULL , 'DETAILED') ps
INNER JOIN sys.objects so
ON ps.object_id = so.object_id
WHERE index_level = 0
;

If we look at the output of this query, we will see that we did indeed attempt to delete 666 records.  Those records will now display in the ghost_record_count column.  We will also see that, since we had two indexes on the table, there are 666 ghost records marked on each index.

Very cool!  We are definitely on the track to capturing those ghosts.  We have a trail that they exist in the database.  Let’s keep going and see where we can see them.  You should note that there is an additional column in our result set that looks like it might be related to ghost records.  We are going to leave the discovery of version_ghost_record_count as a homework experiment for you to perform.  It is beyond the current scope of this article.

/* Use Fn_dblog to get Pages with Ghost Counts */Select Operation,Context,[Transaction ID],[Page ID] AS [File:PageIDHex],AllocUnitId,[Slot ID],PartitionId
,CONVERT(INT,CONVERT(VARBINARY,'0x' + RIGHT([Page ID], 8),1)) AS PageID
From fn_dblog(null,null) fn
Where CONTEXT = 'LCX_MARK_AS_GHOST';

Now this is getting exciting.  We have stronger evidence in the log showing that these ghosts are hanging around in the database.  Not only are they hanging around in the database, we can see which pages in the database on which they are trying to hide.

This is really solid information!  fn_dblog is giving us just about everything we need in order to get those ghosts.  It took a little bit of work since the log reports the page number in hex.  Converting that to an integer page number is essential for us to look at the page (besides integer values are easier to interpret for most people).  Now I can take that PageID and pass that number, for any of the records reported by fn_dblog, and pass it into yet another undocumented procedure known as DBCC Page.

When looking to use DBCC page, we can either look at the PFS Page and see more pages that have ghost record counts.  Or we can take the results seen from the fn_dblog output  and then look at the contents of the page and catch those ghosts.  We will take a quick look at the PFS page first.  Then we will take a look at an index page next.  In this database that we have created, the PFS page will show several other pages that have ghost records on them.  Due to the size (over 2 million pages), we only see index pages with ghost records in that result.  If our database were smaller, we would quite possibly see data pages in our first PFS page of the database.  Let’s see a sample from the first PFS in this database.

DBCC PAGE('Sandbox',1,1,3) WITH TABLERESULTS
GO

We can follow that link from this point to page 126.  Page 126 happens to be an index page similar to the following.  There are a couple of indicators that this is an index page.  First being that when we run DBCC Page with a format of 3, we will see two result sets.  The second result set will show statistics and index information.  The second being in the image attached after the query.  We will leave it as an exercise to you to see other ways to demonstrate that this is an index page.

DBCC PAGE('Sandbox',1,126,3) WITH TABLERESULTS
GO

That is great, but we have more ghosts to find.  Let’s look at a ghost on a data page.  Randomly picking a PageID from that list that was output from fn_dblog, let’s see what DBCC Page will provide to us.

DBCC PAGE('Sandbox',1,1522936,3) WITH TABLERESULTS
GO

Conclusion

Well, isn’t that just cool!  We have trapped a bunch of ghosts and were even able to see them.  This has been a fantastic deep dive into the crypts of the database.  This is merely a scratch on the surface though.  We hope this will encourage you to explore a bit and at least try one of the homework assignments we left behind in this article.

With all of that, we have a bit of cleanup to do.  The cleanup comes in one of two methods.  Method one involves manual labor.  Method two involves our friendly little trace flag we already used.  Since most DBAs prefer the automated mechanisms over manual, let’s just discuss method two for now.  It is extremely effortless.

DBCC TRACEOFF (661, -1)
GO

That will put the system back to the way it was when we started (and of course we trust that nobody did this on their prod box).

This has been one of a few articles about ghosts in the database. You can check out some of the others here and here.