Database Ghosts

Recently you may have seen my first article on this topic over at SQL Solutions Group.  If not, here is the link so you can read that article first before proceeding here.  This article is intended as a first of two part follow-up to that article.

Now that you have read that other article, we can recap it a little bit.

You will recall that in that article, I discussed the presence of Ghosts in your database.  I also discussed that those ghosts are a good thing.  They have some benefits to them, of which is a bit of a performance boost for some operations like the rollback of a delete.

In that article I discussed one method with which you could see these ghost records.  In this article, I would like to share an alternate method to be able to see these ghosts.

In order to explore this alternate means, let’s go ahead and follow the first few steps from the other article to get the setup complete so our second ghost hunting foray can begin.

Use master;
Go
IF DB_ID('Cemetery') IS NULL 
BEGIN
 EXECUTE ('CREATE DATABASE Cemetery');
 ALTER DATABASE [Cemetery] SET RECOVERY SIMPLE WITH NO_WAIT
 ALTER DATABASE [Cemetery] MODIFY FILE ( NAME = N'Cemetery', SIZE = 6144000KB , FILEGROWTH = 262144KB )
 ALTER DATABASE [Cemetery] MODIFY FILE ( NAME = N'Cemetery_log', SIZE = 131072KB , FILEGROWTH = 131072KB )
END
GO
Use Cemetery;
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)
/* additional index to show the ghosts */Create NonClustered Index IX_GhostPinky on Halloween.Ghosts (Pinky)

Now with the setup complete, we should once again confirm that we have appropriate data available for the hunt.  Once again a simple query can suffice to show the data.

/* Take a peek at the data */Select top 100 *
From Halloween.Ghosts

Upon execution of the check script, we should see something similar to the following data-set.

Great, the data is confirmed and we should have a wonderful chance to find some ghosts once again.  In the previous attempt, we needed an additional plasma blaster in the form of a trace flag.  In this attempt we will hunt these ghosts without that tool and see what we can find.  Why attempt it in this way?  Well, it is simply because I’d rather not use a trace flag if it is not necessary.  If you recall, that trace flag had a couple of noted effects.  One of the effects was that it turned off the ghost cleanup process.  If I can avoid it, I’d rather leave the containment unit in tact.

Now, due to that clerical error of putting Halloween on the wrong date, we need to follow the prescribed policy to delete records prior to creating the proper records.

/* Somebody made a mistake and thought halloween was Oct 30 instead of Oct 31 
we need to kill those records
*/Begin tran
Delete top(666)
From Halloween.Ghosts
WHERE Slimer = '10/30/2014';

Notice that we issued the delete in a transaction this time around.  We are going to leave that transaction open for a bit while we hunt those ghosts.

The first step is to verify that some ghosts might be present.  To do that we should run a query such as the following.

/* 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('Cemetery'), OBJECT_ID('Halloween.Ghosts'), NULL, NULL , 'DETAILED') ps
INNER JOIN sys.objects so
ON ps.object_id = so.object_id
WHERE index_level = 0
;

From that query we should see something like the following.

Once again we are on the right track.  We can further confirm the existence of these ghosts through a little more investigation and monitoring.  Let’s try the trick with the dblog function again.

/* Use Fn_dblog to get Pages with Ghost Counts..works with TF 661 or an uncommitted tx */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';

With that query, we should see something like the following result set.

Very cool.  We once again can see that these phantasms are in the database.  We have enough information that we can proceed on to the next stage.  We can pass the PageID into DBCC PAGE in order to investigate the ghosts on the page.  If we use the PageID that is circled with the green in the preceding result set, we can get a better feel for these specters.

DBCC PAGE('Cemetery',1,2002440,3) WITH TABLERESULTS
GO

And we may see results such as the following.

Recall that the log says this page has ghost records on it.  When we check the page with DBCC PAGE we can definitely see that there are ghosts on the page.  This is very cool.  Now, had we tried to check for ghost records on the PFS page we would not be able to see the ghost count like we were able to see by enabling the TF.

Once again we have been able to display the existence of ghosts in the database.  In order to get these ghosts to move on from the database to their afterlife, we merely need to commit the transaction or roll back the transaction.

Stay tuned for the next article in this mini-series about ghosts.  Who knows, we may even have a bonus Halloween article since this is Halloween month.

Part III of the series can now be found here.