Instant File Initialization

  • Divine Flame

    SSCoach

    Points: 15940

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


    Sujeet Singh

  • Alan Burstein

    SSC Guru

    Points: 61006

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

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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: 354

    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: 15940

    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: 15940

    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: 1222

    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: 1222

  • Divine Flame

    SSCoach

    Points: 15940

    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

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

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