SQL Server数据库的Ghost清理任务

迪鲁宾
448 阅读8分钟

这篇文章解释了SQL Server数据库的Ghost清理任务的内部情况。

简介

每个关系型数据库都由后台进程和用户进程组成。同样，SQL Server也有许多内部进程，如Checkpoint、Lazy Writer、Lock Monitor、System_Health_Monitor、Trace Queue Task和Ghost CleanUp。大体上，我们可以把这些系统进程分为以下几类。

  • 数据编写员流程
    • 懒人写手
    • 检查点（CHECKPOINT
    • 渴望写作的人
  • 记录交易过程
    • LOG写入器
    • 备份日志
  • 系统流程
    • 资源监控
    • 锁定监控
    • 幽灵清理
    • 跟踪队列任务
  • 工作调度流程
    • SQL服务器代理

我看到DBA们对Checkpoint、Lazy Writer进程有一定的了解，但他们忽略了Ghost Cleanup后台进程的重要性。要了解这些后台进程的一些情况，请参考文章 -SQL Server中的CHECKPOINT、Lazy Writer、Eager Writer和Dirty Pages

在使用sp_who2或动态管理视图sys.dm_exec_requests检查进程时，你是否看到GHOST CLEANUP任务？你可以使用状态栏输出的Background来识别一个后台进程。

本文对SQL Server数据库中的幽灵清理任务进行了深入的探讨。

前提条件

如果你对SQL Server索引--集群和非集群索引有基本的了解，对理解本文的幽灵清理任务会有帮助。

  • 注意：你可以随时参考索引来了解索引的要求、架构和使用方法。

什么是SQL Server数据库的Ghost清理任务？

幽灵清理是一个定期运行并清理幽灵记录的后台进程。接下来的问题是--什么是幽灵记录？

当用户从聚类索引数据页或非聚类索引叶子页中删除记录时，SQL Server不会物理地删除它们。相反，它将这些记录标记为幽灵记录。因此，被删除的记录会留在页面上；但是，行头会显示该记录是一个幽灵记录。

这是一种性能优化技术，使删除操作执行逻辑删除并迅速完成。同样地，在回滚过程中，SQL Server必须将该记录取消标记为幽灵记录。因此，它消除了在回滚时重新插入这些被删除的记录的要求。

一旦删除事务提交，鬼魂清理任务发生，它就会检查页面上的鬼魂记录，并从物理上删除它们。SQL Server也会更新PFS（页面可用空间）的页头，将其标记为幽灵记录页。它还将数据库标记为有鬼魂记录。

幽灵清理任务执行以下工作。

  • 幽灵清理任务每5秒发生一次（SQL Server 2012+）。

  • 它开始检查被标记为有幽灵记录的数据库，并扫描幽灵数据库的PFS页面

  • 它从数据库中物理地删除幽灵记录行

  • 一旦它从数据库中删除了所有的幽灵记录，它就把它标记为没有幽灵条目。因此，在下一次运行鬼魂清理任务时，SQL Server会跳过该数据库。

重要说明。

  • GHOST CLEANUP任务的单次运行在每次执行中最多可以清理10个页面。这是为了确保不影响系统的性能。如果有多个页面（超过10个），它会在随后执行的幽灵清理任务中处理它们
  • 如果清理过程不能在有幽灵记录的数据库上取得共享锁，它就会跳过它，并在下一次运行时再次检查。*

通常情况下，如果你查询sp_who2或DMV，我们不会看到GHOST CLEANUP任务，因为它是一个快速过程。然而，对于一个有多次频繁删除的大量数据库，你可以在会话的输出中观察到这一点。

在本文中，为了捕获幽灵清理任务，我们将创建一个表[GhostSession]并使用WHILE循环向表中插入记录，直到捕获GHOST CLEANUP后台进程。

CREATE TABLE [dbo].[GhostSession] (
    [session_id] [smallint] NOT NULL,
    [start_time] [datetime] NOT NULL,
    [status] [nvarchar](30) NOT NULL,
    [command] [nvarchar](32) NOT NULL,
    [database_id] [smallint] NOT NULL
    ) ON [PRIMARY]
GO
SET NOCOUNT ON;
GO
DECLARE @i INT
SELECT @i = 0;
WHILE (@i < 1)
BEGIN
INSERT INTO [GhostSession]( [session_id],[start_time],[status], 
[command],[database_id])    
    SELECT  [session_id],[start_time],[status], [command],[database_id]
    FROM sys.dm_exec_requests WHERE command LIKE '%ghost%'
    SELECT @i = COUNT (*) FROM [GhostSession]
 
END;
GO
Select * from [GhostSession]

如下图所示，SQL Server触发了数据库id 5的幽灵清理任务。

Ghost Cleanup task in SQL Server Database

为了找出数据库中的幽灵记录，我们可以按照下面的脚本，使用DMVsys.dm_db_index_physical_stats 。这个脚本根据数据库ID对记录进行分组，并按照幽灵记录总数的降序排列。

  • 注意：DMV返回指定表或数据库的大小、碎片信息和幽灵记录信息。你可以参考微软的文档来了解DMV的使用情况sys.dm_db_index_physical_stats
SELECT sum(ghost_record_count) total_ghost_records, db_name(database_id) as DB
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED')
group by database_id
order by total_ghost_records desc

如下图所示，[AdventureWorks2017]数据库在运行DMV时有两条幽灵记录。我们在其余的数据库中没有任何幽灵记录。

Check ghost record count SQL Server Databases

SQL Server数据库的幽灵记录的实际演示

直到你对SQL Server的幽灵清理任务有了了解。本节将查看SQL Server内部页面，以确定幽灵记录和清理过程。为此目的，让我们创建一个SQL Server数据库，并在其中插入样本记录。

Create Database TestDatabase
Go
Use TestDatabase
Go
CREATE TABLE [dbo].[ViewGhostRecords]
(
    [ID] Int PRIMARY KEY CLUSTERED,
        [Name] varchar(100) NULL,
        [Amount] int NULL
 
) ON [PRIMARY]
CREATE  NONCLUSTERED INDEX [IX_Amount] ON [dbo].[ViewGhostRecords]
(
        [Amount] 
)
declare @i int
Set @i=0
While(@i<1000)
BEGIN
insert into [dbo].[ViewGhostRecords] values (@i, 'Ghost record demo',100)
set @i= @i+1
END

在我们进一步进行之前，我们启用以下两个跟踪标志。

  • 追踪标志3604用于在SSMS中查看DBCC结果

  • 追踪标志661用于禁用GHOST CLEANUP任务

  • 注意：除非你有特殊要求，否则不要在生产环境中启用跟踪标志。

DBCC traceon (3604,-1)
go
DBCC traceon (661,-1)
go
  • 注意：不要在生产实例中禁用GHOST CLEANUP。如果需要的话，在生产之前要有一个受控的环境测试。

此外，我们从演示表中删除记录，并使用DBCC PAGE查看页面数据。你也可以在SQL Server 2019中使用DMV sys.dm_db_page_info。

BEGIN TRAN ABCD
Delete from [dbo].[ViewGhostRecords] 
go
DBCC PAGE(7,1,1,3) WITH TABLERESULTS
go

样本输出显示了PFS页面上有信息的条目。

View page details

你可以查询索引DMV来检查幽灵记录的数量。如下图所示，到现在为止，它有2000条幽灵记录。

Verify ghost records increased count

我们还可以使用fn_dblog()函数来检查交易日志的内容。

DECLARE @TranID CHAR (20)
 
SELECT @TranID = [Transaction ID] FROM fn_dblog (null, null) WHERE
    [Transaction Name]='ABCD'
 
SELECT * FROM fn_dblog (null, null) WHERE [Transaction ID] = @TranID;
 
GO

在输出结果中，你看到操作为LOP_DELETE_ROWS，上下文为LCX_MARK_AS_GHOST，用于样本表中删除的行。

Check context as LCK_MARK_AS_GHOST

禁用SQL Server数据库的幽灵清理功能

禁用ghost清理过程会增加缓冲池的大小，提高IO请求。它可能会增加数据库的大小，因为SQL Server不会重复使用通过删除行而清除的空间。SQL Server还必须添加新的页面，以适应新的插入，增加页面分割，最终导致性能问题

正如我们前面所看到的，你可以使用跟踪标志661来禁用幽灵清理。然而，不建议这样做，直到你有一个特定的要求，并且在生产版本中测试得足够好。

然而，如果你禁用了幽灵清理任务，你可以使用下面的存储过程手动删除幽灵记录。

在SQL Server数据库中使用存储过程sp_clean_db_free_space

这个存储过程清理了数据库页面上的幽灵记录。它需要两个参数-数据库名称（必选）和清理延迟时间（秒）（可选）。清理延迟是一个可以指定的间隔时间，用来延迟清理页面的时间。默认值是0。

存储过程sp_clean_db_free_space的语法。

sp_clean_db_free_space
    [ @dbname = ] 'database_name'   
    [ , [ @cleaning_delay = ] 'delay_in_seconds' ] [;]

SQL Server数据库中的存储过程sp_clean_db_file_free_space

这个存储过程可以清理一个数据库的特定数据文件中的幽灵记录。如语法所示，它需要一个额外的数据文件id（强制）参数。

存储过程sp_clean_db_file_free_space的语法。

sp_clean_db_file_free_space   
    [ @dbname = ] 'database_name'   
    , [ @fileid = ] 'file_number'   
    [ , [ @cleaning_delay = ] 'delay_in_seconds' ] [;]
  • 注意：这些存储过程的执行时间取决于数据库的大小、数据文件、可用自由空间、磁盘容量。你应该在非工作时间运行它们，以避免任何性能影响*

总结

这篇文章探讨了在SQL Server数据库中删除幽灵记录的幽灵清理任务。你应该了解这个有用的后台程序。如果你因为特定的原因禁用它，可以使用存储过程来手动清理它们。这篇文章的目的是让大家了解鬼魂清理任务。 因此，我再次重申，除非你有特殊原因，否则永远不要禁用SQL Server中的鬼魂清理任务。

avatar
文章
阅读
粉丝