{"id":749,"date":"2016-03-08T11:07:17","date_gmt":"2016-03-08T19:07:17","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=749"},"modified":"2016-06-22T08:54:01","modified_gmt":"2016-06-22T15:54:01","slug":"instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/","title":{"rendered":"Instant File Initialization: Easier to Enable in SQL Server 2016 (and some updated numbers)"},"content":{"rendered":"

The ability to have SQL Server data files skip zero initialization when they are created or grown has been available since SQL Server 2005.\u00a0 By default, when you create a new data file in SQL Server, or extend the size of an existing one, zeroes are written to the file.\u00a0 Depending on the size of the file or its growth, and the type of storage, this can take a while.\u00a0 With Instant File Initialization (IFI), space is allocated for the data file but no zeroes are written.\u00a0 Prior to SQL Server 2016, to enable this feature you had to edit the Local Security Policy to give the account that runs the SQL Server service the “Perform volume maintenance tasks” right (from Start | Run, type secpol, within the Local Security Policy expand Local Policies, then User Rights Assignment).\u00a0 This was a task that DBAs had to perform separate from the SQL Server installation (or have a server admin do it for them), and if you did not make the change before installing SQL Server, then it required restarting SQL Server after making the change for it to take affect.\u00a0 This has changed with the SQL Server 2016 installation, as you can now select the option “Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service” when you specify the service accounts, and this will grant the right to the service account at that time.<\/p>\n

\"Enable<\/a>
Enable Instant File Initialization during SQL Server 2016 installation<\/figcaption><\/figure>\n

There is a potential security risk to using this feature, which Kimberly discusses in her Instant Initialization – What, Why and How?<\/a> post.\u00a0 The information presented in her post is still valid and worth the read, but Glenn and I did re-run some tests recently, just to get some current numbers to show the benefits of IFI.\u00a0 We ran the same four tests that Kimberly ran way back in 2007 (!) on four different sets of storage: two sets of 15K disks (one in a RAID10 array, the other in a RAID1 array) and two sets of flash storage (FusionIO cards).\u00a0 More information on the storage at the end of the post.\u00a0 The tests were:<\/p>\n

 <\/p>\n\n\n\n\n\n\n
1<\/td>\nCreate 20GB database<\/td>\n<\/tr>\n
2<\/td>\nGrow existing database by 10GB<\/td>\n<\/tr>\n
3<\/td>\nRestore 30GB empty database<\/td>\n<\/tr>\n
4<\/td>\nRestore 30GB database with 10GB data<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

The tests were run on two different physical servers, both running SQL Server 2014.\u00a0 Details for each storage system are listed below for reference, and the test results were as we expected:<\/p>\n

 <\/p>\n

\"Duration<\/a>
Duration for file modification or database restore, with and without IFI<\/figcaption><\/figure>\n

 <\/p>\n

The time to zero out a file and write data is a function of sequential write performance on the drive(s) where the SQL Server data file(s) are located, when IFI is not enabled.\u00a0 When IFI is<\/em> enabled, creating or growing a data file is so fast that the time is not of significant consequence.\u00a0 The time it takes to create or grow a value varies in seconds between 15K, SSD, flash, and magnetic storage when IFI is enabled.\u00a0 However, if you do not enable IFI, there can be drastic differences in create, grow, and restore times depending on storage.<\/p>\n

Storage Details:<\/p>\n

    \n
  • 15K RAID10 = Six (6) 300GB 15K disks in RAID 10<\/li>\n
  • Flash Drive1 = 640GB Fusion-io ioDrive Duo<\/li>\n
  • Flash Drive2 = 2.41TB Fusion-io ioDrive2 Duo<\/li>\n
  • 15K RAID1 = Two (2) 300GB Seagate Savvio 15K drives in RAID 1<\/li>\n<\/ul>\n

    Note: This post was edited on April 13, 2016, to clarify the storage configuration based on a helpful comment.<\/p>\n","protected":false},"excerpt":{"rendered":"

    The ability to have SQL Server data files skip zero initialization when they are created or grown has been available since SQL Server 2005.\u00a0 By default, when you create a new data file in SQL Server, or extend the size of an existing one, zeroes are written to the file.\u00a0 Depending on the size of […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[45],"tags":[],"yoast_head":"\nInstant File Initialization: Easier to Enable in SQL Server 2016 (and some updated numbers) - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Enabling Instant File Initialization in SQL Server 2016 is easier than ever, thanks to a new option in the installation dialog.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Instant File Initialization: Easier to Enable in SQL Server 2016 (and some updated numbers) - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Enabling Instant File Initialization in SQL Server 2016 is easier than ever, thanks to a new option in the installation dialog.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2016-03-08T19:07:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-06-22T15:54:01+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2016\/03\/screen-shot.jpg\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/\",\"name\":\"Instant File Initialization: Easier to Enable in SQL Server 2016 (and some updated numbers) - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2016-03-08T19:07:17+00:00\",\"dateModified\":\"2016-06-22T15:54:01+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Enabling Instant File Initialization in SQL Server 2016 is easier than ever, thanks to a new option in the installation dialog.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Instant File Initialization: Easier to Enable in SQL Server 2016 (and some updated numbers)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Instant File Initialization: Easier to Enable in SQL Server 2016 (and some updated numbers) - Erin Stellato","description":"Enabling Instant File Initialization in SQL Server 2016 is easier than ever, thanks to a new option in the installation dialog.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/","og_locale":"en_US","og_type":"article","og_title":"Instant File Initialization: Easier to Enable in SQL Server 2016 (and some updated numbers) - Erin Stellato","og_description":"Enabling Instant File Initialization in SQL Server 2016 is easier than ever, thanks to a new option in the installation dialog.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/","og_site_name":"Erin Stellato","article_published_time":"2016-03-08T19:07:17+00:00","article_modified_time":"2016-06-22T15:54:01+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2016\/03\/screen-shot.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/","name":"Instant File Initialization: Easier to Enable in SQL Server 2016 (and some updated numbers) - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2016-03-08T19:07:17+00:00","dateModified":"2016-06-22T15:54:01+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Enabling Instant File Initialization in SQL Server 2016 is easier than ever, thanks to a new option in the installation dialog.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/instant-file-initialization-easier-to-enable-in-sql-server-2016-and-some-updated-numbers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Instant File Initialization: Easier to Enable in SQL Server 2016 (and some updated numbers)"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/749"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=749"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/749\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=749"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=749"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=749"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}