SQL 2016 - It Just Runs Faster: Multiple Log Writer Workers

  • [アーティクル]

 

SQL Server 2016 introduces multiple log writer workers to the engine. For many years the log device was limited by the capabilities of spinning media and hardware caches. The advancement of hardware caches and fast storage types (SSD, flash, …) on large installations can saturate a single log write worker.

 

SQL Server 2016 extended the log writer by allowing up to 4 workers for the instance. You can find out the number of log writers present in your SQL Server using the following command:

 

select session_id, status, command, scheduler_id, task_address, wait_type

from sys.dm_exec_requests

where command = 'LOG WRITER'

 

The number of log writers created during the SQL Server instance startup depends on the number of hardware NUMA nodes present on the system. If there is only one NUMA node, then there will be only one log writer created. For systems with multiple NUMA nodes, there can be a maximum of 4 log writer threads.

 

If you have configured the affinity I/O mask (not recommended) the log writer threads are bound to the CPU mask specified in the affinity I/O mask configuration. Otherwise, the log writer threads will be created on separate hidden schedulers. These hidden schedulers are always bound to NUMA node 0. These decision to cap the maximum number of log writers and their placement is based on extensive performance tests conducted with heavy concurrent activities of multiple log writers accessing the log flush queue.

 

'It Just Runs Faster' - SQL Server 2016 uses up to 4, log writer workers to service log write activities improving LDF throughput capabilities.

 

Suresh Kandoth -Senior SQL Server Escalation Engineer

Nitin Verma - Senior Software Engineer

 

Update: There have been reports of performance problems when Multiple Log Writers are used by the engine on NUMA machines if the database is enabled for delayed durability. A fix is now available for this problem in SQL Server 2016 in the latest cumulative update. The symptoms of this problem may be higher than normal CPU usage and a high rate of collisions and backoffs for the LOGCACHE_ACCESS spinlock as seen in sys.dm_os_spinlock_stats.

Bob Ward
Microsoft

Comments

  • Anonymous
    June 20, 2016
    This small change to the query might make it a little better:SELECT session_id, [status], command, scheduler_id, task_address, wait_typeFROM sys.dm_exec_requestsWHERE command = N'LOG WRITER';
  • Anonymous
    September 19, 2016
    Hi Robert,If there are multiple writers does this change the advice re multiple log files for a database ? ie Can we get a benefit by adding extra log files on their own disks ? Previously only 1 log file would be accessed at a time so there was never a performance advantage to having multiple files but with multiple writers it seems possible (probably ?) that having multiple files would helptiaStephen
    • Anonymous
      September 19, 2016
      The way the log blocks are laid out does not change with multiple log writers. The I/O pattern should be very similar just that we have more threads to handle formatting the log block and handling the I/O completions.
  • Anonymous
    August 01, 2017
    The comment has been removed