I have been testing out some interesting items that were mentioned in sessions at the PASS Summit in Seattle last month. One of these was the concept of Instant File Initialization (IFI) for large database files. Even though this feature has been around since Windows Server 2003 and SQL Server 2005, I decided to include it in my testing of SQL11 CTP1, code-named “Denali” that was handed out at the conference to see if IFI still works the same way.
In SQL Server 2000 and before, creating a large empty database was painfully slow as the database engine and file system worked together to “zero out” the newly allocated data files using “Zero Initialization.” Surprisingly, this is still the default behavior in SQL Server 2005, 2008 and now SQL11 CTP1. A simple example: I created a 10GB database on a W2K8 test server and it took 42 seconds – way too long. To verify that Zero Initialization was being used, I had turned on Traceflags 3004 and 3605 using the –T switch that gives “Zeroing” messages in the Error Log. SQL Server Configuration Manager in Denali now has a “Startup Parameters” tab under the SQL Server Service Properties window. Now you can enter additional Startup Parameters using this tab instead of the Advanced tab, not a big deal but it saves you entering tricky semi-colons etc. Of course, a restart of the SQL Server service is needed for the trace flags to take. The Error Log then shows that the new .MDF file “Zeroing” takes 35 seconds.
To turn on “Instant File Initialization” (or IFI) you need to add your SQL Server service account to the following Windows User Rights Assignment: “Perform Volume Maintenance Tasks”. To do this use the Local Security Policy tool under Administrative Tools. It’s easiest to use the Windows group created by the SQL Server installation named: SQLServerMSSQLUser$NY-SQL-01 $MSSQLSERVER which contains the service account (where NY-SQL-01 is my test server). The nice thing about using the automatically generated group is that if you change the service account later you will not have to go searching for all the references to the individual account – the group name will never change. Another restart of the SQL Server service is required to pick up the new rights and then recreating my 10GB database takes 7 seconds, a sixth of the time – an 85% time savings. Looking at the Error Log, I can still see the “Zeroing” messages for the LDF file but there are no such messages for the MDF file – it’s using IFI, not Zero Initialization. SQL11 QED.
The reason this setting is not the default, apparently, is that potentially it constitutes a super-contrived security vulnerability. If you had some previously deleted sensitive files on the same disk that you were using for the new database, and the new data files effectively overlaid those deleted files then IFI would not zero-out the data pages and someone could potentially look at 8K fragments of the sensitive data using an undocumented command such as DBCC PAGE. Of course, the question arises as to why the sensitive files were not deleted effectively in the first place but that’s another story.