SQL Server 2012回升一直卡在ASYNC_IO_COMPLETION浅析

当SQL
Server
2012(11.0.7001.0)下面在平复一个数据库(备份文件40多G大小,实际数据库大小300G),在尚原经过被,出现一直待ASYNC_IO_COMPLETION,如下测试截图所示,已经待了72分钟了,但是还原比例仍为0%

 

SELECT  r.session_id ,

        r.command ,

        r.start_time,

        r.status,

        r.wait_type,

        CONVERT(NUMERIC(6, 2), r.percent_complete)   AS [Percent Complete(%)] ,

        CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time,

                                     GETDATE()), 20) AS [ETA Completion Time] ,

        CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] ,

        CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min] ,

        CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0

        / 60.0) AS [ETA Hours] ,

        CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(text,

                                                  r.statement_start_offset / 2,

                                                  CASE WHEN r.statement_end_offset = -1

                                                       THEN 1000

                                                       ELSE ( r.statement_end_offset

                                                              - r.statement_start_offset )

                                                            / 2

                                                  END)

                                 FROM   sys.dm_exec_sql_text(sql_handle)

                               )) AS CommandText

FROM    sys.dm_exec_requests r

WHERE   command IN ( 'RESTORE DATABASE', 'BACKUP DATABASE','RESTORE LOG' );

 

 

 

图片 1

 

本,这里是试验,如果还要连续伺机的话,相信这时空会见重复增长。这个是于给人想不到之场景。后面查了瞬间之和即时文件初始化(Instant
File Initialization
(IFI))有关。关于此概念,可以参见官方文档数据库文件初始化
,摘抄部分情如下所示:

 

 

 

数据库文件初始化

 

初始化数据与日志文件为遮盖前删除的文本遗留在磁盘上之另现有数量。 执行以下中同样宗操作时,应首先通过零填充(用零填充)数据以及日志文件来初始化这些文件:

 

·        
创建数据库。

·        
向现有数据库添加数据或者日志文件。

·        
增大现有文件之尺寸(包括电动增长操作)。

·        
还原数据库或文件组。

 

文本初始化会招致这些操作费还多时光。 但是,首不善将数据写入文件后,操作系统就是无需因此零来填充文件

 

 

立文件初始化 (IFI)

 

每当SQL
Server中,可以于转瞬针对数据文件进行初始化,以避免零填充操作。即时文件初始化可以快捷执行上述文件操作。 即时文件初始化功能将回收利用的磁盘空间,而无论是需采用零填充空间。 相反,新数据写入文件时见面盖磁盘内容。 日志文件未克立初始化。

 

备注

除非在 Microsoft Windows
XP Professional 或 Windows
Server 2003 或还胜版本被才得以行使就经常文件初始化功能。

 

重要

只有当数据文件中才堪以就经常文件初始化功能。 创建日志文件或者其尺寸增长时,将始终零填充该文件。

 

即时文件初始化功能就于朝SQL
Server服务启动帐户授予了 SE_MANAGE_VOLUME_NAME
之后才可用。 Windows
Administrator 组的分子有这个权限,并可透过将其他用户添加到 施行卷维护任务 安全策略中来吧那授予此权限。

 

重要

或多或少职能以(如透明数据加密
(TDE))可以阻挡即经常文件初始化。

 

 

 因为这案例被,启动SQL
Server的劳务启动账号也NT
Service\MSSQLSERVER,所以无权力即经常文件初始化的,如果以启动SQL
Server的劳务启动账号变更呢有着管理员权限的域账号,就无见面面世这种气象。另外,如果一定要盖NT
Service\MSSQLSERVER为启动账号,可以按照如下步骤操作:

 

假设朝向一个帐户授予 Perform
volume maintenance tasks 权限:

 

 

  1. 于即将创建备份文件的微机及打开地方安全政策应用程序 (secpol.msc)。

 

  1. 在左窗格中,展开“本地策略”
    ,然后单击“用户权限指派”

 

  1. 以右侧窗格中,双击“执行卷维护任务”。

 

  1. 单击“添加用户要组”
    ,添加用于备份的另用户帐户。

 

  1. 单击“应用”
    ,然后关所有“本地安全政策”
    对话框。

 

图片 2

 

 

安装后,重开SQL
Server服务,然后还原数据库就会见正常,此时之待事件也BACKUPTHREAD,而不是ASYNC_IO_COMPLETION,
如果版本是SQL Server 2012
SP4或以上版本,可以由此下面SQL查看识别是否启用了立即文件初始化

 

 

SELECT  servicename ,

        startup_type ,

        instant_file_initialization_enabled

FROM    sys.dm_server_services; 

 

 

图片 3

 

另外,从SQL
Server 2016 (13.x) 开始,可在装期间给予服务帐户此权限。 如果下命令提示称安装,请加加
/SQLSVCINSTANTFILEINIT 参数,或选中安装为导中“授予
SQL Server 数据库引擎服务实施卷维护任务权限”复选框。

 

 

参考资料:

 

https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-2017

相关文章