{"id":375,"date":"2016-11-30T13:57:29","date_gmt":"2016-11-30T13:57:29","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=375"},"modified":"2016-11-30T13:57:29","modified_gmt":"2016-11-30T13:57:29","slug":"in-memory-logging-much-cooler-than-disk-based","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2016\/11\/30\/in-memory-logging-much-cooler-than-disk-based\/","title":{"rendered":"In-Memory logging, much cooler than disk based"},"content":{"rendered":"

This week I want to look at one of the most important parts, in my opinion, that allow In-Memory to outperform and scale better than the traditional engine, and this is the transaction log usage. Last week we started exploring some of the cool features that the new In-Memory engine (code name Hekaton) has brought into play, looking at the differences between Non-Clustered indexes<\/a> when they are memory optimized or disk based. That was cool. <\/p>\n

This week I want to look at one of the most important parts, in my opinion, that allow In-Memory to outperform and scale better than the traditional engine, and this is the transaction log usage.<\/p>\n

 
\nBackground<\/strong><\/p>\n

SQL Servers in order to guarantee Durability (that’s the D from ACID<\/a>) uses the transaction log if a way that every operation has to be written first there before sending the commit acknowledgment to the client.<\/p>\n

This is called Write-Ahead logging<\/a>, and due to its own nature, there are some obvious scalability problems. <\/p>\n

Think about it, if each record we insert, update or delete has to be written to the transaction log first and there is only one transaction log file per database, that can be a real bottleneck for highly transactional system, where multiple processes will be competing each other to make those writes to keep processing data.<\/p>\n

 
\nIn-Memory Engine<\/strong><\/p>\n

According to the original whitepaper<\/a> one of the biggest challenges when developing the new engine was scalability and as we’ve seen, it’d be difficult if things kept being done the same way.<\/p>\n

But on the other hand, working with datasets exclusively in memory, would any failure happens, recovery might be impossible, hence there is no other way than hardening that data to disk to guarantee the Durability of each transaction which is committed.<\/p>\n

What is cool is how this is achieved, but as always, let me write some queries to show you. <\/p>\n

I’ll be using [WideWorldImporters] database if you want to follow me.<\/p>\n

\r\nUSE WideWorldImporters\r\nGO\r\nSELECT VehicleTemperatureID, VehicleRegistration, ChillerSensorNumber, RecordedWhen, Temperature, FullSensorData, IsCompressed, CompressedSensorData\r\nINTO [Warehouse].[VehicleTemperatures_DiskBased] \r\nFROM [Warehouse].[VehicleTemperatures]\r\nGO\r\n<\/pre>\n
That would create an identical (~ish) copy of one of our In-Memory tables, but disk based so we can see the difference when we DELETE a number of rows.<\/p>\n
\r\nCHECKPOINT\r\nGO\r\n\r\nBEGIN TRANSACTION DEL_100_DISK_BASED\r\n \r\nDELETE TOP(100) t\r\nFROM [Warehouse].[VehicleTemperatures_DiskBased] AS t\r\n \r\nCOMMIT\r\nGO\r\n \r\nSELECT *\r\nINTO #t\r\nFROM sys.fn_dblog(NULL, NULL)\r\n \r\nSELECT [Current LSN]\r\n\t\t, Operation\r\n\t\t, Context\r\n\t\t, [Transaction ID]\r\n\t\t, [Transaction Name]\r\n\t\t, AllocUnitId\r\n\t\t, [Page ID]\r\n\t\t, [Slot ID]\r\n\t\t, [Lock Information]\r\n\t\t, Description\r\nFROM #t\r\nWHERE [Transaction ID] = (SELECT [Transaction ID] FROM #t WHERE [Transaction Name] = 'DEL_100_DISK_BASED')\r\nORDER BY [Current LSN] ASC\r\n\r\n<\/pre>\n
\"01_disk_based_delete_100_rows_commit\"<\/a><\/p>\n
See how for each of the records we have deleted, a record in the transaction log is also required, not very efficient but that’s how it is.<\/p>\n
Now we can see that it get even worse if we ROLLBACK our transaction.<\/p>\n
\r\nCHECKPOINT\r\nGO\r\n\r\nBEGIN TRANSACTION DEL_100_DISK_BASED_ROLLBACK\r\n \r\nDELETE TOP(100) t\r\nFROM [Warehouse].[VehicleTemperatures_DiskBased] AS t\r\n \r\nROLLBACK\r\nGO\r\n \r\nSELECT *\r\nINTO #t2\r\nFROM sys.fn_dblog(NULL, NULL)\r\n \r\nSELECT [Current LSN]\r\n\t\t, Operation\r\n\t\t, Context\r\n\t\t, [Transaction ID]\r\n\t\t, [Transaction Name]\r\n\t\t, AllocUnitId\r\n\t\t, [Page ID]\r\n\t\t, [Slot ID]\r\n\t\t, [Lock Information]\r\n\t\t, Description\r\nFROM #t2\r\nWHERE [Transaction ID] = (SELECT [Transaction ID] FROM #t2 WHERE [Transaction Name] = 'DEL_100_DISK_BASED_ROLLBACK')\r\nORDER BY [Current LSN] ASC\r\n<\/pre>\n
\"02_disk_based_delete_100_rows_rollback\"<\/a><\/p>\n
Now we need double the rows, because for each row we’ve said it’s been deleted, we have to tell SQL Server that was not actually deleted (COMPENSATION due to ROLLBACK) in case of recovery (crash recovery or backup recovery). That’s so bad.<\/p>\n
But not everything is lost yet \ud83d\ude42 let’s check how the In-Memory engine deal with this problem<\/p>\n
\r\nCHECKPOINT\r\nGO\r\n\r\nBEGIN TRANSACTION DEL_100_IN_MEMORY\r\n \r\nDELETE TOP(100) t\r\nFROM [Warehouse].[VehicleTemperatures] AS t\r\n \r\nCOMMIT\r\nGO\r\n \r\nSELECT *\r\nINTO #t3\r\nFROM sys.fn_dblog(NULL, NULL)\r\n \r\nSELECT [Current LSN]\r\n\t\t, Operation\r\n\t\t, Context\r\n\t\t, [Transaction ID]\r\n\t\t, [Transaction Name]\r\n\t\t, AllocUnitId\r\n\t\t, [Page ID]\r\n\t\t, [Slot ID]\r\n\t\t, [Lock Information]\r\n\t\t, Description\r\nFROM #t3\r\nWHERE [Transaction ID] = (SELECT [Transaction ID] FROM #t3 WHERE [Transaction Name] = 'DEL_100_IN_MEMORY')\r\nORDER BY [Current LSN] ASC\r\n<\/pre>\n
\"03_in_memory_delete_100_rows_commit\"<\/a><\/p>\n
That’s better! According to the whitepaper Hekaton\u2019s transaction log is designed for high efficiency and scale. Each transaction is logged in a single, potentially large, log record. <\/em><\/strong><\/p>\n
SQL Server would write one single record for the whole transaction as this has been committed, but how does it work when we rollback our transaction?<\/p>\n
\r\nCHECKPOINT\r\nGO\r\n\r\nBEGIN TRANSACTION DEL_100_IN_MEMORY_ROLLBACK\r\n \r\nDELETE TOP(100) t\r\nFROM [Warehouse].[VehicleTemperatures] AS t\r\n \r\nROLLBACK\r\nGO\r\n \r\nSELECT *\r\nINTO #t4\r\nFROM sys.fn_dblog(NULL, NULL)\r\n \r\nSELECT [Current LSN]\r\n\t\t, Operation\r\n\t\t, Context\r\n\t\t, [Transaction ID]\r\n\t\t, [Transaction Name]\r\n\t\t, AllocUnitId\r\n\t\t, [Page ID]\r\n\t\t, [Slot ID]\r\n\t\t, [Lock Information]\r\n\t\t, Description\r\nFROM #t4\r\nWHERE [Transaction ID] = (SELECT [Transaction ID] FROM #t4 WHERE [Transaction Name] = 'DEL_100_IN_MEMORY_ROLLBACK')\r\nORDER BY [Current LSN] ASC\r\n\r\n<\/pre>\n
\"04_in_memory_delete_100_rows_rollback\"<\/a><\/p>\n
Nothing, nada, rien, nichts, \u043d\u0438\u0447\u0435\u0433\u043e. That’s even better!<\/p>\n
And that is because in this case, the transaction log file contains only enough information to redo committed transactions<\/em><\/strong>, the whole set of changes goes to CHECKPOINT files, which are out of the scope of this post, but I’ll talk about them in the future for sure since they are key in all this process.<\/p>\n
 
\nConclusion<\/strong><\/p>\n
The changes done in the way SQL Server can provide Durability through the In-Memory engine are really impressive and can take out all the contention created by having just one transaction log file, by reducing its usage to the minimum.<\/p>\n
There is still a lot to explore in this topic, so stay tuned for future posts!<\/p>\n
Thanks for reading.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
This week I want to look at one of the most important parts, in my opinion, that allow In-Memory to outperform and scale better than the traditional engine, and this is the…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,21],"tags":[53,54,23,12,56],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/375"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=375"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/375\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=375"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=375"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=375"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}