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)(Service)\MSSQLSERVER,所以并未权力即时文件初步化的,假使将启动SQL
Server的劳动启动账号改为有着管理员权限的域账号,就不会现出这种情状。另外,假如一定要以NT
瑟维斯\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

相关文章