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

T-SQL Tuesday 104 Roundup

Bert Wagner reviews the entries for T-SQL Tuesday 104: This month’s T-SQL Tuesday topic asked “What code would you hate to live without?” Turns out you like using script and code to automate boring, repetitive, and error-prone tasks. Thank you to everyone who participated; I was nervous that July holidays and summer vacations would stunt […]

Read More

Comparing System Metadata Between SQL Server Versions

Aaron Bertrand shows how he finds hidden features in new SQL Server builds: One of the areas I like to focus on is new features in SQL Server. Under both MVP and Microsoft Partner programs, I get to see a lot of builds of SQL Server that don’t make it to the public, and documentation […]

Read More

Categories

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