DBTA – Delayed Durability—The SQL Server 2014 Transaction Log Hack!

Originally appearing in my monthly column at Database Trends & Applications magazine.

Delayed durability was announced late in the SQL Server 2014 development cycle, but offers something that many SQL Server professionals have wanted for years—the ability to disable transaction logging.

Why turn off the transaction log? It is the main guarantor of transactional consistency in SQL Server and is critical for highly recoverable applications, after all. That answer, in itself, tells you about situations where you might like to turn off the transaction log. That is, you can accelerate performance in a lot of situations where you do NOT need transactional consistency.

DEFAULT TRANSACTION LOG BEHAVIOR COMPARED TO DELAYED DURABILITY
Here’s how SQL Server’s transaction log works by default. It uses a write-ahead log (WAL) algorithm, which basically means that transactions must be recorded in the log before they are otherwise committed. (If you’re more familiar with Oracle, note that the SQL Server transaction log combines the functionality of Oracle’s archive and redo logs.) Because all transactional work in a SQL Server database is dependent on the speed and I/O throughput of the transaction log, it’s not uncommon to find instances of SQL Server that perform poorly because of I/O bottlenecks on the file system where the transaction log is located.

Read the rest of this article at HERE.

-Kevin

Connect with me online! Facebook | Twitter | LinkedIn | SlideShare | YouTube | Google Author

Speak Your Mind

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.