Tuesday, August 16, 2011

Faster backups, restores & data file growths in SQL Server

I recently attended SQLSkills.com Performance Tuning SQL Server. One of the things we learnt about was configuration options that can affect performance. One such option is "Instant File Initialization". Instant File Initialization was introduced in SQL Server 2005. This feature allows file allocation requests to skip zero initialization. By default when new files are created for a certain size (say 1GB), SQL server will write zeros to the entire 1GB file and thereby "remove" any data still persistent on that 1GB space. This can take a huge amount of time when the file sizes are big. By enabling "Instant File Initialization", SQL Server skips the process of writing zeros in this space.

Check if this is enabled: Just one of the ways is to enable trace flag 3004 & 3605 and then create a new database. Once done, look at the SQL server log files:


Run following in SQL Mgmt Studio:
DBCC TRACEON (3004 );
DBCC TRACEON (3605 );


CREATE DATABASE testDB_IFI
ON
( NAME = testDB_IFI_dat,
    FILENAME = 'F:\data\testDB_IFI.mdf',
    SIZE = 10GB,
    MAXSIZE = 50GB,
    FILEGROWTH = 5GB )
LOG ON
( NAME = testDB_IFI_log,
    FILENAME = 'F:\data\testDB_IFI.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB )

DBCC TRACEOFF (3004 );
DBCC TRACEOFF (3605 );


Now look at the SQL Server log file. If you see foll. entries it would mean Instant File Initialization is NOT enabled:
Zeroing F:\data\testDB_IFI.mdf from page 0 to 1310720 (0x0 to 0x280000000)
Zeroing completed on F:\data\testDB_IFI.mdf

Conversely, if you DONT see these entries it means Instant File Initialization is enabled.

NB: Log files CANNOT be instant initialized. So you WILL see foll. entries irrespective of whether you have this enabled or disabled:
Zeroing F:\data\testDB_IFI.ldf from page 0 to 640 (0x0 to 0x500000)
Zeroing completed on F:\data\testDB_IFI.ldf

Enabling Instant File Initialization:
This is essentially a Windows feature and you can enable this feature in SQL Server by giving the user account under which SQL Server is running permission to access this Windows feature. The privilege to give is "Perform Volume Maintenance Tasks".

Resources:
Paul Randall's blog: How to tell if you have instant file initialization enabled and Follow-up post.
Kimberly Tripp's blog: "Instant Initialization: What, Why and How?"
Paul's Misconceptions around file initialization post



No comments: