Database Instant File Initialization

Question

Post reply

Database Instant File Initialization

sql.monk

Mr or Mrs. 500

Points: 570
More actions
April 18, 2016 at 7:35 am

#306644

Hi team ,
Can anyone help me to understand in deep the advantages and disadvantages of Database Instant File Initialization .
Should i enable it if not there or disable it if there ..!!
Just wanted to know what actually Database Instant File Initialization does ...IS it anything related to Database file (.mdf file) growth ?
Thanks in advance
SQLMONK

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

MadAdmin SSChampion Points: 11261 More actions · Answer 1

Enable it.

From Kimberly Tripp

Performance Test with Zero Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
CREATE DATABASE with 20 GB Data file = 14:02 minutes
ALTER DATABASE BY 10 GB = 7:01 minutes
RESTORE 30 GB DATABASE (EMPTY Backup) = 21:07 minutes
RESTORE 30 GB DATABASE (11GB Backup) = 38:28 minutes
Performance Test with Instant Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
CREATE DATABASE with 20 GB Data file = 1.3 seconds
ALTER DATABASE BY 10 GB = .4 seconds
RESTORE 30 GB DATABASE (EMPTY Backup) = 5 seconds
RESTORE 30 GB DATABASE (11GB Backup) = 19:42 minutes

http://www.sqlskills.com/blogs/kimberly/instant-initialization-what-why-and-how/

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

sql.monk Mr or Mrs. 500 Points: 570 More actions · Answer 2

MadAdmin (4/19/2016)
Enable it.
From Kimberly Tripp
Performance Test with Zero Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
CREATE DATABASE with 20 GB Data file = 14:02 minutes
ALTER DATABASE BY 10 GB = 7:01 minutes
RESTORE 30 GB DATABASE (EMPTY Backup) = 21:07 minutes
RESTORE 30 GB DATABASE (11GB Backup) = 38:28 minutes
Performance Test with Instant Initialization
Hardware: Dell Precision 670 Dual Proc (x64) with Dual Core, 4 GB Memory, RAID 1+0 array w/4-142 GB, 15000rpm disks
CREATE DATABASE with 20 GB Data file = 1.3 seconds
ALTER DATABASE BY 10 GB = .4 seconds
RESTORE 30 GB DATABASE (EMPTY Backup) = 5 seconds
RESTORE 30 GB DATABASE (11GB Backup) = 19:42 minutes
http://www.sqlskills.com/blogs/kimberly/instant-initialization-what-why-and-how/

Thanks

Kemberly