Translation log cache (the logfile)

Logging cache (the log caches)

Section translated from: Microsoft SQL Server Internals P177 (This section is new in version 2012)

Just as a data page writes to the data cache before writing to the disk, SQL Server caches the log records before physically writing to the disk. In a 64-bit system, the log cache contains a maximum of 128 items, and 32-bit systems that contain up to 32 items. Each entry contains a buffer (buffer) to store the log records-before they are written to disk as an entire block. Although the size of the block is from 512 bytes to 60KB, the buffer (buffer) is always 60KB because the maximum potential block can be accommodated by the buffer (buffer). The number of buffers actually allocated (that is, not all items are used) depends on the actual workload and the performance of the log disk. After the log buffer (buffer) is written to disk, SQL Server re-uses the buffer. If the write process takes longer, you need to allocate more buffers (buffer), and if the write process is completed quickly, then only a small buffer (buffer) is required. Since a log write can be up to 4M in size, if your work produces a large log block, you will need less buffer (buffer) than the work of generating small log blocks.

SQL Server 2012 introduces a new structure for managing cache log blocks-the log pool. A log pool is a hash table that is hashed based on the block ID and the database ID (DB ID). Log pools allow SQL Server to access a set of log records (sets of log records) for a variety of different technologies. For example, the replication agent (replication agents) needs to access the transaction log for mirroring and replication, and so on, the log set, mirror (mirroring), and Replication Manager (replica Manager) that require accessing replicated transactions. If you retrieve the DMV list, you will find 7 DMV involved in the log pool. Although the actual cached log block has a global hash, because each database manages its own log, metadata (metadata) points to the log pool manager-the manager manages their own log records and an empty pool of empty log buffers for their databases.

Translation log cache (the logfile)