THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

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 http://www.dbta.com/Columns/SQL-Server-Drill-Down/Delayed-Durability-The-SQL-Server-2014-Transaction-Log-Hack!-100928.aspx.

-Kevin

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

Published Wednesday, July 15, 2015 8:00 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

LondonDBA said:

Hi Kevin, thanks for this article. In it you say 'You can change the log buffer chunk size at the database level'. What's the command to do this ?

Thanks.

July 15, 2015 6:45 PM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Archives

Privacy Statement