{"id":4773,"date":"2015-05-18T12:39:26","date_gmt":"2015-05-18T10:39:26","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/"},"modified":"2015-05-18T12:39:26","modified_gmt":"2015-05-18T10:39:26","slug":"variations-on-1m-insert-2-write-commit","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/","title":{"rendered":"Variations on 1M insert (2): write commit"},"content":{"rendered":"

In this blog post, I will try to do the same than my colleagues about Oracle<\/a> and for PostgreSQL<\/a>. As a reminder, we\u2019ve seen in my previous blog post<\/a> that SQL Server is designed to commit transactions implicitly by default and inserting 1M rows in this case may have a huge impact on the transaction log throughput. Each transaction is synchronously committed to the transaction log. In this blog post, we\u2019ll see a variation of the previous test<\/p>\n

Indeed, since SQL Server 2014 version, it is possible to change a little bit this behaviour to improve the overall performance of our test by using a feature called delayed durability transaction. This is a performance feature for sure but you will have to trade durability for performance. As explained here<\/a>, SQL Server uses a write-ahead logging protocol (WAL) and using this new feature will temporarily suspend this requirement.<\/p>\n

So, let\u2019s perform the same test but this time I will favour the overall throughput performance by using delayed durability option.<\/p>\n

alter database demo set delayed_durability = allowed;
\ngo
\nDECLARE @i INT = 1;
\nWHILE @i <= 1000000
\nBEGIN
\nbegin tran;
\nINSERT INTO DEMO VALUES (@i, CASE ROUND(RAND() * 10, 0) WHEN 1 THEN 'Marc' WHEN 2 THEN'Bill' WHEN 3 THEN 'George' WHEN 4 THEN 'Eliot' WHEN 5 THEN 'Matt' WHEN 6 THEN 'Trey' ELSE'Tracy' END, RAND() * 10000);
\ncommit tran with (delayed_durability = on);
\nSET @i += 1;
\nEND<\/code><\/p>\n

— 00:00:20 \u2013 Heap table<\/strong><\/div>\n
— 00:00:19 \u2013 table with clustered index<\/strong><\/div>\n

If I refer to my first test results<\/a> with implicit commit behaviour, I may effectively notice a big performance improvement (86%). You may also note that I used this option at the transaction level after enabling delayed durability at the database level but in fact you have other possibilities<\/a>. Depending on your context, you may prefer either to enable or to force this option directly at the database level.<\/p>\n

Do I have to enable it? If your business is comfortable making throughput versus durability and this option improves the overall performance, go ahead but keep in mind that you also have others ways to improve your transaction log throughput before enabling this option (please read the Paul Randal\u2019s blog posts<\/a>)<\/p>\n

Want to meet in one day our experts from all technologies? Come to our Event In-Memory: boost your IT performance!<\/a>\u00a0where we talk about SQL Server, Oracle and SAP HANA.<\/p>\n

By David Barbarin<\/p>\n","protected":false},"excerpt":{"rendered":"

In this blog post, I will try to do the same than my colleagues about Oracle and for PostgreSQL. As a reminder, we\u2019ve seen in my previous blog post that SQL Server is designed to commit transactions implicitly by default and inserting 1M rows in this case may have a huge impact on the transaction… <\/p>\n","protected":false},"author":26,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[99],"tags":[67,52],"class_list":["post-4773","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-performance","tag-sql-server-2014"],"yoast_head":"\nVariations on 1M insert (2): write commit - dbi services | Blog<\/title>\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.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Variations on 1M insert (2): write commit\" \/>\n<meta property=\"og:description\" content=\"In this blog post, I will try to do the same than my colleagues about Oracle and for PostgreSQL. As a reminder, we\u2019ve seen in my previous blog post that SQL Server is designed to commit transactions implicitly by default and inserting 1M rows in this case may have a huge impact on the transaction… \" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2015-05-18T10:39:26+00:00\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-insert-2-write-commit\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-insert-2-write-commit\\\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/857c83b2f1d7d3e7c113ed40edf63294\"},\"headline\":\"Variations on 1M insert (2): write commit\",\"datePublished\":\"2015-05-18T10:39:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-insert-2-write-commit\\\/\"},\"wordCount\":337,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"keywords\":[\"Performance\",\"SQL Server 2014\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-insert-2-write-commit\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-insert-2-write-commit\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-insert-2-write-commit\\\/\",\"name\":\"Variations on 1M insert (2): write commit - dbi services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2015-05-18T10:39:26+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-insert-2-write-commit\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-insert-2-write-commit\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-insert-2-write-commit\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Variations on 1M insert (2): write commit\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/857c83b2f1d7d3e7c113ed40edf63294\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/microsoft-square.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/microsoft-square.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/microsoft-square.png\",\"caption\":\"Microsoft Team\"},\"description\":\"Posts published under the Microsoft Team account are collective contributions by the dbi services Microsoft consultants. The practice covers SQL Server across its lifecycle, from installation, migration, and security to high availability with Always On and performance tuning, together with Azure data services and the Microsoft Business Intelligence stack, including Power BI. The team holds Microsoft certifications up to Expert level and operates customer platforms under service level agreements.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/microsoft-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Variations on 1M insert (2): write commit - dbi services | Blog","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.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/","og_locale":"en_US","og_type":"article","og_title":"Variations on 1M insert (2): write commit","og_description":"In this blog post, I will try to do the same than my colleagues about Oracle and for PostgreSQL. As a reminder, we\u2019ve seen in my previous blog post that SQL Server is designed to commit transactions implicitly by default and inserting 1M rows in this case may have a huge impact on the transaction… ","og_url":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/","og_site_name":"dbi services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2015-05-18T10:39:26+00:00","author":"Microsoft Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/857c83b2f1d7d3e7c113ed40edf63294"},"headline":"Variations on 1M insert (2): write commit","datePublished":"2015-05-18T10:39:26+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/"},"wordCount":337,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"keywords":["Performance","SQL Server 2014"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/","url":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/","name":"Variations on 1M insert (2): write commit - dbi services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-05-18T10:39:26+00:00","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-insert-2-write-commit\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Variations on 1M insert (2): write commit"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/857c83b2f1d7d3e7c113ed40edf63294","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/microsoft-square.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/microsoft-square.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/microsoft-square.png","caption":"Microsoft Team"},"description":"Posts published under the Microsoft Team account are collective contributions by the dbi services Microsoft consultants. The practice covers SQL Server across its lifecycle, from installation, migration, and security to high availability with Always On and performance tuning, together with Azure data services and the Microsoft Business Intelligence stack, including Power BI. The team holds Microsoft certifications up to Expert level and operates customer platforms under service level agreements.","url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4773","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4773"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4773\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4773"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4773"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4773"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}