DBTA - Delayed Durability—The SQL Server 2014 Transaction Log Hack!
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 (@kekline) serves as Principal Program Manager at SentryOne. He is a founder and former president of PASS and the author of popular IT books like SQL in a Nutshell. Kevin is a renowned database expert, software industry veteran, Microsoft SQL Server MVP, and long-time blogger at SentryOne. As a noted leader in the SQL Server community, Kevin blogs about Microsoft Data Platform features and best practices, SQL Server trends, and professional development for data professionals.