Infrastructure at your Service

David Barbarin

Variations on 1M insert (2): write commit

In this blog post, I will try to do the same than my colleagues about Oracle and for PostgreSQL. As a reminder, we’ve 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 log throughput. Each transaction is synchronously committed to the transaction log. In this blog post, we’ll see a variation of the previous test

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, SQL Server uses a write-ahead logging protocol (WAL) and using this new feature will temporarily suspend this requirement.

So, let’s perform the same test but this time I will favour the overall throughput performance by using delayed durability option.

alter database demo set delayed_durability = allowed;
go
DECLARE @i INT = 1;
WHILE @i <= 1000000
BEGIN
begin tran;
INSERT 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);
commit tran with (delayed_durability = on);
SET @i += 1;
END

– 00:00:20 – Heap table
– 00:00:19 – table with clustered index

If I refer to my first test results 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. Depending on your context, you may prefer either to enable or to force this option directly at the database level.

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’s blog posts)

Want to meet in one day our experts from all technologies? Come to our Event In-Memory: boost your IT performance! where we talk about SQL Server, Oracle and SAP HANA.

 

Leave a Reply


× nine = 54

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader