Delayed Durability

Kenneth Fisher talks about Delayed Durability as his favorite SQL Server feature:

In the end I decided it would be fun to post about one of the newer features;Delayed Durability. To understand DD (delayed durability) you have to understand how SQL Server implements durability. To start with there are two pieces of any data change. The actual data page and any log records that are created. In order to keep SQL Server as fast as possible the data pages are changed in memory and then written to disk as part of a background process. Unfortunately this means that you could lose those changes if the server crashes or shuts down before they are written to disk. In order to avoid this, and keep the changes durable, the transaction can not complete until the log information is actually written to disk. This means that one of the big bottlenecks in any given transaction is writing that log record to disk. The DD option basically changes that write from a synchronous to an asynchronous operation. The log writes are bundled together in 60k chunks before being written to disk. This can (depending on your work load) provide a huge increase in speed. I’m not going to bother discussing that part of it since Aaron Bertrand (b/t) and Melissa Connors (b/t) wrote great posts on just that (Aaron’s and Melissa’s).

What I want to discuss is the actual risk of data loss.

My philosophy on this is simple:  if you need delayed durability and you can afford the data loss, then maybe there’s a better data platform.  I want my SQL Server like I want my Grateful Dead concert:  ACID compliant.

Related Posts

Using Date Types In Warehouses

Koen Verbeeck argues that date keys in warehouses should be actual date types: The worst are by far the string representation, as there is no actual check on the contents. It can literally contain everything. And is ’01/02/2018′ the first of February 2018 (like any sane person would read, because days come before months), or […]

Read More

Appropriate Data Types And Unicode

Raul Gonzalez on (in)appropriate use of National character strings: Yes, you have read it… I see dates stored as NVARCHAR(10) and NCHAR(10) on daily basis, please don’t ask me why. This case is even worse, because DATE takes 3 bytes where NCHAR(10) takes 20 bytes, yes Ladies and Gentlemen more than 6 times more space […]

Read More

Categories

April 2016
MTWTFSS
« Mar May »
 123
45678910
11121314151617
18192021222324
252627282930