本文共 11111 字,大约阅读时间需要 37 分钟。
This article gives an overview of Instant File Initialization and its benefits for SQL Server database creation, restoration and file growth.
本文概述了即时文件初始化及其对SQL Server数据库创建,还原和文件增长的好处。
As a database administrator, you must ensure database availability, performance, recovery from disaster. You might have faced a scenario in that new database creation, or database restoration takes a long time. We perform various optimization techniques to ensure excellent performance of instance and database. Instant file initialization is one of the important features that enable you to reduce database creation, data file growth and minimize the database restoration, especially for large databases. SQL Server 2016 onwards, we see an option to grant volume maintenance task permissions to the SQL Server database engine.
作为数据库管理员,您必须确保数据库的可用性,性能以及从灾难中恢复。 您可能在新数据库创建中遇到了一个场景,或者数据库恢复需要很长时间。 我们执行各种优化技术以确保实例和数据库的出色性能。 即时文件初始化是重要的功能之一,使您能够减少数据库的创建,数据文件的增长并最大程度地减少数据库还原,尤其是对于大型数据库。 从SQL Server 2016开始,我们看到一个向SQL Server数据库引擎授予卷维护任务权限的选项。
You might ask a question or two here.
您可以在这里问一两个问题。
We will explore the answers to all these questions in this article. Let’s begin with the introduction of volume maintenance task permissions.
我们将在本文中探讨所有这些问题的答案。 让我们从介绍卷维护任务权限开始。
As a database best practice, you should pre-allocate space for the data and log files. It avoids unnecessary auto-growth events that might cause performance issues in SQL Server.
作为数据库的最佳实践,您应该为数据和日志文件预分配空间。 它避免了可能导致SQL Server性能问题的不必要的自动增长事件。
Suppose you are performing any of the following tasks:
假设您正在执行以下任何任务:
Let’s say we need to create a database with the following configuration:
假设我们需要使用以下配置创建数据库:
Once we issue create database command, it allocates the required space using internal Windows API functions and initializes every byte of this with zero values (0x0). This process is known as zeroing.
发出create database命令后,它将使用内部Windows API函数分配所需的空间,并使用零值(0x0)初始化其中的每个字节。 此过程称为清零。
Suppose we have the following storage byte 1100 1010 before SQL Server occupies this space and starts zeroing process. After the zeroing process, it converts byte into 0000 0000. Therefore, we cannot read old data in any case. It is a good thing, but you need to bear the cost of it.
假设在SQL Server占用此空间并开始清零过程之前,我们具有以下存储字节1100 1010。 归零过程之后,它将字节转换为00000000。因此,在任何情况下我们都无法读取旧数据。 这是一件好事,但是您需要承担这笔费用。
SQL Server provides a feature to skip this zeroing process using this feature. We enable by providing Volume Maintenance task permissions to the SQL Server service account. It takes time to finish this zeroing process. It prevents SQL Server to access old data present in the disk. For a smaller data file or growth, it might take less time, however during large operations, and it takes considerable time.
SQL Server提供了一项功能,可以跳过使用此功能的调零过程。 我们通过向SQL Server服务帐户提供“批量维护”任务权限来启用。 完成调零过程需要时间。 它可以防止SQL Server访问磁盘中存在的旧数据。 对于较小的数据文件或增长的文件,可能需要较少的时间,但是在大型操作中会花费大量的时间。
Starting from SQL Server 2016, you get the option to grant volume performance maintenance permissions to the SQL service account as shown in the image above.
从SQL Server 2016开始,您可以选择向SQL服务帐户授予卷性能维护权限,如上图所示。
If you haven’t enabled it during installation, you can still do it using the following methods.
如果您在安装过程中未启用它,则仍然可以使用以下方法来启用它。
In the Windows search, look for Local Security Policy
在Windows搜索中,查找“本地安全策略”
Go to Security settings -> Local policies -> User rights assignment
转到安全设置->本地策略->用户权限分配
On the right side, you can see policies applicable to the user rights assignment.
在右侧,您可以看到适用于用户权限分配的策略。
In the policies, look for Perform Volume Maintenance Tasks and add SQL Service account in this policy.
在策略中,查找“执行卷维护任务”,然后在该策略中添加SQL Service帐户。
If you are not sure about the SQL Service account, go to SQL Server Configuration Manager and check the service account under that SQL service is running.
如果不确定SQL服务帐户,请转到SQL Server配置管理器并检查该SQL服务正在运行的服务帐户。
We can use DMV sys.dm_server_services to check whether the instant file initialization is enabled on a connected instance or not.
我们可以使用DMV sys.dm_server_services来检查是否在连接的实例上启用了即时文件初始化。
select instant_file_initialization_enabled ,* from sys.dm_server_serviceswhere servicename like 'SQL Server%'
It returns Y that shows this feature is enabled on the connected instance.
它返回Y,表示已在连接的实例上启用此功能。
Before we move further and explore the benefits of it. SQL Server uses Windows API to call for storage operations specified above. It consists of the following operations.
在我们进一步研究它的好处之前。 SQL Server使用Windows API来调用上面指定的存储操作。 它由以下操作组成。
Let’s look at the benefit of Instant file initialization in SQL Server.
让我们看一下SQL Server中即时文件初始化的好处。
Suppose we require to create a database with primary data file initial size 70 GB. First, enable the trace flag 3004 and 3605 to capture additional information in the error log for the zeroing process.
假设我们需要创建一个主数据文件初始大小为70 GB的数据库。 首先,启用跟踪标志3004和3605以捕获错误日志中的其他信息以进行调零过程。
DBCC TRACEON(3004,-1); DBCC TRACEON(3605,-1);
Execute the command to create a database in SQL Server.
执行命令以在SQL Server中创建数据库。
CREATE DATABASE DB_withoutIFI ON PRIMARY (NAME = N'DB_withoutIFI', FILENAME = N'E:\TestDB\DB_withoutIFI.mdf', SIZE = 70GB) LOG ON ( NAME = N'DB_withoutIFI_log', FILENAME = N'E:\TestDB\DB_withoutIFI.ldf', SIZE = 1GB)
In my environment, it took 2 minutes 34 seconds to create a blank database with the above query. Now, look at the error log, and you can see it perform zeroing for the data file and log files. It took 151713ms for the zeroing of a data file.
在我的环境中,用上述查询创建空白数据库花了2分34秒。 现在,查看错误日志,您可以看到它对数据文件和日志文件执行清零。 数据文件归零花费了151713ms。
Let’s go a step further and view the internal data of a page using DBCC Page command. We need to enable trace flag 3604 before executing DBCC PAGE command.
让我们更进一步,使用DBCC Page命令查看页面的内部数据。 在执行DBCC PAGE命令之前,我们需要启用跟踪标志3604。
DBCC TRACEON(3604)GODBCC PAGE (DB_withoutIFI, 1, 1000, 2)
In the following screenshot, we can see all zero bytes for a database page.
在以下屏幕截图中,我们可以看到数据库页面的所有零字节。
We will see high wait type PREEMPTIVE_OS_WRITEFILEGATHER while the zeroing process is in progress.
在进行调零过程时,我们将看到高等待类型PREEMPTIVE_OS_WRITEFILEGATHER。
Let’s create another database with the instant file initialization feature enable. It creates the database immediately, and in the error logs, you do not see any zeroing for the data files.
让我们创建另一个具有即时文件初始化功能的数据库。 它会立即创建数据库,并且在错误日志中,您看不到数据文件的任何清零。
Execute the DBCC page command, and we can see garbage data on the page. Previously, it returned a page with zero bytes.
执行DBCC page命令,我们可以在页面上看到垃圾数据。 以前,它返回的页面的字节数为零。
Similarly, we can test the scenario for backup restoration and file growth as well. Once we restore a database, it performs the following tasks.
同样,我们也可以测试备份还原和文件增长的方案。 还原数据库后,它将执行以下任务。
If we enable the instant file initialization on SQL instance, it avoids zero initialization steps, and restoration is comparatively fast. In the following screenshot, you can see the following.
如果我们在SQL实例上启用即时文件初始化,则可以避免初始化步骤为零,并且恢复速度相对较快。 在以下屏幕截图中,您可以看到以下内容。
You might see it is a minimal improvement but think in terms of an extensive database. You might have a database in TB’s, and restoration takes a very long time. You can improve restoration performance if the destination instance is enabled with instant file initialization.
您可能会看到这是一个最小的改进,但是要考虑一个庞大的数据库。 您可能在TB中有一个数据库,并且恢复需要很长时间。 如果使用即时文件初始化启用了目标实例,则可以提高还原性能。
Trace flag 1806 disabled the instant file initialization behavior even if we have provided permissions to perform a volume maintenance task
跟踪标志1806禁用了即时文件初始化行为,即使我们已提供执行卷维护任务的权限
You can enable it using the DBCC traceon(1806,-1) command
您可以使用DBCC traceon(1806,-1)命令启用它
We should enable it only if we require to do so. We can disable this trace flag using the DBCC TRACEOFF(1806) command
仅在需要时才应启用它。 我们可以使用DBCC TRACEOFF(1806)命令禁用此跟踪标志
Once we run the DBCC CHECKDB command on a database, SQL Server creates an internal database snapshot and initializes it with zero. It does not any impact of the enabled instant file initialization feature
在数据库上运行DBCC CHECKDB命令后,SQL Server将创建一个内部数据库快照并将其初始化为零。 它对启用的即时文件初始化功能没有任何影响
In the following screenshot, we can see that zeroing once we execute the CHECKDB command to check database consistency
在下面的屏幕快照中,我们可以看到,一旦执行CHECKDB命令检查数据库一致性,便会清零。
Instant file initialization provides better performance for the data file creation, growth, and database restoration. We can read the garbage data because SQL Server does not follow the zeroing process for the data files. There might be some security concerns about the garbage data; however, most of the DBA uses this approach. You should understand the security risk and make a decision based on the requirement.
即时文件初始化为数据文件创建,增长和数据库还原提供了更好的性能。 我们可以读取垃圾数据,因为SQL Server不遵循数据文件的清零过程。 垃圾数据可能存在一些安全问题; 但是,大多数DBA都使用这种方法。 您应该了解安全风险并根据需求做出决定。
翻译自:
转载地址:http://hfiwd.baihongyu.com/