There is one user log cache for each configured user connection. SQL Server uses these user log caches to buffer the user transaction log records, which reduces the contention at the end of the transaction log. When a user log cache becomes full or when another event occurs (such as when the transaction completes), SQL Server “flushes” all log records from the user log cache to the database transaction log. You can configure the size of all user log caches for your server using the user log cache size parameter of sp_configure; the default size is 2048 bytes.
Since more than one process can access the contents of a user log cache when determining a possible “flush,” SQL Server uses spinlocks to protect the user log caches. A spinlock is an internal locking mechanism that prevents a second process from accessing the data structure being used by another process. SQL Server uses the user log cache spinlock ratio parameter of sp_configure to specify the ratio of user log caches per user log cache spinlock; the default is 20 user log caches for each spinlock.
For information about configuring user log cache size and user log cache spinlock ratio, see Chapter 11, “Setting Configuration Parameters,” in the System Administration Guide.
