Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted December 4, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

How to Turn on Instant File Initialization

By Greg Larsen

Do you have instant file initialization turned on?  If you do, then allocating your database data pages will go much faster.  Not having instant file initialization turned on slows down the process of adding pages to your database.  It slows down because prior to allocating the data pages to your database, SQL Server needs to zero out the newly allocated pages.

What is Instant File Initialization

Instant file initialization means SQL Server does not initialize your data pages with binary zeroes.    The default configuration for instant file initialization is to not perform instant file initialization.  When instant file initialization is not enabled, SQL server will write binary zeroes to across all data pages that are being allocated to the database.  This means every time SQL Server needs to create a database, or grow a data file the new pages allocated to the database first need to be filled with binary zeroes.  The process of writing binary zeroes takes time.  Therefore, when instant file initialization is not turned it takes SQL Server longer to create or expand a database.  The more space added to a database the longer it takes.

Why does SQL Server Not Perform Instant File Initialization by Default?

By writing binary zeroes to newly allocated disk space SQL Server makes sure pages allocated to the database don’t contain old disk images.  Without overwriting those data pages, a person might be able to peek into the internal structure of a data page and see those bits and bytes of old data stored on the disk.  By not zeroing out those data pages there is a security risk that old data can be viewed by outputting raw database pages.

How to Turn on Instant File Initialization

In order to turn on instant file initialization you need use the following steps:

  1. Logon to your SQL Server with an account that is a member of the local Windows Administrator group. 
  2. Open the Local Security Policy application by running secpol.msc from a command prompt.
  3. In the left pane, click the “Local Policies”:
    Local Policies

  4. In the left pane double-click on the “User Rights Assignment”
    User Rights Assignment

  5. Double-click on “Perform volume maintenance” item.
    Perform volume maintenance

  6. Click the “Add User or Group” button
    Add User or Group

  7. Add the account name for which the SQL Server is running under in the dialog below:
    Add the account name

  8. Click the “Apply” button, and then close the “Local Security Policy” dialog box.

Effects of Turning on Instant File Initialization

It takes time to write binary zeroes to newly allocated database pages, which means users may experience delays while the data pages are being allocated to your database.  By turning on instant file initialization SQL Server doesn’t have to write binary zeroes to the newly allocated data pages, thus eliminating the wait time to write binary zeroes.   

See all articles by Greg Larsen



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.