认识SQL Server的虚影行（Ghost record）

目录

什么是SQL Server的虚影行

在SQL Server中，为了加快数据的删除与数据删除后的回滚操作，对含有索引的表引入了数据的逻辑删除，即数据先被标记为删除，再进行物理删除。这些被标记删除，而还未执行物理删除的记录行，则被称为虚影行，即ghost record（又被称为ghost row）

Row in the leaf level of an index that has been marked for deletion, but has not yet been deleted by the database engine.

下图所示是SSMS中某个表虚影行的信息，
索引属性->碎片选项->虚影行数：

认识并维护虚影行

实际上SQL Server的虚影行由后台线程：ghost_cleanup自主维护。

为了更清晰的理解上述内容，我们做如下演示：创建一张含聚集索引的表并插入5000条记录，然后通过trace flags禁用后台线程ghost_cleanup，再删除表中所有记录后，最终观察记录行数的变化及影响。

首先，我们创建一个测试数据库mydb

USE masterGO--创建一个测试数据库CREATE DATABASE mydb;GO

然后在这个库中，创建一张含有5000条记录的聚集索引表ghosttest：

--创建一个测试表ghosttestCREATE TABLE ghosttest(      id int identity(1,1) NOT NULL ,      fname nvarchar(20) NOT NULL,      lname nvarchar(50) NULL) --在字段id上创建聚集索引CREATE CLUSTERED INDEX id ON ghosttest ( id )--循环插入5000条记录insert into ghosttest (fname, lname) values ('Hyper','Wang');go 5000

然后我们通过trace flag(661)禁用ghost record维护的相关线程：ghost_cleanup：

--打开661全局标记，禁用ghost record维护的相关线程DBCC TRACEON (661,-1)GO--查询当前trace flag状态DBCC TRACESTATUS-----------------------------------------------TraceFlag   Status  Global  Session661         1       1       0

接着我们删除聚集索引表ghosttest上的所有记录：

--delete表中的所有记录DELETE FROM ghosttest;--查询当前记录数SELECT * FROM dbo.ghosttest--------------------------------------------------

最终我们可通过如下sql，查询表ghosttest的虚影行数为5000行。
注意DB与表的参数

--查询表ghosttest的虚影行数SELECT    OBJECT_NAME(object_id) AS Name    ,record_count    ,GHOST_RECORD_COUNTFROM sys.dm_db_index_physical_stats(DB_ID(N'mydb'), OBJECT_ID('ghosttest'), NULL, NULL, 'DETAILED')-----------------------------------------------Name        record_count    GHOST_RECORD_COUNTghosttest   0               5000ghosttest   25              0

虚影行存在的坏处

通过上述内容，我们已经知道SQL Server存在虚影行，而且这个虚影行由后台线程自主维护。如上述，虚影行存在可加快表中记录的删除与记录删除后的回滚，但是虚影行的存在也有坏处：虚影行（Ghost record）会影响SQL Server的统计信息的预估，从而影响sql语句的整个执行计划。

在清空表ghosttest记录后，我们执行select语句，并查看IO的统计信息与实际执行计划：

--打开IO统计信息,并执行select语句SET STATISTICS IO ON;SELECT * FROM dbo.ghosttest-----------------------------------------------(0 row(s) affected)表 'ghosttest'。扫描计数 1，逻辑读取 27 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

注意：逻辑读取27次
同时，我们可能看到执行计划中预估信息：5000行

当我们开启ghost record相关线程后，我们再次执行select语句

--开启ghost record相关线程DBCC TRACEOFF(661,-1);--打开IO统计信息,并执行select语句SET STATISTICS IO ON;SELECT * FROM dbo.ghosttest-----------------------------------------------(0 row(s) affected)表 'ghosttest'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

我们可以看到，此时的逻辑读取已经由原来的27变为2。注意，本文虽然使用了聚集索引表进行说明，但对于含有非聚集索引的堆表同样存在虚影行，因此在我们使用SQL Server的过程中，我们需尽可能的降低虚影行带来的负面影响。对于高更改（即增/删/改）的堆表，个人建议在合适列上添加聚集索引(Clustered index)。

参考资料

1. Glossary – SQL Server 2008 R2
   https://msdn.microsoft.com/en-us/library/ms165911%28d=printer,v=SQL.105%29.aspx

2. Tuning options for SQL Server when running in high performance workloads
   https://support.microsoft.com/en-us/kb/920093
   文章收藏地址：
   http://blog.csdn.net/wstoneh01/article/details/50604857