SQL Server MVP Deep Dives, Volume 2

Chapter 36. Performance-tuning the transaction log for OLTP workloads

Brad M. McGehee

SQL Server can experience various types of bottlenecks that can negatively affect its performance. In this chapter, I’ll focus on a specific and often overlooked bottleneck: the transaction log. I’ll briefly describe how the transaction log works, the factors that can contribute to it being a bottleneck, how to determine if you have a transaction log bottleneck, and best practices you can employ to help prevent future bottlenecks or to correct existing bottlenecks.

How can the transaction log be a bottleneck?

To understand how the transaction log can become a bottleneck for SQL Server, let’s first take a brief look at how the transaction log works. The following ...