Fast Database File Creation for Large Databases in SQL Server 2005

The time needed for creating large and very large database can often take many minutes. SQL Server 2005 has a new feature called Instant File Initialization (IFI) that can speed up the data file initialization of a database.

Database File initialization
The pages in the Data and Log files are initially filled with zeros when one of the following database tasks is performed:

• CREATE DATABASE
• Creating the TEMPDB database at service startup
• Autogrow of databases activity (including TEMPDB)
• RESTORE DATABASE from backup media
• ALTER DATABASE…MODIFY FILE

The filling zero process can affect the performance of SQL Server. SQL Server 2005 has a new feature called Instant File Initialization (IFI). This feature skips zeroing out of the data pages. Instant File Initialization reduces the time during the creation of large and very large databases.

When installing SQL Server 2005, the login account of the service, by default, runs under the Local System account. Microsoft recommends that DBA's use a named Windows user (in the case of workgroup environment) or using a domain user account whe the server is a member of domain.

By default these accounts do not have the right "Perform Volume Maintenance Tasks". In other words, the SQL Server service login account isn't granted the security policy "SE_MANAGE_VOLUME_NAME". As a result SQL Server can't perform instant initialization and perform the filling zero process during database file creation which can take a long time.

Enabling Instant Data File Initialization
Instant initialization is only used for data files (not log files). This feature is available only on Microsoft Windows XP, Windows Server 2003 or later versions. 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.

Configuring a SQL 2005 service Account to Enable Instant File Initialization

1. Log into the SQL Server 2005 machine using an account with administrative privileges.
2. Determin the SQL Server 2005 service account name . You can get the account name from the services applet or from SQL Server Configuration Manager:

• Click Start, Microsoft SQL Server 2005, Configuration Tools, SQL Server Configuration Manager
• Click SQL Server 2005 Services. The services will be displayed in the right pane
• Double-click the SQL Server service, the SQL Server (MSSQLSERVER) properties window is displayed (the service may be with other name, if you use a named service)

• 
  

• In the login tab, you will find the login account name
• Click Start, select Settings and click Control Panel
• In Control Panel, double-click Administrative Tools
• In Administrative Tools, double-click "Local Security Settings"
• In Local Security Settings window, expand Security Settings, and then expand Local Policies
• Select the User Rights Assignment folder. The policies will be displayed in the right pane
• In the right pane, select Perform Volume Maintenance Tasks

• Right click, select properties, the following window appear

• 
  

• On the Local Security Setting tab, click Add User or Group button
• In the Select Users or Groups dialog box, add the account that is used by the SQL Server process and
  Click OK
• Click OK to close the Perform Volume Maintenance Tasks properties window.
• Click start, run, type gpupdate /force , to apply the modification in the security group policy

Conclusion
An increas in thedisk performance in Windows 2003 Server/Windows XP or latter can be increased using a new feature in SQL Server 2005 called Instant File Initialization.

This feature reduces the time of database creation or auto growing activities for the databases. To increase disk performance, enable "Perform Volume Maintenance Tasks" in the security policy for the account running the SQL Server 2005 service.

About Author
Mohamed Hassan is the Software Engineering Manager in Electric Power System (EPS) –Cairo - Egypt.
He has a master degree in computer science, MCSE, MCITP (SQL 2005 administration & development), MCDP, and MCTS.

He has 18+ years experience in databases design and implementation. He is the database infrastructure designer of large scale 24/7 billing systems for utility companies based on Microsoft technologies.
Email: mohamed.hassan@eps-egypt.com