Ghost Records in SQL Server… Now whats that ????

I always wondered what happens when a row is deleted from a table.Does SQL server directly overwrites the record or makes it invalid or it does something which is undocumented.Little bit of googling let me to this http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/23/644607.aspx

There was brief description done by Paul Randal in the msdn blog quoted above.
“These are records that have been logically deleted but not physically deleted from the leaf level of an index.”

So what exactly are those records which are logically deleted but are not physically deleted.Well these records are called as “Ghost Records”.
What exactly happens when a delete statement is fired? Well the record affected by the delete statement in the data page is marked as ghost and is not released until the Ghost CleanUp Process takes over which runs asynchronously,deletes the records physically and frees the space.

Now enough of definitions now lets be practical….

I will start by creating a table with a single column and insert only one record and run the delete statement to find exactly how is that ghost record managed.I will be using DBCC IND and DBCC PAGE commands to dig into the internals of the datapage.If you do not know exactly what these commands do then maybe you will have to do some reading on these commands before you can continue further on this article.We will also be creating a clustered index on the column and experiment this on the index.
Always remember DO NOT run any of the undocumented DBCC commands on your production databases.

Microsoft will not be liable for any unwanted behaviour resulting due to execution of these commands.

Use master
	 
Go
  
Create Table T(Id Int)
 
Go
  
Create Clustered Index IX_ID On T(Id)
	 
Go
	 
Insert T
 
Select 1

So far so good.Now lets use the DMV index_physical_stats to get the objectid assigned to the index of the record in the datapage.

Select object_id,index_id,index_depth,index_level From sys.dm_db_index_physical_stats(db_id(),object_id('T'),object_id('IX_ID'),null,null)

In my case the Index has been allocated an ID of 688721506.Now lets find more details of this index in the sysindexes table by running the following query

Select id,name,root,first from sys.sysindexes where id=688721506

Nothing surprising in the o/p.We have a same root and the leaf page memory addresses.Lets use the DBCC IND to get the pagefileid.
In my case I am getting 2 page ids one is id 342 which is of type 10 i.e an IAM page and Page id 345 of the type 1 i.e an Index page.Now let us dig a bit deeper into the actual page using the DBCC PAGE command.You need set the DBCC TRACEON (3604) to enable this feature.

DBCC TRACEON (3604)
GO
DBCC PAGE(master,1,345,1)

The first parameter DBCC PAGE takes is the dbname, the second one is the indexid for the object T (Refer to the DBCC IND screenshot), the third one is the pageid and the fourth is the level of details you need to view for the page.Values for the fourth parameter range from 0 to 3.We will go with value 1 for the fourth parameter as we need very few details displayed.

Refer to the screenshot above.In the screenshot there are two details which are marked in red.First is the m_ghostRecCnt with value 0 and the second is Record Type=’Primary Record’

Now lets delete that single record from the table and re run the DBCC PAGE command.

Delete from T where Id=1
 
GO
DBCC PAGE(master,1,345,1)

Now check the value of m_ghostRecCnt and Record Type in the screenshot above.The value of 1 indicated for  m_ghostRecCnt indicates that now it is a ghost record and wont be available for any DML statements for table T.

Why does SQL do all this ? In order to prevent the overhead of rewriting the records in case of Rollbacks.

In case of Rollbacks SQL will simply set the value of m_ghostReCnt to 0 and the record will be available for any future DML statements.

So will the record always be unavailable and the space occupied by the deleted row.Well No…..SQL Server will delete the records marked as 1 in the database using an asynchronous task called  “Ghost Cleanup Task” and reclaim the space occupied.

Now let us see what happens when the record is deleted in a transaction and the transaction gets rolledback.Make sure you insert the record once again.

Lets see the behaviour by running the following delete statement within a transaction.

Begin Tran
 
   Delete from T where Id=1

Now lets run the DBCC page command to see the status of the record.

Well exactly same thing happened what I mentioned above.The record is marked as deleted and becomes unavailable for any future DML statements until the transaction is committed or rollbacked.I wonder whether the record will be available with a NoLock because NoLocks dont respect any other lock or transactions.Lets see….

Select  *  From T (nolock)

The above query did not return any data.So it means the record deletion is considered committed by NoLock. Now lets rollback the transaction and run the DBCC Page to check the status of the page.As expected the status of the m_ghostReCnt is set to 0 which means now the record is available for any future DML statements.

Regards

Sachin Nandanwar

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook