SQL Server Log Writer Workers

SQL Server 2017 leverages up to 4 log writer workers, on hidden schedulers, to assist in transaction log processing activities.   You may find the number of log write workers has been increased to 8 when running newer releases of SQL Server to accommodate larger systems.  (Caution: Pre-release software may change before final release.)

During SQL Server startup the number of log writer workers is determined. 

If multiple log write workers are not allowed use a single log writer

Otherwise

• Total number NUMA nodes * 2
• Count the number of available CPUs on the NUMA node (affinity mask can impact this count)

       Take the smaller of the above calculations and cap by the MAX_LOG_WRITERS allowed (4 or 8 depending on version)

Bob Dorr

• Anonymous
  February 12, 2019
  Thanks very much, Bob. Two quick questions. Are there any DMVs/extended events that show the beneficial effects of these multiple log writer workers or show stats for them in action ? And is there a trace flag to change to only one log writer worker (for comparison testing) ? I'd like some stats that quantify the effect of multiple log writer workers. Thanks again.
• Anonymous
  February 12, 2019
  I think you can disable the multi-threaded log writer with trace flag 9038 but, unless you have a very very fast storage and you see an abnormal amount of spinlocks (LOGFLUSHQ), it will be better to keep it on.
• Anonymous
  February 20, 2019
  Benchmarks would be nice too