SQL Server 2022 introduces two new features that enhance thread management: Parallel Redo Thread Pool and Parallel Redo Batch Redo. These features can significantly boost the performance of redo operations, which is particularly advantageous for databases with a high volume of redo work. I had a great discussion about Thread Management for Performance Improvement with my client, who recently hired me for Comprehensive Database Performance Health Check.
Why is Redo Important? -Thread Management
When a transaction is committed in SQL Server, its modifications are recorded in the transaction log. The transaction log plays a crucial role in ensuring the durability of SQL Server. Durability guarantees that changes made by a committed transaction remain permanent, even in the event of a system failure.
In AlwaysOn, a database is replicated to one or more secondary replicas. When a transaction is committed on the primary replica, the changes are written to the transaction log on that replica. Subsequently, the changes are replicated to the secondary replicas.
The transaction log on the secondary replicas is utilized to replay the changes made by committed transactions. This ensures that the secondary replicas are always synchronized with the primary replica.
Parallel Redo Thread Pool
The Parallel Redo Thread Pool is a thread pool shared at the instance level, available for all databases that involve redo operations. This means that each database can benefit from parallel redo, even if it lacks sufficient active connections to fill the thread pool.
In previous SQL Server versions, the number of threads available for redo was limited to 100. This limitation arose from the need for each thread to acquire a latch on the log record being redone. This latch prevented other threads from accessing the same log record, leading to potential performance bottlenecks.
The Parallel Redo Thread Pool resolves this issue by enabling multiple threads to access the same log record simultaneously. This is accomplished by batching together the redo of log records. Each batch of log records is assigned to a single thread, reducing the number of times the latch needs to be acquired.
As a result of the Parallel Redo Thread Pool, the redo performance in SQL Server 2022 can be significantly improved. This improvement is particularly valuable for databases with a substantial amount of redo work, such as frequently updated databases and databases participating in an AlwaysOn configuration.
Parallel Redo Batch Redo
The Parallel Redo Batch Redo feature enhances redo performance by batching together the redo of contiguous log records. This involves grouping together log records that appear consecutively in the log file into a single batch. Each batch of log records is then redone by a single thread.
By batching together the redo of log records, the number of times the log file needs to be read is reduced. Each thread only needs to read the log records within its assigned batch. Consequently, the batching of log records significantly enhances redo performance.
The Parallel Redo Batch Redo feature is particularly advantageous for databases with a large amount of redo work. Such databases typically exhibit a high number of contiguous log records. By batching the redo of these log records, the Parallel Redo Batch Redo feature can greatly enhance redo performance.
Conclusion
The Parallel Redo Thread Pool and Parallel Redo Batch Redo features are two new enhancements in SQL Server 2022 that focus on thread management. These features offer significant performance improvements for redo operations, benefiting databases with a substantial amount of redo work.
In addition to the aforementioned features, there are a few other actions you can take to enhance redo performance:
- Ensure that your database server has sufficient memory and CPU resources.
- Utilize high-speed storage devices for your log files.
- Regularly defragment your log files.
- Disable any unnecessary services and features on your database server.
Following these guidelines can enhance redo performance and make your database more responsive. You can always reach out to me on YouTube.
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
