使用即时文件初始化加速SQL Server

Instant File Initialization Speeds SQL Server

使用即时文件初始化加速SQL Server

Database Administration,Database Maintenance | July 21, 2010 | 9:20 am

Sometimes, its just the smallest of details that can make all the difference. For example, on my test system (see the end of this posting for a description), I created a new 50GB database. The database creation process took about 5 minutes and 50 seconds to complete.

四两拨千斤的现象是屡见不鲜的。例如：在我的测试系统中（请看本文结尾处的描述），我新建了一个50G的数据库，该创建过程花费了5分 50秒时间才完成。

Next, I populated the database with over 61 million rows of data, which virtually occupied all of the available space within the newly created database. After that, I backed up the database using SSMS, and then I deleted the original database.

接着，我向该数据库填充了超过61,000,000条数据——这些数据几乎占据了该新建数据库的所有可用空间。然后我使用SSMS备份了这个数据库并将其删掉。

At this point, I restored the database from the backup using SSMS. Below, you see the typical Restore Database screen.

接下来，我使用SSMS恢复了该数据库。下面是典型的数据库恢复界面。

 

At the bottom, right-hand side of the screen in the Progress box, notice the “Executing (0%)” indicator. Between the time I clicked the OK button to begin the restore, and when the “Executing (0%)” counter began to move, it took about 5 minutes and 50 seconds. At that point, the counter began to increment and the database was restored.

请注意位于界面底部右侧的进度框中的“完成(%)”标识。从我点击确定按钮到开始备份，也就是即“完成(%)”计数器开始计数，花费了5分 50秒。此时，计数器开始计数，数据库完成恢复。

Now I make one very small change to my SQL Server instance (I’ll describe it in just a moment), and then I repeat the above steps (after deleting the database I just restored). First, I created a new 50GB database. This time, instead of taking 5 minutes and 50 seconds to create the database, it takes just under 2 seconds, a savings of about 5 minutes and 48 seconds. Next, I populated the database with the same amount of data as before, backed it up, and then deleted the original file. When I restored the database this time around, instead of having to wait 5 minutes and 50 seconds before the backup began to restore, I only had to wait just under 2 seconds. In both of these cases, I saved a significant amount of time.

现在我对SQL Server实例做一个小小的改动（稍后我将详述该改动），然后重复上述步骤（删除了我刚才恢复的数据库）。首先，我新建一个50G的数据库。这次，不再需要花费5分50秒来创建数据库，仅仅花费了2秒的时间，节省了5分48秒！接着我向数据库中填充此前填充同样多的数据，备份并删除原先的数据库文件。当我这次恢复这个数据库时，在备份开始之前不再需要等待5分50秒，我只等待了2秒。在这两个操作中，我节省了大量的时间。

So what was the very small change that I made, and why did it radically reduce the amount of time for database creation and database restoration to occur? I turned instant file initialization on.

那么我所做的小小改动是什么呢，而为什么这个改动能够显著缩短数据库创建和恢复时间呢？——我打开了即时文件初始化功能。

 

What is Instant File Initialization?

什么是即时文件初始化？

In my first two examples, before instance file initialization was turned on, the reason it took so long for the database to be created, or the database to be restored (before a database can be restored, its space must first be pre-allocated, much like creating a new database), SQL Server had to go to every page in the 50 GB database and zero each one of them out. It can take a lot of time for SQL Server to go to every 8K page in a file (especially very large files) and physically zero out each page. When instant file initialization is turned on, SQL Server doesn’t have to zero out every 8K page that has been allocated. Instead, the space is just allocated to SQL Server by the operating system in one fell swoop, which is a very quick process, potentially saving you a great deal of time.

在我前面的两个示例中，打开即时文件初始化功能之前，系统花费了如此长的时间创建或者恢复数据库（在开始恢复数据库前，数据库空间必须提前分配，正如新建数据库一样）。原因是SQL Server必须到每个数据页中进行数据清零。这将会花费SQL Server大量时间来访问文件中每个大小为8K的数据页（尤其当文件非常巨大时）并将每页数据清零。当即时文件初始化功能打开时，SQL Server无需将分配的每个8K大小的数据页进行数据清零。相应的，只需操作系统的一个动作即可完成对SQL Server的空间分配，整个过程非常迅速，这样可能会节省你大量的时间。

How Do You Turn Instant File Initialization On?

如何打开即时文件初始化功能？

Unlike most configuration features in SQL Server, there is no on/off switch for instant file initialization. Instead, you have to assign a specific user right to the SQL Server Service (mssqlserver) account. Here’s what you need to do to turn on instant file initialization.

不像大多数SQL Server中的功能设置，即时文件初始化功能并没有打开/关闭开关。相反的，你必须为SQL Server（MSSQL）账户分配一个特定的用户权限。下面是打开即时文件初始化功能所需的操作。

First of all, to use instant file initialization with SQL Server in a production environment, you must be using some combination of:

• Windows Server 2003 or
• Windows Server 2008 or
• Windows Server 2008 R2

and using:

• SQL Server 2005 (any edition) or
• SQL Server 2008 (any edition) or
• SQL Server 2008 R2 (any edition)

First of all, to use instant file initialization with SQL Server in a production environment, you must be using some combination of:

首先，要在生产环境中使用SQL Server即时文件初始化功能，你必须使用下列组合：

• Windows Server 2003 或者
• Windows Server 2008 或者
• Windows Server 2008 R2

并使用：

• SQL Server 2005 (任意版本) 或者
• SQL Server 2008 (任意版本) 或者
• SQL Server 2008 R2 (任意版本)

Second, you must assign the SQL Server Service (mssqlserver) a special user right called “Perform volume maintenance tasks”. To do this, start the Local Security Policy tool (you must be a local administrator to perform this task), then drill down to Security Settings | Local Policies | User Rights Assignment | Perform volume maintenance tasks, as you see in the screenshot below.

第二，你必须分配给SQL Server服务（MSSQL）一个称作“执行卷维护任务”的特殊用户权限。要分配此权限，启动本地安全策略工具（你必须是本地系统管理员才能执行此任务），接着进入安全设置|本地策略|用户权限分配|执行卷维护任务，如下图截屏所示。

Once you have located “Perform volume maintenance tasks”, right-click on it and select “Properties”, and the “Perform volume maintenance tasks Properties” screen appears. Click on “Add User or Group” and then proceed through the remaining screens until you select the account that is being used as the service account for SQL Server. In the screen shot below, notice that I have added the BRADMCGEHEE/sqlserverservice account to this user rights assignment. This is the user account I use on my test server to run my SQL Server instance.

一旦找到“执行卷维护任务”，右击并选择“属性”，“执行卷维护任务”界面会显示出来。点击“添加用户或组”并完成剩余界面中的设置直到你选择了SQL Server服务所使用的用户账户。在下面的截屏中，注意我将BRADMCGEHEE/sqlserverservice加入到了该权限的分配列表中。这正是我测试服务器上运行SQL Server服务的用户账户。

Once the SQL Server service account has been assigned this user right, you will have to restart the SQL Server service (of course, only when it is not being used), and from this point forward, instant file initialization is turned on for all MDF files in your SQL Server instance.

一旦为SQL Server账户分配了这个用户权限，必须重启SQL Server服务（当然是在SQL Server不再被使用的情况下）。重启后，即时文件初始化功能将为你的SQL Server实例中的所有MDF文件打开。

Note: If your SQL Server service account is a member of the local administrators group, then the account already has the “Perform volume maintenance tasks” user right and you don’t need to assign it again.

注意：如果你的SQL Server用户是本地系统管理组中的成员，那么这个账户就已经拥有“执行卷维护任务”用户权限，因而无需再为该用户分配此权限。

Why Isn’t Instant File Initialization Turned On by Default?

为什么即时文件初始化没有被默认打开？

When a SQL Server instance is first installed, one of the things you must enter is a SQL Server service account. If you follow the best practice and select a domain user account to be used as the SQL Server service account, the setup process automatically assigns the domain user account with only just enough rights and permissions to run SQL Server. The “Perform volume maintenance tasks” user right is not automatically assigned during installation because it is not required to run SQL Server, and because allowing the service account to have this additional user right introduces a very small security risk.

在安装SQL Server实例时，有一项工作是输入SQL Server账户。如果你遵循最佳实践而选择域用户账户作为SQL Server账户，安装程序将自动为该域用户账户分配仅满足其运行SQL Server的足够权限。即时文件初始化用户权限并没有在安装过程中分配因为运行SQL Server无需该权限，而且因为允许服务账户拥有该项额外的用户权限会带来些许的安全风险。

Oh no, a security risk! Well, not really much of a security risk. Here’s the possible security risk scenario. The disk that is being used to create the new database on has been used for storing data that has been previously deleted. As you may know, when data is deleted from disk by the operating system, it really is not physically deleted; the space holding the data is just marked as being available. At some point, the older data will be overwritten with new data. This occurs all the time on millions of computers throughout the world every day. And as such, any data that has been marked for deletion, but not yet overwritten, is potentially available for access if you have the right tools and know what you are doing. In fact, undelete software uses this to recover data that has been accidently deleted.

哦，不要，有安全风险！不过还好，不是太大安全风险。下面是可能出现安全风险的场景。用于新建数据库的硬盘是用来存储前文中删除掉的数据。你大概知道，当数据被操作系统从硬盘上删掉时，数据并没有被真正的物理删除；仅仅是存储数据的空间被标记为可用。有时，旧的数据会被新的数据重写。这种现象在世界上多达数百万的计算机中每天都无时不刻的发生着。正是这样，任何被标记为删除而尚未被重写的数据都有可能通过适当的工具进行访问进而窥知你的工作。实际上数据恢复软件正是基于这一点来恢复无意删掉的数据。

When instant file initialization is not turned on, and when SQL Server allocates space for an MDF file, each of the pages allocated for the database is zeroed out, which removes the older data, in theory, preventing it from being accessed. I say “in theory” because there are computer forensics techniques that can even recover data that has been overwritten, but that discussion is really not applicable here.

当即时文件初始化功能打开时，在SQL Server为MDF文件分配空间的时候，为数据库分配的所有数据页都被清零，也就是说旧的数据被移除了，理论上旧的数据将无法再访问了。我说“理论上”是因为仍有计算机刑侦手段可以将即使已经被重写的数据恢复回来，不过关于这个问题的讨论这里就不罗嗦了。

So if instant file initialization is turned on, there is a very slight risk that someone could go to the pages allocated for the new database and read any older data that still may exist there. This is essentially a non-issue in virtually every organization, other than those that require very high security. But because of this potential security issue, instant file initialization is not turned on by default.

因此，如果即时文件初始化功能被打开，有人就有可能(一点点可能)进入为新建数据库分配的数据页中读取可能仍然存在的旧的数据。不仅是那些对系统安全要很高的组织，在每一个现实的组织中，这个问题从本质上讲不是什么问题。不过还是因为这个潜在的安全问题，默认状态下即时文件初始化功能没有打开。

If instant file initialization is turned on, and pages are not zeroed out when the database is initially created, SQL Server will automatically overwrite any data that might have been on those pages when SQL Server needs that space.

如果即时文件初始化功能被打开，在数据库初始化时数据页也没有进行清零，当SQL Server需要空间时，SQL Server将会自动重写可能位于那些数据页上的任何数据。

When Is Instant File Initialization Used?

何时会使用即时文件初始化？

If instant file initialization is turned on, it is used in all of these cases:

• When a database is first created
• When a an existing database’s size is manually increased
• When tempdb is recreated each time SQL Server is restarted
• When autogrowth kicks in
• When backups are restored (as the space has to be pre-allocated before a restore can occur) 

如果即时文件初始化功能被打开，在以下情形中该功能将被使用:

• 数据库初次创建时
• 手动增长数据库大小时
• 每次SQL Server启动tempdb被重建时
• （数据库）开始自增时
• 当恢复备份时(由于恢复开始前数据库空间需要重新分配) 

Instant file initialization only affects MDF and NDF files, not LDF files. In other words, transaction log files can’t take advantage of instant file initialization. This is because log files are circular in nature and must be zeroed out, as random data in transaction log pages can be problematic. In my earlier test, when I created a new 50 GB database, the MDF file was 50 GB and the log file was only 1 MB. If I had created a large log file (which is not uncommon), it would have taken awhile for the log to be created, although the MDF file would have been instantly created. This is also true when you manually increase the size of a log file, or when log file autogrowth occurs. In other words, don’t expect to have all of your databases (MDF and LDF files) created in less than 2 seconds like in my test. While the MDF will be created virtually instantly, the log file may take awhile to be created.

即时文件初始化仅对MDF文件和NDF文件凑效而与LDF文件无关。也就是说，事务日志文件不能从即时文件初始化功能中获益。这是因为日志文件通常情况下都是循环使用的，而必须将随机数据清零对于事务日志数据页是有困难的。在我前面的测试中，当我创建50G的数据库时，MDF文件是50G而日志文件仅有1MB。如果我创建了一个大的日志文件（通常没有人这么做），尽管MDF文件将被很快创建，创建日志文件仍然会花费很长的时间。这种耗时在手动增大日志文件或者日志文件自动增涨时依然会发生。换言之，不要期望创建所有数据库（MDF和LDF文件）都能像我测试的那样，时间少于2秒。尽管MDF能迅速创建，日志文件却得费点功夫。

When I was working with SQL Server 2000 a few years back, which does not support instant file initialization, one of the things that annoyed me the most when restoring large databases was waiting for the database space to be allocated before the restore actually began. During emergency database restores, this wasted a lot of precious time, preventing me from getting the database back into production as fast as I would have preferred. If you aren’t using instant file initialization today, you are facing this same problem. That’s why I recommend all SQL Server 2005/2008 instances have instant file initialization turned on. The time saved when restoring databases is the best reason to use instant file initialization.

几年前，当我工作在SQL Server 2000上而无即时文件初始化功能时，一件让我最为头大的事情就是每当我恢复大数据库，在恢复实际开始之前，数据库空间分配带来的漫长等待，这简直就是浪费宝贵时间！无法让我像我想要的那样尽可能快的将数据库恢复到生产环境。今天如果你没有使用即时文件初始化，你会面临同样的问题。这也正是我推荐所有的SQL Server2005/2008实例将即时文件初始化功能打开的原因了。恢复数据库时节省时间无疑是使用即时文件初始化的首要原因了。

Check to See if Your SQL Server Instances Have Instant File Initialization Turned On

检查你的SQL Server实例是否打开了即时文件初始化功能

Hopefully, by now, you see the benefits of using instant file initialization. Assuming that you don’t already know if instant file initialization is turned on or off on the SQL Servers your manage, I challenge you to check and see, and if you find it turned off, turn it on and reap its many benefits.

 至此，希望你已经知道使用即时文件初始化功能大有裨益。假定在SQL Server管理中，你尚不知晓如何得知即时文件初始化功能是否打开，我希望你查看查看，如果你发现该功能没有打开，请打开该功能并从中获益吧。

Test Hardware

测试硬件：

·         Dell T610 Tower, with a single, 6-core CPU (Intel Xeon X5670, 2.93 Ghz, 12M Cache, HT, 1333MHz FSB); 32GB 1333MHz RAM; a PERC H700 RAID controller; two 146GB 15K SAS Drives; one dual-port HBA (to connect to the DAS); and dual network connections. Hyper-threading turned off.

·         One PowerVault MD3000 DAS with two, dual-port controllers, and 15 146GB 15K SAS drives. MDF files located on RAID 10 array with 10 spindles, LDF files on RAID 10 array with 4 spindles, backup drive on a single spindle.

Read more: http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/#ixzz110HlsUO1
Under Creative Commons License: Attribution