Instant File Initialization

Question

Instant File Initialization

Divine Flame

SSCoach

Points: 15943
More actions
July 10, 2014 at 12:06 am

#309881

Comments posted to this topic are about the item Instant File Initialization
Sujeet Singh

Viewing 8 posts - 1 through 7 (of 7 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Great article, 5 stars. I new nothing about this. Learned something new today already!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

rick.foster Old Hand Points: 360 More actions · Answer 2

Does it save time when the file is expanded? It is a best practice to keep the file increase size relatively small because of the hit on performance when the database file grows, does Instant Initialize help in this regard? Thanks, Rick

Divine Flame SSCoach Points: 15943 More actions · Answer 3

rick.foster (7/10/2014)
Does it save time when the file is expanded? It is a best practice to keep the file increase size relatively small because of the hit on performance when the database file grows, does Instant Initialize help in this regard? Thanks, Rick

Yes, IFI saves time when the file is expanded (either manually or by AutoGrowth).

For example, if currently your data file size is 40 GB & you want to expand it to 70 GB proactively, IFI helps you there by reducing the processing time.

Sujeet Singh

Divine Flame SSCoach Points: 15943 More actions · Answer 4

Alan.B (7/10/2014)
Great article, 5 stars. I new nothing about this. Learned something new today already!

Thanks for your kind words Alan. Glad you find it useful.

Sujeet Singh

Thomas Keller Ten Centuries Points: 1246 More actions · Answer 5

4 stars! You should mention how to confirm that it took effect, by looking at the errorlog after creating a database, to see if it no longer says "Zeroing completed". There is a script to check for this, using sp_readerrorlog, at: http://sqlblog.com/blogs/tibor_karaszi/archive/2013/10/30/check-for-instant-file-initialization.aspx

Note it also requires trace flags 3004 and 3605 to make that information actually show up in the errorlog, see: http://www.rdx.com/Blog/dba_tips/2011/03/undocumented-trace-flags-inside-the-restore-process/[/url]

Thomas Keller Ten Centuries Points: 1246 More actions · Answer 6

Please consider voting for these related Microsoft Connect feature requests (I just added #917919):

https://connect.microsoft.com/SQLServer/feedback/details/917919/when-instant-file-initialization-is-in-effect-zeros-should-be-lazy-written

https://connect.microsoft.com/SQLServer/feedback/details/809901/add-isinstantfileinitializationenabled-islockpagesinmemory-to-policy-facets

Divine Flame SSCoach Points: 15943 More actions · Answer 7

Thomas Keller (7/11/2014)
4 stars! You should mention how to confirm that it took effect, by looking at the errorlog after creating a database, to see if it no longer says "Zeroing completed". There is a script to check for this, using sp_readerrorlog, at: http://sqlblog.com/blogs/tibor_karaszi/archive/2013/10/30/check-for-instant-file-initialization.aspx
Note it also requires trace flags 3004 and 3605 to make that information actually show up in the errorlog, see: http://www.rdx.com/Blog/dba_tips/2011/03/undocumented-trace-flags-inside-the-restore-process/[/url]

Thanks for your valuable comments Thomas. When writing this article, I initially thought to provide this information in the article, however I skipped that later for the simplicity of article.

My idea was that after reading the article people will be curious about IFI & they will find out about these undocumented DBCC commands very soon.

Sujeet Singh