Chapter 36. Performance-tuning the transaction log for OLTP workloads
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.
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 example has been simplified to make it easy for you to understand. In the real world, what happens is a lot messier, but the principles remain the same.
1. Let’s assume that a) an application wants to insert a single row into a table and calls a stored procedure wrapped within an explicit transaction to perform the task, b) the stored procedure is the only code in the batch that’s being submitted to SQL Server from the application, and c) there are no other transactions going on at the same time.
