数据库实例文件初始化Database Instant File Initialization

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

在本文中,你将了解即时文件初始化,还将了解如何启用它以加速 SQL Server 数据库文件的增长。In this article, you learn about instant file initialization, and how to enable it to speed up growth for your SQL Server database files.

默认情况下,初始化数据和日志文件以覆盖之前删除的文件遗留在磁盘上的任何现有数据。By default, data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. 执行以下操作时,应首先通过零填充(用零来填充)数据和日志文件来初始化这些文件:Data and log files are first initialized by zeroing the files (filling with zeros) when you perform the following operations:

  • 创建数据库。Create a database.
  • 向现有数据库添加数据或日志文件。Add data or log files, to an existing database.
  • 增大现有文件的大小(包括自动增长操作)。Increase the size of an existing file (including autogrow operations).
  • 还原数据库或文件组。Restore a database or filegroup.

SQL ServerSQL Server 中,通过即时文件初始化 (IFI) 可以更快执行前面提到的文件操作,因为它会回收已使用的磁盘空间,而不会用零来填充该空间。In SQL ServerSQL Server, instant file initialization (IFI) allows for faster execution of the previously mentioned file operations, since it reclaims used disk space without filling that space with zeros. 相反,新数据写入文件时会覆盖磁盘内容。Instead, disk content is overwritten as new data is written to the files. 日志文件不能立即初始化。Log files cannot be initialized instantaneously.

启用即时文件初始化Enable instant file initialization

即时文件初始化功能仅在向 SQL ServerSQL Server 服务启动帐户授予了 SE_MANAGE_VOLUME_NAME 之后才可用。Instant file initialization is only available if the SQL ServerSQL Server service startup account has been granted SE_MANAGE_VOLUME_NAME. Windows Administrator 组的成员拥有此权限,并可以通过将其他用户添加到 执行卷维护任务 安全策略中来为其授予此权限。Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

重要

某些功能使用(如透明数据加密 (TDE))可以阻止即时文件初始化。Some feature usage, such as Transparent Data Encryption (TDE), can prevent Instant File Initialization.

备注

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,可在安装期间授予服务帐户此权限。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), this permission can be granted to the service account at install time, during setup.

如果使用命令提示符安装,请添加 /SQLSVCINSTANTFILEINIT 参数,或选中安装向导中“授予 SQL Server 数据库引擎服务执行卷维护任务权限”复选框。If using the command prompt install, add the /SQLSVCINSTANTFILEINIT argument, or check the box Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service in the installation wizard.

要向一个帐户授予 Perform volume maintenance tasks 权限:To grant an account the Perform volume maintenance tasks permission:

  1. 在将要创建数据文件的计算机上打开本地安全策略应用程序 (secpol.msc)。On the computer where the data file will be created, open the Local Security Policy application (secpol.msc).

  2. 在左侧窗格中,展开“本地策略” ,然后单击“用户权限指派” 。In the left pane, expand Local Policies, and then click User Rights Assignment.

  3. 在右侧窗格中,双击“执行卷维护任务”。In the right pane, double-click Perform volume maintenance tasks.

  4. 单击“添加用户或组”并添加可运行 SQL Server 服务的帐户。Click Add User or Group and add the account that runs the SQL Server service.

  5. 单击“应用” ,然后关闭所有“本地安全策略” 对话框。Click Apply, and then close all Local Security Policy dialog boxes.

  6. 重启 SQL Server 服务。Restart the SQL Server service.

  7. 在启动时检查 SQL ServerSQL Server 错误日志。Check the SQL ServerSQL Server error log at startup.

    适用于: SQL ServerSQL Server(从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP4、SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 和 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本开始)。Applies to: SQL ServerSQL Server (Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP4, SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later).

    1. 如果授予 SQL ServerSQL Server 服务启动帐户 SE_MANAGE_VOLUME_NAME,将记录类似如下的消息:If the SQL ServerSQL Server service startup account is granted SE_MANAGE_VOLUME_NAME, an informational message that resembles the following is logged:

      Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

    2. 如果授予 SQL ServerSQL Server 服务启动帐户 SE_MANAGE_VOLUME_NAME,将记录类似如下的消息:If the SQL ServerSQL Server service startup account has not been granted SE_MANAGE_VOLUME_NAME, an informational message that resembles the following is logged:

      Database Instant File Initialization: disabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

    备注

    可以使用 sys.dm_server_services DMV 中的 instant_file_initialization_enabled 列来识别是否启用了即时文件初始化。You can also use the column instant_file_initialization_enabled in the sys.dm_server_services DMV to identify if instant file initialization is enabled.

安全注意事项Security considerations

建议启用即时文件初始化,因为其优势可能远大于安全风险。We recommend enabling instant file initialization as the benefits can outweigh the security risk.

使用即时文件初始化,只有将新数据写入文件时才会覆盖已删除的磁盘内容。When using instant file initialization, the deleted disk content is overwritten only as new data is written to the files. 出于此原因,在数据文件的该特定区域写入其他数据之前,未经授权的主体可能访问已删除的内容。For this reason, the deleted content might be accessed by an unauthorized principal, until some other data writes on that specific area of the data file.

当数据库文件连接到 SQL ServerSQL Server 实例之后,可以通过文件中的随机访问控制列表 (DACL) 来降低此信息泄露的风险。While the database file is attached to the instance of SQL ServerSQL Server, this information disclosure risk is reduced by the discretionary access control list (DACL) on the file. 此 DACL 仅允许 SQL ServerSQL Server 服务帐户和本地管理员访问文件。This DACL allows file access only to the SQL ServerSQL Server service account and the local administrator. 但是,当文件分离以后,可以由不具有 SE_MANAGE_VOLUME_NAME 的用户或服务访问。However, when the file is detached, it may be accessed by a user or service that does not have SE_MANAGE_VOLUME_NAME.

以下情况有类似的注意事项:Similar considerations exist when:

  • 备份数据库时。The database is backed up. 如果未使用适当的 DACL 对备份文件进行保护,则未授权的用户或服务将可以使用删除的内容。If the backup file is not protected with an appropriate DACL, the deleted content can become available to an unauthorized user or service.

  • 使用 IFI 增大文件时。A file is grown using IFI. SQL Server 管理员可能会访问原始页面内容并查看以前删除的内容。A SQL Server administrator could potentially access the raw page contents and see the previously deleted content.

  • 数据库文件托管在存储区域网络中。The database files are hosted on a storage area network. 存储区域网络也可能始终向以预先初始化的形式提供新页面,让操作系统重新初始化页面可能会产生不必要的开销。It is also possible that the storage area network always presents new pages as pre-initialized, and having the operating system re-initialize the pages might be unnecessary overhead.

如果担心可能会泄漏删除的内容,则应执行以下两种或其中一种操作:If the potential for disclosing deleted content is a concern, you should take one or both of the following actions:

  • 请始终确保所有分离的数据文件和备份文件都具有限制性的 DACL。Always make sure that any detached data files and backup files have restrictive DACLs.

  • SQL ServerSQL Server 的实例禁用即时文件初始化。Disable instant file initialization for the instance of SQL ServerSQL Server. 要进行此操作,请从 SQL ServerSQL Server 服务启动帐户撤消 SE_MANAGE_VOLUME_NAME。To do so, revoke SE_MANAGE_VOLUME_NAME from the SQL ServerSQL Server service startup account.

    备注

    禁用该功能将延长数据文件的分配时间,且只会影响在撤消用户权限后创建的文件或增加的文件部分。Disabling will increase allocation times for data files, and only affects files that are created or increased in size after the user right is revoked.

SE_MANAGE_VOLUME_NAME 用户权限SE_MANAGE_VOLUME_NAME user right

可在 Windows Administrative Tools、Local Security Policy 小程序中分配 SE_MANAGE_VOLUME_NAME 用户特权 。The SE_MANAGE_VOLUME_NAME user privilege can be assigned in Windows Administrative Tools, Local Security Policy applet. 在“本地策略”下选择“用户权限分配”并修改“执行卷维护任务”属性 。Under Local Policies select User Right Assignment and modify the Perform volume maintenance tasks property.

性能注意事项Performance considerations

数据库文件初始化过程将零写入进行初始化的文件的新区域。The Database File initialization process writes zeros to the new regions of the file under initialization. 此过程的持续时间取决于初始化的文件部分的大小以及存储系统的响应时间和容量。The duration of this process depends on size of file portion that is initialized and on the response time and capacity of the storage system. 如果初始化需要很长时间,则可能在 SQL Server 错误日志和应用程序日志中看到以下消息。If the initialization takes a long time, you may see the following messages recorded in the SQL Server Errorlog and the Application Log.

Msg 5144
Autogrow of file '%.*ls' in database '%.*ls' was cancelled by user or timed out after %d milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
Msg 5145
Autogrow of file '%.*ls' in database '%.*ls' took %d milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

数据库和/或事务日志文件的长时间自动增长可能导致查询性能问题。A long autogrow of a database and/or transaction log file may cause query performance problems. 这是因为,要求文件自动增长的操作将在文件增长操作期间保留到锁定或闩锁等资源。This is because an operation that requires the autogrowth of a file will hold on to resources such as locks or latches during the duration of the file grow operation. 可能看到分配页面的闩锁上出现长时间等待。You may see long waits on latches for allocation pages. 需要长时间自动增长的操作将显示等待类型 PREEMPTIVE_OS_WRITEFILEGATHER。The operation that requires the long autogrow will show a wait type of PREEMPTIVE_OS_WRITEFILEGATHER.

另请参阅See Also

CREATE DATABASE (SQL Server Transact-SQL)CREATE DATABASE (SQL Server Transact-SQL)