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

Comments (7)

  1. OmariO says:

    Do they all do IO? Or just put IO requests to some queue?

  2. Bob Dorr says:

    The log writers job is to post and process the I/O. Each can perform I/O.

  3. Eric Barr says:

    In what scenarios is it faster? The test case here (https://exadat.co.uk/2016/04/01/sql-server-2016-multi-threaded-log-writer/) is designed to stress log writing and is slower in 2016 than 2014.

    1. Bob Dorr says:

      I passed along your experience to the code owners for closer inspection.

      We did uncover an issue with latching in the pre-release builds that lead to slower insert speeds and has lots of similar behavior as you described. This will be addressed in the SQL 2016 release bits. This could have skewed your stress testing results.

  4. Azad Sale says:

    Its mentioned configuring affinity IO mask is not recommended. But on other hand you are stating its important to properly place the log writers on NUMA nodes otherwise they will all run on numa node 0.

    So which one is the best option ? As i am confused here.

  5. Neeraj Mittal says:

    Awesome !! faster transaction log throughput

Skip to main content